Linking to a Work sheet from a User form

[Mizar]

I have a user form with a command button on it and a to text boxes for a User name and a password.

I want to be able to get the userform to go to a sheet in the workbook and check to see if the data entered in the texboxes matches an entry in the cells, and if it does to open a new user form with the .show command


Also



I want to stop a user from Closing a VBA User Form in Excel. Is there a way of continuingly opening the window until the user hits a certain button. If they close it down and the button is not clicked then it reopens


Can some one help me?

[TheFIDDLER]

First part is simple.

Code for your button click event

Private Sub CommandButton1_Click()
Dim username As String
Dim password As String
username = Sheets(1).Range("A1")
password = Sheets(1).Range("B1")

If TextBox1.Text = username Then
If TextBox2.text = password Then
me.hide
newform.show
End If
End If
End Sub




As to your second part, I don't think there is any method that stops the user from closing the form in VBA. I'd be interested if anyone knows how this would be done.

[TheFIDDLER]

This partly answers your second question, which is to stop the user form from being cancelled.

This code will disable the X button on the top of the form.



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



Unfortunately does bring on a system message box telling the user to use the cancel button. Anyone know how we can change or eliminate this message?

[Mizar]

Il try out the second part of coding and see what happens:


Meanwhile this is what Iv been trying to do:

[Highlight=VB]
Sub Form_Unload()
If Sheets(2).Range("G4") = "No" Then
Login_Form.Show
Else
Login_Form.Hide
End If
End Sub
[Highlight=VB]

I have set up a module with the above code in it and the Reference No refers to a Work book sheet. I have an auto loading User form that inputs "No" when it loads to G4 so my idea is that this module could check to see if the value is No and if the form in unloaded it reopens the User Form unless the value is "Yes", which will keep the form closed.

At the moment the code does not work, I dont think the Unload part is correct. Any ideas

[TheFIDDLER]

I don't think there is a form unload event to start with.
Excel supports a form deactivate and a form terminate event.
You might want to give these a try and see what you come up with. I

Also - if this form is just a login screen, you really should clear it from memory and not simply hide it. Otherwise it will still gobble up some precious bites on your system
Rather than
Form.hide
Use
Unload Me

[Mizar]

Ok. Will that improve performance?


I heard i may have to code it using API calls, sounds hard

[Mizar]

That No close code works brilliantly. no message box pops up and it is secure nice one