Toggle Button Updating Concern

I have some toggle buttons on a "home page" worksheet in my workbook. I have
some VBA code programmed that updates their values based on a property of a
cell, for example, if a cell is protected the toggle button displays
"unprotect cells" so that the user knows that the button will remove
protection. Additionally, when these buttons are clicked, a userform comes
up prompting for a password before removing protection (not all of my users
have excel 2002 or better, so I'm forced to protect my document this way).
If the password is correct it runs a macro and is supposed to UnLoad the
userform. The code on the worksheet (for the button) is as follows:

Private Sub ProtectionToggle_Click()
If Sheet14.ProtectContents = True Then 'True means that protection is on!
With ProtectionToggle
.Value = False
.Caption = "Protect Sheets"
End With
Application.Run "RunProtectionPasswordUserForm"
ElseIf Sheet14.ProtectContents = False Then
ans = MsgBox("Are you sure you want to protect the worksheet?" _
& vbLf & "This action cannot be undone without a password.",
vbOKCancel, _
"Confirm Protect Sheets")
If ans = vbOK Then
With ProtectionToggle
.Value = False
.Caption = "Begin Advanced Editing"
End With
End If
Application.Run "WorkbookProtect"
End If
End Sub

The code for the userform OK button is as follows:

Private Sub OKButton_Click()
Application.Run "FilterEstimateData.ConfirmPassword"
UnLoad Me
End Sub

The problem that I am having is that the userform is, in fact, unloading,
but reloading. It consistently unloads upon OK then reloads once (if I hit
OK again with the right password, it does not reload again).

Any suggestions? Please help!

NB

The first thing that I notice in your code that puzzles me is why are you using

Application.Run "???"

instead of just calling procedures? I am not sure if or what problems doing
this might cause. That being said you are not showing the subprocedures that
you are invoking so it is a little tought to debug. Have you tried stepping
through the code using F8 to see where the for is being re-initialized? That
is where I would start...

HTH

"Nicole B" wrote:

> I have some toggle buttons on a "home page" worksheet in my workbook. I have
> some VBA code programmed that updates their values based on a property of a
> cell, for example, if a cell is protected the toggle button displays
> "unprotect cells" so that the user knows that the button will remove
> protection. Additionally, when these buttons are clicked, a userform comes
> up prompting for a password before removing protection (not all of my users
> have excel 2002 or better, so I'm forced to protect my document this way).
> If the password is correct it runs a macro and is supposed to UnLoad the
> userform. The code on the worksheet (for the button) is as follows:
>
> Private Sub ProtectionToggle_Click()
> If Sheet14.ProtectContents = True Then 'True means that protection is on!
> With ProtectionToggle
> .Value = False
> .Caption = "Protect Sheets"
> End With
> Application.Run "RunProtectionPasswordUserForm"
> ElseIf Sheet14.ProtectContents = False Then
> ans = MsgBox("Are you sure you want to protect the worksheet?" _
> & vbLf & "This action cannot be undone without a password.",
> vbOKCancel, _
> "Confirm Protect Sheets")
> If ans = vbOK Then
> With ProtectionToggle
> .Value = False
> .Caption = "Begin Advanced Editing"
> End With
> End If
> Application.Run "WorkbookProtect"
> End If
> End Sub
>
> The code for the userform OK button is as follows:
>
> Private Sub OKButton_Click()
> Application.Run "FilterEstimateData.ConfirmPassword"
> UnLoad Me
> End Sub
>
> The problem that I am having is that the userform is, in fact, unloading,
> but reloading. It consistently unloads upon OK then reloads once (if I hit
> OK again with the right password, it does not reload again).
>
> Any suggestions? Please help!
>
> NB

To call a procedure, you just type Call ProcedureName? I'll try that.
That's what happens when you're teaching yourself to program from books...you
don't learn the best way to do things...

Here are the subprocedures, in order as best as they can be (they look
somewhat circular but I don't think they really are). By the way, the msgbox
pops up twice, too, as well as the userform.

Private Sub ToggleEstimateFilters_Click()
Application.ScreenUpdating = False
If Sheet2.FilterMode = True Then
With ToggleEstimateFilters
.Caption = " Hide Items Not in Estimate"
.Value = False
End With
Application.Run "RunEstPasswordUserForm"
ElseIf Sheet2.FilterMode = False Then
ans = MsgBox("Are you sure you want to hide the items that are not in
the estimate?" _
& vbLf & "This action cannot be undone without a password.",
vbOKCancel + vbInformation, _
"Confirm Filter")
If ans = vbOK Then
With ToggleEstimateFilters
.Caption = " Show All Items"
.Value = False
End With
Application.Run "FilterEstimateData.FilterEstimateData"
End If
If ans = vbCancel Then End
End If
Application.ScreenUpdating = True
End Sub

Private Sub RunEstPasswordUserForm()
EstimatePassword.Show
End Sub

Private Sub OKButton_Click()
Application.Run "FilterEstimateData.ConfirmPassword"
End Sub

Private Sub ConfirmPassword()
PasswordText = EstimatePassword.PasswordTextBox.Text
Unload EstimatePassword
If PasswordText = "estimate" Then
Application.Run "RemoveEstimateFilter"
ElseIf PasswordText <> "estimate" Then
Application.Run "WrongEstPasswordMsgBox"
End If
End Sub

On Error Resume Next

Application.StatusBar = "Removing Estimate Filters"
Dim ProtectionStatus As Boolean
If Sheet1.ProtectContents = True Then
ProtectionStatus = True
End If
If Sheet1.ProtectContents = False Then
ProtectionStatus = False
End If
If Sheet2.FilterMode = True Then
Application.Run "WorkbookUnprotect"
Application.ScreenUpdating = False
For i = 2 To 12
Worksheets(i).ShowAllData
Next
Range("TlLumber", "BlumberEstQty").Locked = False
Range("TlShores", "BShoresEstQty").Locked = False
Range("TLPlywood", "BPlywoodEstQty").Locked = False
Range("TLHardware", "BHardwareEstQty").Locked = False
Range("TLScaffold", "BScaffoldEstQty").Locked = False
Range("TLPurlinsRunners", "BPurlinsRunnersEstQty").Locked = False
Range("TLHVDoka", "BHVDokaEstQty").Locked = False
Range("TLCecoWalls", "BCecowallsEstQty").Locked = False
Range("TLBurkeHandset", "BBurkeHandsetEstQty").Locked = False
Range("TLBurkeCranset", "BBurkeCranesetEstQty").Locked = False
Range("TLPansEquip", "BPansEquipEstQty").Locked = False
ElseIf Sheet12.FilterMode = False Then Resume Next
End If
If ProtectionStatus = True Then
Application.Run "WorkbookProtect"
ElseIf ProtectionStatus = False Then Resume Next
End If
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Sorry it's so lengthy!

I tried to call procedures, and it didn't work, and when I looked it up, it
said that you cannot call procedures marked as Private from other modules.
My project is broken into several modules because there is so much code. I
believe, especially because all references that I've used in learning to
program teach it the same way, that using application.run is just about the
same thing, only it can do it from other modules.

I am going to stick with a few generallities here to see if we can clean
things up. At the top of your code modules ad teh word Option Explicit . This
will require explicit variable declarations. When you have done this click
Debug -> Compile. Clean up any sysntax errors that the compiler finds. In the
code that you posted the list procedure is not declared with a procedure
name. Once this is done Place your cursor in the ProtectionToggle_Click
precedure and hit F8. This will allow you to trace your code one line at a
time and see what is occuring as the code executes. Hopefully this will help
you find your problem.

With respect to the Call of procedures. The Idea behind public and private
is to hide or expose the functionallity of specific sheets and modules from
other sheets and modules. The rule o fthumb is to try to keep as much private
as is possible, but if the proceudure is required outside the moduel then
make it public. Try this to see what I mean. Insert a new module into your
project. Create two sub procedures. one public and one private. Something
like this... Name the module modMyModule

public sub MyPublicMessage ()
msgbox "Public message"
end sub

private sub MyPrivateMessage()
msgbox "Private Message"
end sub

Now in one of your other modules type

Call modMyModule.

The intellisence will give you a list showing you your public procedure. It
will also show any public variables...

HTH

"Nicole B" wrote:

> To call a procedure, you just type Call ProcedureName? I'll try that.
> That's what happens when you're teaching yourself to program from books...you
> don't learn the best way to do things...
>
> Here are the subprocedures, in order as best as they can be (they look
> somewhat circular but I don't think they really are). By the way, the msgbox
> pops up twice, too, as well as the userform.
>
> Private Sub ToggleEstimateFilters_Click()
> Application.ScreenUpdating = False
> If Sheet2.FilterMode = True Then
> With ToggleEstimateFilters
> .Caption = " Hide Items Not in Estimate"
> .Value = False
> End With
> Application.Run "RunEstPasswordUserForm"
> ElseIf Sheet2.FilterMode = False Then
> ans = MsgBox("Are you sure you want to hide the items that are not in
> the estimate?" _
> & vbLf & "This action cannot be undone without a password.",
> vbOKCancel + vbInformation, _
> "Confirm Filter")
> If ans = vbOK Then
> With ToggleEstimateFilters
> .Caption = " Show All Items"
> .Value = False
> End With
> Application.Run "FilterEstimateData.FilterEstimateData"
> End If
> If ans = vbCancel Then End
> End If
> Application.ScreenUpdating = True
> End Sub
>
> Private Sub RunEstPasswordUserForm()
> EstimatePassword.Show
> End Sub
>
> Private Sub OKButton_Click()
> Application.Run "FilterEstimateData.ConfirmPassword"
> End Sub
>
> Private Sub ConfirmPassword()
> PasswordText = EstimatePassword.PasswordTextBox.Text
> Unload EstimatePassword
> If PasswordText = "estimate" Then
> Application.Run "RemoveEstimateFilter"
> ElseIf PasswordText <> "estimate" Then
> Application.Run "WrongEstPasswordMsgBox"
> End If
> End Sub
>
> On Error Resume Next
>
> Application.StatusBar = "Removing Estimate Filters"
> Dim ProtectionStatus As Boolean
> If Sheet1.ProtectContents = True Then
> ProtectionStatus = True
> End If
> If Sheet1.ProtectContents = False Then
> ProtectionStatus = False
> End If
> If Sheet2.FilterMode = True Then
> Application.Run "WorkbookUnprotect"
> Application.ScreenUpdating = False
> For i = 2 To 12
> Worksheets(i).ShowAllData
> Next
> Range("TlLumber", "BlumberEstQty").Locked = False
> Range("TlShores", "BShoresEstQty").Locked = False
> Range("TLPlywood", "BPlywoodEstQty").Locked = False
> Range("TLHardware", "BHardwareEstQty").Locked = False
> Range("TLScaffold", "BScaffoldEstQty").Locked = False
> Range("TLPurlinsRunners", "BPurlinsRunnersEstQty").Locked = False
> Range("TLHVDoka", "BHVDokaEstQty").Locked = False
> Range("TLCecoWalls", "BCecowallsEstQty").Locked = False
> Range("TLBurkeHandset", "BBurkeHandsetEstQty").Locked = False
> Range("TLBurkeCranset", "BBurkeCranesetEstQty").Locked = False
> Range("TLPansEquip", "BPansEquipEstQty").Locked = False
> ElseIf Sheet12.FilterMode = False Then Resume Next
> End If
> If ProtectionStatus = True Then
> Application.Run "WorkbookProtect"
> ElseIf ProtectionStatus = False Then Resume Next
> End If
> Application.ScreenUpdating = True
> Application.StatusBar = False
> End Sub
>
> Sorry it's so lengthy!

I don't want to leavy you high and dry but I have to go now. Re-post tomorrow
if it still won't work.

"Jim Thomlinson" wrote:

> I am going to stick with a few generallities here to see if we can clean
> things up. At the top of your code modules ad teh word Option Explicit . This
> will require explicit variable declarations. When you have done this click
> Debug -> Compile. Clean up any sysntax errors that the compiler finds. In the
> code that you posted the list procedure is not declared with a procedure
> name. Once this is done Place your cursor in the ProtectionToggle_Click
> precedure and hit F8. This will allow you to trace your code one line at a
> time and see what is occuring as the code executes. Hopefully this will help
> you find your problem.
>
> With respect to the Call of procedures. The Idea behind public and private
> is to hide or expose the functionallity of specific sheets and modules from
> other sheets and modules. The rule o fthumb is to try to keep as much private
> as is possible, but if the proceudure is required outside the moduel then
> make it public. Try this to see what I mean. Insert a new module into your
> project. Create two sub procedures. one public and one private. Something
> like this... Name the module modMyModule
>
> public sub MyPublicMessage ()
> msgbox "Public message"
> end sub
>
> private sub MyPrivateMessage()
> msgbox "Private Message"
> end sub
>
> Now in one of your other modules type
>
> Call modMyModule.
>
> The intellisence will give you a list showing you your public procedure. It
> will also show any public variables...
>
> HTH
>
> "Nicole B" wrote:
>
> > To call a procedure, you just type Call ProcedureName? I'll try that.
> > That's what happens when you're teaching yourself to program from books...you
> > don't learn the best way to do things...
> >
> > Here are the subprocedures, in order as best as they can be (they look
> > somewhat circular but I don't think they really are). By the way, the msgbox
> > pops up twice, too, as well as the userform.
> >
> > Private Sub ToggleEstimateFilters_Click()
> > Application.ScreenUpdating = False
> > If Sheet2.FilterMode = True Then
> > With ToggleEstimateFilters
> > .Caption = " Hide Items Not in Estimate"
> > .Value = False
> > End With
> > Application.Run "RunEstPasswordUserForm"
> > ElseIf Sheet2.FilterMode = False Then
> > ans = MsgBox("Are you sure you want to hide the items that are not in
> > the estimate?" _
> > & vbLf & "This action cannot be undone without a password.",
> > vbOKCancel + vbInformation, _
> > "Confirm Filter")
> > If ans = vbOK Then
> > With ToggleEstimateFilters
> > .Caption = " Show All Items"
> > .Value = False
> > End With
> > Application.Run "FilterEstimateData.FilterEstimateData"
> > End If
> > If ans = vbCancel Then End
> > End If
> > Application.ScreenUpdating = True
> > End Sub
> >
> > Private Sub RunEstPasswordUserForm()
> > EstimatePassword.Show
> > End Sub
> >
> > Private Sub OKButton_Click()
> > Application.Run "FilterEstimateData.ConfirmPassword"
> > End Sub
> >
> > Private Sub ConfirmPassword()
> > PasswordText = EstimatePassword.PasswordTextBox.Text
> > Unload EstimatePassword
> > If PasswordText = "estimate" Then
> > Application.Run "RemoveEstimateFilter"
> > ElseIf PasswordText <> "estimate" Then
> > Application.Run "WrongEstPasswordMsgBox"
> > End If
> > End Sub
> >
> > On Error Resume Next
> >
> > Application.StatusBar = "Removing Estimate Filters"
> > Dim ProtectionStatus As Boolean
> > If Sheet1.ProtectContents = True Then
> > ProtectionStatus = True
> > End If
> > If Sheet1.ProtectContents = False Then
> > ProtectionStatus = False
> > End If
> > If Sheet2.FilterMode = True Then
> > Application.Run "WorkbookUnprotect"
> > Application.ScreenUpdating = False
> > For i = 2 To 12
> > Worksheets(i).ShowAllData
> > Next
> > Range("TlLumber", "BlumberEstQty").Locked = False
> > Range("TlShores", "BShoresEstQty").Locked = False
> > Range("TLPlywood", "BPlywoodEstQty").Locked = False
> > Range("TLHardware", "BHardwareEstQty").Locked = False
> > Range("TLScaffold", "BScaffoldEstQty").Locked = False
> > Range("TLPurlinsRunners", "BPurlinsRunnersEstQty").Locked = False
> > Range("TLHVDoka", "BHVDokaEstQty").Locked = False
> > Range("TLCecoWalls", "BCecowallsEstQty").Locked = False
> > Range("TLBurkeHandset", "BBurkeHandsetEstQty").Locked = False
> > Range("TLBurkeCranset", "BBurkeCranesetEstQty").Locked = False
> > Range("TLPansEquip", "BPansEquipEstQty").Locked = False
> > ElseIf Sheet12.FilterMode = False Then Resume Next
> > End If
> > If ProtectionStatus = True Then
> > Application.Run "WorkbookProtect"
> > ElseIf ProtectionStatus = False Then Resume Next
> > End If
> > Application.ScreenUpdating = True
> > Application.StatusBar = False
> > End Sub
> >
> > Sorry it's so lengthy!

Thanks, Jim. I'm working on it...I'll get back to you tomorrow if I'm still
stuck.

"Jim Thomlinson" wrote:

> I don't want to leavy you high and dry but I have to go now. Re-post tomorrow
> if it still won't work.
>
> "Jim Thomlinson" wrote:
>
> > I am going to stick with a few generallities here to see if we can clean
> > things up. At the top of your code modules ad teh word Option Explicit . This
> > will require explicit variable declarations. When you have done this click
> > Debug -> Compile. Clean up any sysntax errors that the compiler finds. In the
> > code that you posted the list procedure is not declared with a procedure
> > name. Once this is done Place your cursor in the ProtectionToggle_Click
> > precedure and hit F8. This will allow you to trace your code one line at a
> > time and see what is occuring as the code executes. Hopefully this will help
> > you find your problem.
> >
> > With respect to the Call of procedures. The Idea behind public and private
> > is to hide or expose the functionallity of specific sheets and modules from
> > other sheets and modules. The rule o fthumb is to try to keep as much private
> > as is possible, but if the proceudure is required outside the moduel then
> > make it public. Try this to see what I mean. Insert a new module into your
> > project. Create two sub procedures. one public and one private. Something
> > like this... Name the module modMyModule
> >
> > public sub MyPublicMessage ()
> > msgbox "Public message"
> > end sub
> >
> > private sub MyPrivateMessage()
> > msgbox "Private Message"
> > end sub
> >
> > Now in one of your other modules type
> >
> > Call modMyModule.
> >
> > The intellisence will give you a list showing you your public procedure. It
> > will also show any public variables...
> >
> > HTH
> >
> > "Nicole B" wrote:
> >
> > > To call a procedure, you just type Call ProcedureName? I'll try that.
> > > That's what happens when you're teaching yourself to program from books...you
> > > don't learn the best way to do things...
> > >
> > > Here are the subprocedures, in order as best as they can be (they look
> > > somewhat circular but I don't think they really are). By the way, the msgbox
> > > pops up twice, too, as well as the userform.
> > >
> > > Private Sub ToggleEstimateFilters_Click()
> > > Application.ScreenUpdating = False
> > > If Sheet2.FilterMode = True Then
> > > With ToggleEstimateFilters
> > > .Caption = " Hide Items Not in Estimate"
> > > .Value = False
> > > End With
> > > Application.Run "RunEstPasswordUserForm"
> > > ElseIf Sheet2.FilterMode = False Then
> > > ans = MsgBox("Are you sure you want to hide the items that are not in
> > > the estimate?" _
> > > & vbLf & "This action cannot be undone without a password.",
> > > vbOKCancel + vbInformation, _
> > > "Confirm Filter")
> > > If ans = vbOK Then
> > > With ToggleEstimateFilters
> > > .Caption = " Show All Items"
> > > .Value = False
> > > End With
> > > Application.Run "FilterEstimateData.FilterEstimateData"
> > > End If
> > > If ans = vbCancel Then End
> > > End If
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > > Private Sub RunEstPasswordUserForm()
> > > EstimatePassword.Show
> > > End Sub
> > >
> > > Private Sub OKButton_Click()
> > > Application.Run "FilterEstimateData.ConfirmPassword"
> > > End Sub
> > >
> > > Private Sub ConfirmPassword()
> > > PasswordText = EstimatePassword.PasswordTextBox.Text
> > > Unload EstimatePassword
> > > If PasswordText = "estimate" Then
> > > Application.Run "RemoveEstimateFilter"
> > > ElseIf PasswordText <> "estimate" Then
> > > Application.Run "WrongEstPasswordMsgBox"
> > > End If
> > > End Sub
> > >
> > > On Error Resume Next
> > >
> > > Application.StatusBar = "Removing Estimate Filters"
> > > Dim ProtectionStatus As Boolean
> > > If Sheet1.ProtectContents = True Then
> > > ProtectionStatus = True
> > > End If
> > > If Sheet1.ProtectContents = False Then
> > > ProtectionStatus = False
> > > End If
> > > If Sheet2.FilterMode = True Then
> > > Application.Run "WorkbookUnprotect"
> > > Application.ScreenUpdating = False
> > > For i = 2 To 12
> > > Worksheets(i).ShowAllData
> > > Next
> > > Range("TlLumber", "BlumberEstQty").Locked = False
> > > Range("TlShores", "BShoresEstQty").Locked = False
> > > Range("TLPlywood", "BPlywoodEstQty").Locked = False
> > > Range("TLHardware", "BHardwareEstQty").Locked = False
> > > Range("TLScaffold", "BScaffoldEstQty").Locked = False
> > > Range("TLPurlinsRunners", "BPurlinsRunnersEstQty").Locked = False
> > > Range("TLHVDoka", "BHVDokaEstQty").Locked = False
> > > Range("TLCecoWalls", "BCecowallsEstQty").Locked = False
> > > Range("TLBurkeHandset", "BBurkeHandsetEstQty").Locked = False
> > > Range("TLBurkeCranset", "BBurkeCranesetEstQty").Locked = False
> > > Range("TLPansEquip", "BPansEquipEstQty").Locked = False
> > > ElseIf Sheet12.FilterMode = False Then Resume Next
> > > End If
> > > If ProtectionStatus = True Then
> > > Application.Run "WorkbookProtect"
> > > ElseIf ProtectionStatus = False Then Resume Next
> > > End If
> > > Application.ScreenUpdating = True
> > > Application.StatusBar = False
> > > End Sub
> > >
> > > Sorry it's so lengthy!