Hi Bagsy,
So VBA is an event-driven language. At its most fundamental that means that code is executed when an event occurs that triggers that code. So for example when a user form is initialised, or when a button is clicked etc. So you want to call your code when the correct event to trigger it occurs, if that makes sense?
Taking your example code above and your description of what you want to achieve we can modify both the code and the structure of your project a little. Unfortunately I can't post screen shots or a sample workbook as I'm in work at the moment and my employer blocks access to all file upload/hosting facilities as we work with very sensitive data, so I'll try my best to describe what you need to do in words and sample code alone.
I've assumed a few things from your previous posts:
- Your user form containing the password text box and the login button is called UserForm1
- The text box to input the password is called Password
- The login button is called Login
If any of those differ then change the references to them in the sample code accordingly. It is also worth noting that this is just one way of achieving what you want. As with anything like this there are multiple ways of achieving the same thing that may work better within the parameters of your project.
Now, first of all you will want a way to show UserForm1 when a user needs to enter their password. The code to show the user form is simply:
As to
where to put this code that will depend on how and when you want the user to input their password. For the sake of example let's say you have a command button embedded in the worksheet called EnterPassword that the user must click on. In that case you'd put the above code in the button's click event, like so:
VBA Code:
Private Sub EnterPassword_Click()
UserForm1.Show
End Sub
To get to the button's click event right-click on it and select 'View Code'. If you want to show the user form in a different way then let us know what that is and we can help you with that.
Now, once UserForm1 is displayed the user will enter their password in the Password text box, then click the Login button. So you want the code that checks the password then takes the rest of the actions to fire when the Login button is clicked. You can either put the code directly in the Login button's click event, or you can put it in a separate code module (Insert > Module in the editor) and then
call it from the button's click event. For this example we'll use the latter. So, insert a new code module and paste the following modification of your above code into the module:
VBA Code:
Public Sub AshSignature()
Dim Password As String
Password = "Apple Crumble"
'Check if the password is correct
If UserForm1.Password.Value <> Password Then
'Display a message and exit the sub-routine if the password is not correct
MsgBox "Sorry, incorrect password. Please try again.", vbOKOnly, "Incorrect Password"
Exit Sub
End If
'The following code will only be executed if the password is correct...
Dim wsPinkCopy As Worksheet
Set wsPinkCopy = Worksheets("Pink Copy1")
wsPinkCopy.Unprotect Password:="password"
wsPinkCopy.Range("O8:P13") = "A.Sewell"
wsPinkCopy.Protect Password:="password"
'Close the password input user form now that we don't need it
Unload UserForm1
Call EnterAshSignature
End Sub
Then, in the click event of the Login button, simply do:
VBA Code:
Private Sub Login_Click()
Call AshSignature
End Sub
I hope I've not over-complicated that for you but you seemed to be struggling with how and where to put your code so I thought a basic explanation of how 'events' and code relate to each other in VBA might help. Once again this is just one way of achieving what you want and not necessarily the best - but it should work.
Hope it helps
