password VBA and need for Macro

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I need to protect a workbook with a password, with the password being
unique based on the user's login id. In addition, I need to prevent
the use of the workbook if macros are disabled.

I downloaded this code from http://www.ozgrid.com/download/default.htm
[passwordbook], which works fine for the password portion:

Private Sub CancelButt_Click()
ThisWorkbook.Close SaveChanges:=False
End Sub

Private Sub CommandButton1_Click()
ThisWorkbook.Close SaveChanges:=False
End Sub

Private Sub Label4_Click()

End Sub

Private Sub OKButt_Click()
Dim iFoundPass As Integer
On Error Resume Next
With Sheets("Config").Range("UserNames")

iFoundPass = .Find(What:=UserNameTextBox, After:=.Cells(1, 1),
LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row
End With
On Error GoTo 0

If iFoundPass = 0 Then
SomethingWrong
Exit Sub
End If

If Sheets("Config").Cells(iFoundPass, 2) <> PasswordTextBox Then
SomethingWrong
Exit Sub
End If

Sheets("Config").Range("LoggedInAs") = UserNameTextBox
Unload Me


End Sub

Private Sub PasswordTextBox_Change()
OKButt.Enabled = (UserNameTextBox.TextLength > 2 And _
PasswordTextBox.TextLength > 2)
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode = 0 Then Cancel = True
End Sub

Private Sub UserNameTextBox_Change()
OKButt.Enabled = (UserNameTextBox.TextLength > 2 And _
PasswordTextBox.TextLength > 2)
End Sub

Private Sub SomethingWrong()
MsgBox "Incorrect Username or Password.", vbCritical +
vbInformation, "SDP Business Case"
End Sub

***************

I have also added the following code, to hide all sheets except one
unless macros are enabled:

Sub On_Open()

Worksheets("Macros").Visible = xlSheetVeryHidden
Worksheets("Introduction").Visible = True


End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Introduction").Visible = xlSheetVeryHidden
Worksheets("Macros").Visible = True

End Sub

The problem I get, is that if macros are enabled, after the correct
password is entered, I get "Run Time Error 1004 - Unable to set the
Visible property of the worksheet class", and the sheets that were
supposed to become visible remain hidden. I'm new to VBA, but I'm
guessing that the passord code some how prevents the macros from
unhiding the sheets.

Any help would be appreciated.

Thanks.
Jason
 
Jason,

First question is "how many sheets are in your workbook"
One sheet 'always' has to be visible.

If you only have two sheets then:
Worksheets("Macros").Visible = xlSheetVeryHidden
Worksheets("Introduction").Visible = True
Click to expand...
in both the Open and Close events have to be flopped.
Make the "Introducton" visible before you try to hide the
"Macros" sheet.

John

Jason said:
I need to protect a workbook with a password, with the password being
unique based on the user's login id. In addition, I need to prevent
the use of the workbook if macros are disabled.

I downloaded this code from http://www.ozgrid.com/download/default.htm
[passwordbook], which works fine for the password portion:

Private Sub CancelButt_Click()
ThisWorkbook.Close SaveChanges:=False
End Sub

Private Sub CommandButton1_Click()
ThisWorkbook.Close SaveChanges:=False
End Sub

Private Sub Label4_Click()

End Sub

Private Sub OKButt_Click()
Dim iFoundPass As Integer
On Error Resume Next
With Sheets("Config").Range("UserNames")

iFoundPass = .Find(What:=UserNameTextBox, After:=.Cells(1, 1),
LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row
End With
On Error GoTo 0

If iFoundPass = 0 Then
SomethingWrong
Exit Sub
End If

If Sheets("Config").Cells(iFoundPass, 2) <> PasswordTextBox Then
SomethingWrong
Exit Sub
End If

Sheets("Config").Range("LoggedInAs") = UserNameTextBox
Unload Me


End Sub

Private Sub PasswordTextBox_Change()
OKButt.Enabled = (UserNameTextBox.TextLength > 2 And _
PasswordTextBox.TextLength > 2)
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode = 0 Then Cancel = True
End Sub

Private Sub UserNameTextBox_Change()
OKButt.Enabled = (UserNameTextBox.TextLength > 2 And _
PasswordTextBox.TextLength > 2)
End Sub

Private Sub SomethingWrong()
MsgBox "Incorrect Username or Password.", vbCritical +
vbInformation, "SDP Business Case"
End Sub

***************

I have also added the following code, to hide all sheets except one
unless macros are enabled:

Sub On_Open()

Worksheets("Macros").Visible = xlSheetVeryHidden
Worksheets("Introduction").Visible = True


End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Introduction").Visible = xlSheetVeryHidden
Worksheets("Macros").Visible = True

End Sub

The problem I get, is that if macros are enabled, after the correct
password is entered, I get "Run Time Error 1004 - Unable to set the
Visible property of the worksheet class", and the sheets that were
supposed to become visible remain hidden. I'm new to VBA, but I'm
guessing that the passord code some how prevents the macros from
unhiding the sheets.

Any help would be appreciated.

Thanks.
Jason
Click to expand...
 
Back
Top