Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
password VBA and need for Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
password VBA and need for Macro
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 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" wrote in message m... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
password VBA and need for Macro
John,
Thanks...that worked. Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password protection in macro ( Anybody can view my password in VB | Excel Discussion (Misc queries) | |||
Macro (password) | Excel Discussion (Misc queries) | |||
Macro Password | Excel Discussion (Misc queries) | |||
Password and Macro | Excel Discussion (Misc queries) | |||
How to see macro code of a password protected macro without a password? | Excel Worksheet Functions |