Toggle Button Updating Concern
 

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

Toggle Button Updating Concern
 

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!

Toggle Button Updating Concern
 

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!

Toggle Button Updating Concern
 

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!