toggle button or worksheet_activate causing form to open twice

I have a userform that prompts for a password. I posted yesterday regarding
the fact that when I click a button, it runs through a macro, either opens a
msgbox or opens a userform, then when you click OK to close the userform (and
run another macro to validate the input), the userform, or message box,
reopens.

I debugged and stepped through the macro (from ProtectionToggle_Click() Sub
to called macros) and it did not come up twice. Also, I have run the
userform macro from VB and it works fine. It seems to only be a problem if I
run it from the toggle button in Excel.

I think it may be either the toggle buttons causing the problem or the
Worksheet_Activate macro. Could someone take a look?

Code for Toggle Button:
Private Sub ProtectionToggle_Click()
Application.ScreenUpdating = False
If wkshtInventoryHome.ProtectContents = True Then
With ProtectionToggle
.Caption = "Protect Sheets"
.Value = False
End With
Call RunProtectionPasswordUserForm
ElseIf wkshtInventoryHome.ProtectContents = False Then
Response = MsgBox("Are you sure you want to protect the worksheet?" _
& vbLf & "This action cannot be undone without a password.",
vbOKCancel, _
"Confirm Protect Sheets")
If Response = vbOK Then
With ProtectionToggle
.Caption = "Begin Advanced Editing"
.Value = False
End With
End If
'after button is pressed:
Call WorkbookProtect
End If
Application.ScreenUpdating = True
End Sub

Worksheet Activate:
Private Sub Worksheet_Activate()
If wkshtLumberShores.ProtectContents = True Then
ProtectionToggle.Caption = "Begin Advanced Editing"
ElseIf wkshtLumberShores.ProtectContents = False Then
ProtectionToggle.Caption = "Protect Sheets"
End If
End Sub

Let's look at a simplified situation which is probably the cause of your
problem.
If I put in code like

Private Sub ToggleButton1_Click()
MsgBox ToggleButton1.Value
With ToggleButton1
.Caption = "Protect Sheets"
.Value = False
End With
End Sub

then the click event runs twice. When you change the value to False, it
triggers another click.

You would need to do something like


Public bBlockEvents

Private Sub ToggleButton1_Click()
If bBlockEvents Then Exit Sub
bBlockEvents = True
MsgBox ToggleButton1.Value
With ToggleButton1
.Caption = "Protect Sheets"
.Value = False
End With
bBlockEvents = False
End Sub

so i use a public variable to cause the second click event to exit
immediately.

--
Regards,
Tom Ogilvy

"Nicole B" <[email protected]> wrote in message
news:[email protected]...
> I have a userform that prompts for a password. I posted yesterday
regarding
> the fact that when I click a button, it runs through a macro, either opens
a
> msgbox or opens a userform, then when you click OK to close the userform
(and
> run another macro to validate the input), the userform, or message box,
> reopens.
>
> I debugged and stepped through the macro (from ProtectionToggle_Click()
Sub
> to called macros) and it did not come up twice. Also, I have run the
> userform macro from VB and it works fine. It seems to only be a problem
if I
> run it from the toggle button in Excel.
>
> I think it may be either the toggle buttons causing the problem or the
> Worksheet_Activate macro. Could someone take a look?
>
> Code for Toggle Button:
> Private Sub ProtectionToggle_Click()
> Application.ScreenUpdating = False
> If wkshtInventoryHome.ProtectContents = True Then
> With ProtectionToggle
> .Caption = "Protect Sheets"
> .Value = False
> End With
> Call RunProtectionPasswordUserForm
> ElseIf wkshtInventoryHome.ProtectContents = False Then
> Response = MsgBox("Are you sure you want to protect the worksheet?" _
> & vbLf & "This action cannot be undone without a password.",
> vbOKCancel, _
> "Confirm Protect Sheets")
> If Response = vbOK Then
> With ProtectionToggle
> .Caption = "Begin Advanced Editing"
> .Value = False
> End With
> End If
> 'after button is pressed:
> Call WorkbookProtect
> End If
> Application.ScreenUpdating = True
> End Sub
>
> Worksheet Activate:
> Private Sub Worksheet_Activate()
> If wkshtLumberShores.ProtectContents = True Then
> ProtectionToggle.Caption = "Begin Advanced Editing"
> ElseIf wkshtLumberShores.ProtectContents = False Then
> ProtectionToggle.Caption = "Protect Sheets"
> End If
> End Sub

Thanks, Tom! That's exactly it! And all because I wanted the button to look
"up" all the time. You made it very easy!

I was wondering... how does your statement:
If bBlockEvents Then Exit Sub
work? is VB assuming some kind of value?

Thanks.

"Tom Ogilvy" wrote:

> Let's look at a simplified situation which is probably the cause of your
> problem.
> If I put in code like
>
> Private Sub ToggleButton1_Click()
> MsgBox ToggleButton1.Value
> With ToggleButton1
> .Caption = "Protect Sheets"
> .Value = False
> End With
> End Sub
>
> then the click event runs twice. When you change the value to False, it
> triggers another click.
>
> You would need to do something like
>
>
> Public bBlockEvents
>
> Private Sub ToggleButton1_Click()
> If bBlockEvents Then Exit Sub
> bBlockEvents = True
> MsgBox ToggleButton1.Value
> With ToggleButton1
> .Caption = "Protect Sheets"
> .Value = False
> End With
> bBlockEvents = False
> End Sub
>
> so i use a public variable to cause the second click event to exit
> immediately.
>
> --
> Regards,
> Tom Ogilvy
>
> "Nicole B" <[email protected]> wrote in message
> news:[email protected]...
> > I have a userform that prompts for a password. I posted yesterday
> regarding
> > the fact that when I click a button, it runs through a macro, either opens
> a
> > msgbox or opens a userform, then when you click OK to close the userform
> (and
> > run another macro to validate the input), the userform, or message box,
> > reopens.
> >
> > I debugged and stepped through the macro (from ProtectionToggle_Click()
> Sub
> > to called macros) and it did not come up twice. Also, I have run the
> > userform macro from VB and it works fine. It seems to only be a problem
> if I
> > run it from the toggle button in Excel.
> >
> > I think it may be either the toggle buttons causing the problem or the
> > Worksheet_Activate macro. Could someone take a look?
> >
> > Code for Toggle Button:
> > Private Sub ProtectionToggle_Click()
> > Application.ScreenUpdating = False
> > If wkshtInventoryHome.ProtectContents = True Then
> > With ProtectionToggle
> > .Caption = "Protect Sheets"
> > .Value = False
> > End With
> > Call RunProtectionPasswordUserForm
> > ElseIf wkshtInventoryHome.ProtectContents = False Then
> > Response = MsgBox("Are you sure you want to protect the worksheet?" _
> > & vbLf & "This action cannot be undone without a password.",
> > vbOKCancel, _
> > "Confirm Protect Sheets")
> > If Response = vbOK Then
> > With ProtectionToggle
> > .Caption = "Begin Advanced Editing"
> > .Value = False
> > End With
> > End If
> > 'after button is pressed:
> > Call WorkbookProtect
> > End If
> > Application.ScreenUpdating = True
> > End Sub
> >
> > Worksheet Activate:
> > Private Sub Worksheet_Activate()
> > If wkshtLumberShores.ProtectContents = True Then
> > ProtectionToggle.Caption = "Begin Advanced Editing"
> > ElseIf wkshtLumberShores.ProtectContents = False Then
> > ProtectionToggle.Caption = "Protect Sheets"
> > End If
> > End Sub
>
>
>

This is because bBlockEvents is a boolean (True/False) variable, and this is
the same as saying

If bBlockEvents = True Then Exit Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nicole B" <[email protected]> wrote in message
news:[email protected]...
> I was wondering... how does your statement:
> If bBlockEvents Then Exit Sub
> work? is VB assuming some kind of value?
>
> Thanks.
>
> "Tom Ogilvy" wrote:
>
> > Let's look at a simplified situation which is probably the cause of your
> > problem.
> > If I put in code like
> >
> > Private Sub ToggleButton1_Click()
> > MsgBox ToggleButton1.Value
> > With ToggleButton1
> > .Caption = "Protect Sheets"
> > .Value = False
> > End With
> > End Sub
> >
> > then the click event runs twice. When you change the value to False, it
> > triggers another click.
> >
> > You would need to do something like
> >
> >
> > Public bBlockEvents
> >
> > Private Sub ToggleButton1_Click()
> > If bBlockEvents Then Exit Sub
> > bBlockEvents = True
> > MsgBox ToggleButton1.Value
> > With ToggleButton1
> > .Caption = "Protect Sheets"
> > .Value = False
> > End With
> > bBlockEvents = False
> > End Sub
> >
> > so i use a public variable to cause the second click event to exit
> > immediately.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Nicole B" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a userform that prompts for a password. I posted yesterday
> > regarding
> > > the fact that when I click a button, it runs through a macro, either
opens
> > a
> > > msgbox or opens a userform, then when you click OK to close the
userform
> > (and
> > > run another macro to validate the input), the userform, or message
box,
> > > reopens.
> > >
> > > I debugged and stepped through the macro (from
ProtectionToggle_Click()
> > Sub
> > > to called macros) and it did not come up twice. Also, I have run the
> > > userform macro from VB and it works fine. It seems to only be a
problem
> > if I
> > > run it from the toggle button in Excel.
> > >
> > > I think it may be either the toggle buttons causing the problem or the
> > > Worksheet_Activate macro. Could someone take a look?
> > >
> > > Code for Toggle Button:
> > > Private Sub ProtectionToggle_Click()
> > > Application.ScreenUpdating = False
> > > If wkshtInventoryHome.ProtectContents = True Then
> > > With ProtectionToggle
> > > .Caption = "Protect Sheets"
> > > .Value = False
> > > End With
> > > Call RunProtectionPasswordUserForm
> > > ElseIf wkshtInventoryHome.ProtectContents = False Then
> > > Response = MsgBox("Are you sure you want to protect the
worksheet?" _
> > > & vbLf & "This action cannot be undone without a password.",
> > > vbOKCancel, _
> > > "Confirm Protect Sheets")
> > > If Response = vbOK Then
> > > With ProtectionToggle
> > > .Caption = "Begin Advanced Editing"
> > > .Value = False
> > > End With
> > > End If
> > > 'after button is pressed:
> > > Call WorkbookProtect
> > > End If
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > > Worksheet Activate:
> > > Private Sub Worksheet_Activate()
> > > If wkshtLumberShores.ProtectContents = True Then
> > > ProtectionToggle.Caption = "Begin Advanced Editing"
> > > ElseIf wkshtLumberShores.ProtectContents = False Then
> > > ProtectionToggle.Caption = "Protect Sheets"
> > > End If
> > > End Sub
> >
> >
> >

I'm confused. I thought that the sub was exiting when bblockevents=false, as
below (noted with asterisk). Otherwise, why wouldn't the sub end before the
msgbox?

> > Public bBlockEvents
> >
> > Private Sub ToggleButton1_Click()
> > If bBlockEvents Then Exit Sub
> > bBlockEvents = True
> > MsgBox ToggleButton1.Value
> > With ToggleButton1
> > .Caption = "Protect Sheets"
> > .Value = False
> > End With
*> > bBlockEvents = False
> > End Sub

"Bob Phillips" wrote:

> This is because bBlockEvents is a boolean (True/False) variable, and this is
> the same as saying
>
> If bBlockEvents = True Then Exit Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Nicole B" <[email protected]> wrote in message
> news:[email protected]...
> > I was wondering... how does your statement:
> > If bBlockEvents Then Exit Sub
> > work? is VB assuming some kind of value?

It exits when bBlockEvents is True.
Otherwise, events are not being blocked - so the next step in the event is
to set it to True so any subsequent event will be blocked. It then performs
an action that triggers an event, but the event code performs the test and
exits (is blocked). At the end of such actions, it sets it back to False so
the next event triggered externally will be handled.

just an added comment,

My declaration should have been

Public bBlockEvents as boolean

as posted it is a variant (just a typo)

It doesn't make any real difference, however, since being empty or false are
equivalent in the if test.
--
Regards,
Tom Ogilvy

"Nicole B" <[email protected]> wrote in message
news:[email protected]...
> I'm confused. I thought that the sub was exiting when bblockevents=false,
as
> below (noted with asterisk). Otherwise, why wouldn't the sub end before
the
> msgbox?
>
> > > Public bBlockEvents
> > >
> > > Private Sub ToggleButton1_Click()
> > > If bBlockEvents Then Exit Sub
> > > bBlockEvents = True
> > > MsgBox ToggleButton1.Value
> > > With ToggleButton1
> > > .Caption = "Protect Sheets"
> > > .Value = False
> > > End With
> *> > bBlockEvents = False
> > > End Sub
>
> "Bob Phillips" wrote:
>
> > This is because bBlockEvents is a boolean (True/False) variable, and
this is
> > the same as saying
> >
> > If bBlockEvents = True Then Exit Sub
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Nicole B" <[email protected]> wrote in message
> > news:[email protected]...
> > > I was wondering... how does your statement:
> > > If bBlockEvents Then Exit Sub
> > > work? is VB assuming some kind of value?
>

To maybe say the same thing as Tom, but in a different manner ...

The routine exits when bBlockEvents is true.
It continues if bBlockEvents is false, but immediately sets it to True so
that if the event gets triggered recursively, it will immediately exit.
At the end, it resets bBlockEvents to False so that any new event triggering
will not immediately exit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nicole B" <[email protected]> wrote in message
news:[email protected]...
> I'm confused. I thought that the sub was exiting when bblockevents=false,
as
> below (noted with asterisk). Otherwise, why wouldn't the sub end before
the
> msgbox?
>
> > > Public bBlockEvents
> > >
> > > Private Sub ToggleButton1_Click()
> > > If bBlockEvents Then Exit Sub
> > > bBlockEvents = True
> > > MsgBox ToggleButton1.Value
> > > With ToggleButton1
> > > .Caption = "Protect Sheets"
> > > .Value = False
> > > End With
> *> > bBlockEvents = False
> > > End Sub
>
> "Bob Phillips" wrote:
>
> > This is because bBlockEvents is a boolean (True/False) variable, and
this is
> > the same as saying
> >
> > If bBlockEvents = True Then Exit Sub
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Nicole B" <[email protected]> wrote in message
> > news:[email protected]...
> > > I was wondering... how does your statement:
> > > If bBlockEvents Then Exit Sub
> > > work? is VB assuming some kind of value?
>

Thanks to you both. It still seems a little awkward and upside down to me,
but I understand it better. Everyone is awesomely helpful on this site!