edit macro cancel step

[Pete]

Hi

Driving me mad.....but cannot see the answer.

Here is the problem: - Need the macro to do the following two steps ONLY.

1. If you clicks worksheet "Administrator" then a userform appears which they can press cancel to go back to wworksheet "Daily Cmmunicatons Log" - if they press ok then nothing happens would like a msg box "Please Enter A Vaild Password"

2. If then the user clicks the worksheet "Daily communications log" whlist in the worksheet "Administrator" they are unbale to switch worksheet without pressing the cancel button on the userform......what to be able to switch irrespective of if i press cancel on the userform or the other worksheet Tab...

just that

[CreganTur]

Noticed that you cross posted this same Thread in the Excel forum. That's not a problem, but please look at this (http://www.excelguru.ca/node/7) regarding cross-posting.


if they press ok then nothing happens would like a msg box "Please Enter A Vaild Password"
Use this code- put it between your very last Dim statement, and your 'Application.EnableEvents = False' line of code:
If IsNull(Me.TextBox1) Then
MsgBox "Please enter a valid password."
ElseIf Me.TextBox1 = "" Then
MsgBox "Please enter a valid password."
End If


If then the user clicks the worksheet "Daily communications log" whlist in the worksheet "Administrator" they are unbale to switch worksheet without pressing the cancel button on the userform

You can't switch because of the modal property of the UserForm. It won't release focus until you make it go away.

Like xld said in the other post, you can use:
Admin_Details.Show vbModeless
it will allow you to switch between worksheets because it changes the UserForm's modal property. But, it won't banish the UserForm when you switch worksheets. You'd have to do some pretty fancy coding to add in the functionality that will unload the UserForm if you switch worksheets.

The simplest way to deal with this is add a note to your UserForm that says "Press cancel to return to the Daily Communications Log Worksheet."
That tells your user what to do to get away from the Admin login screen. It's a simple and effective answer to your problem.