Creating a Userform

[MaxRoberts22]

I have an excel file where I call a "Workbook_Open" subroutine from the
"ThisWorkbook" subheading in the Mircosoft Excel Objects. Before I call
this subroutine, I want to hide all of my sheets (1, 2, and 3) and then
I want to call a userform that prompts for a username and password. If
the username and password are correct, then the userform should call my
"Workbook_Open" subroutine and unhide the hidden sheets.

I had some code I saw on another topic, but I couldn't get it to call
my subroutine ("Workbook_Open") after it validated my username and
password.

Anybody have a suggestion here. I can make the userform but I'm not
sure where to insert the code.

Thanks for the help!

Ben

 

[Leith Ross]

Hello MaxRoberts22,

The UserForm must be called from the Workbook_Open event, not the
reverse. The Workbook_Open event can not be triggered from VBA, but
only by Excel opening the workbook. At the start of the event hide,
Excel will display how the workbook was saved last, If the worksheets
were visible, the worksheets will be displayed. They will be hidden
once the code runs. To have the workbook come up "blank", hide the
worksheets and save the workbook in the Workbook_BeforeClose event.

Sincerely,
Leith Ross

 

[MaxRoberts22]

So you're saying start the Workbook_Open subroutine by calling the
userform_activate subroutine in the userform? Or should there be two
subroutines in the Workbook_Open; one to call the userform and then the
userform will call the second Workbook_Open subroutine (if that makes
any sense)?

Can you give me a condensed version of what this might look like in the
work book? This is the code I have written for the userform:

Public cntr As Integer

Private Sub CommandButton1_Click()
ValidatePWD
End Sub

Private Sub UserForm_Activate()
cntr = 0
Label1.Caption = "Username & Password Required"
Label2.Caption = "Username:"
Label3.Caption = "Password:"
TextBox2.PasswordChar = "*"
CommandButton1.Caption = "Open Workbook"
End Sub

Private Sub ValidatePWD()
If TextBox1.Value = "username" And TextBox2.Value = "password" Then
UserForm1.Hide
Call Workbook_Open
Else
cntr = cntr + 1
If cntr > 3 Then
MsgBox "Sorry...wrong password...goodbye"
ThisWorkbook.Saved = True
ThisWorkbook.Close
Else
MsgBox "Attempt #" & cntr & vbCrLf & "Incorrect UserName
and/or Password entered"
End If
End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox Prompt:=" Sorry but I can't let you do that. "
End If
End Sub

I wanna call the Workbook_Open function from this userfrom_activate.
You're saying I cannot do this?

Ben

 

[MaxRoberts22]

Nevermind, I got it to work. Thanks for the help!