Solved: Toggle:Hide/Unhide Issue with calling a module

[klutz]

I have this macro for a toggle button. It performs the task accordingly.

i want to be able to call a module when on click of toggle is TRUE, when on click is FALSE I don;t want the module to be called.

Currently I have set as below. But When toggle is false it does not appear, when it is true it appears. I click again FALSE but it does not DISAPPEAR, I click again TRUE and it does not disappear, I click once again FALSE and it appears and then click again TRUE and still appears.

The toggle is set for False and is not triple phase.

Any thoughts?..:help


Private Sub ToggleButton2_Click()
With Worksheets("Allocations 2")
ActiveSheet.Unprotect Password:="PWD"

.Columns(9).Hidden = ToggleButton2.Value
.Columns(10).Hidden = Not ToggleButton2.Value
.Columns(31).Hidden = ToggleButton2.Value
.Columns(34).Hidden = ToggleButton2.Value
.Columns(43).Hidden = Not ToggleButton2.Value
.Columns(69).Hidden = ToggleButton2.Value
.Columns(76).Hidden = ToggleButton2.Value
.Columns(70).Hidden = Not ToggleButton2.Value
.Columns(77).Hidden = Not ToggleButton2.Value




If ToggleButton2.Value = True Then
Call Module16.LargestGPW

End If

End With

With Worksheets("Fees")

.Columns(2).Hidden = Not ToggleButton2.Value
.Columns(3).Hidden = ToggleButton2.Value
.Columns(9).Hidden = Not ToggleButton2.Value
.Columns(18).Hidden = Not ToggleButton2.Value
.Columns(8).Hidden = ToggleButton2.Value
.Columns(17).Hidden = ToggleButton2.Value
End With
ActiveSheet.Protect Password:="PWD", _


End Sub




This is what's in module 16:


Sub LargestGPW()


Static rngData As Range
Dim lngLastRow As Long, rngCell As Range

ActiveSheet.Unprotect Password:="PWD"
If rngData Is Nothing Then
lngLastRow = Cells(Rows.Count, "g").End(xlUp).row
On Error Resume Next
Set rngData = Range("G26:G" & lngLastRow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngData Is Nothing Then
For Each rngCell In rngData.Cells
With rngCell
.Offset(3, -2).FormulaR1C1 = "=LOOKUP(2,1/((R27C7:R" & lngLastRow & "C7=R[-2]C[2])
*(R27C28:R" & _
lngLastRow & "C28=R[1]C)),R27C11:R" & lngLastRow & "C11)"
.Offset(4, -2).FormulaArray = "=MAX(IF(R27C7:R" & lngLastRow
& "C7=R[-3]C[2],R27C28:R" & lngLastRow & "C28))"

End With
Next rngCell
End If

Else
' rngData was set last time it was run


For Each rngCell In rngData.Cells
With rngCell
.Offset(3, -2).ClearContents
.Offset(4, -2).ClearContents
End With
Next rngCell
Set rngData = Nothing ' Clear the range variable ready for next 'Click'


ActiveSheet.Protect Password:="PWD"
End If
End Sub


Klutz

[Bob Phillips]

I am not sure what you mean, the togglebutton test fine as I see.

[Benzadeus]

I tested bypassing the Module 16 call and the macro worked perfectly.

[klutz]

Exactly, which is why my confusion.

When I toggle for
1. false: it disappears,
2. true: it appears.

When I togle for
3. FALSE again it does not disappear,
4. TRUE it does not disappear,

then I toggle again for
5. FALSE and it disappears
6. TRUE it dissapper

The I toggle again for
7. false: it appears,
8. true: it appears.

Not sure why it is doing this...

[Bob Phillips]

What is IT in this situation, you keep calling it IT, but I have no idea what IT is.

[klutz]

XLD,

IT refers to the data that the macro from module 16 generates...

[Bob Phillips]

I think you need to step through it in debug as you seem to be saying the macro is sometimes invoked, sometimes not, for both true and false toggles. My reading of the code is that it is ONLY invoked for true.

[p45cal]

Just tweaked to make it work, so it won't necessarily have the best logic in it:
Change the call to LargestGPW to (and call it unconditionally, that is, remove the ' If ToggleButton2.Value = True Then')
:
Call Module16.LargestGPW(ToggleButton2.Value)
Make the highlighted changes to LargestGPW itself:
Sub LargestGPW(ToggleOn As Boolean)
Static rngData As Range
Dim lngLastRow As Long, rngCell As Range
ActiveSheet.Unprotect Password:="PWD"
If rngData Is Nothing And ToggleOn Then
lngLastRow = Cells(Rows.Count, "g").End(xlUp).Row
On Error Resume Next
Set rngData = Range("G26:G" & lngLastRow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngData Is Nothing Then
For Each rngCell In rngData.Cells
With rngCell
.Offset(3, -2).FormulaR1C1 = "=LOOKUP(2,1/((R27C7:R" & lngLastRow & "C7=R[-2]C[2])*(R27C28:R" & _
lngLastRow & "C28=R[1]C)),R27C11:R" & lngLastRow & "C11)"
.Offset(4, -2).FormulaArray = "=MAX(IF(R27C7:R" & lngLastRow & "C7=R[-3]C[2],R27C28:R" & lngLastRow & "C28))"
End With
Next rngCell
End If
Else
If Not ToggleOn And Not rngData Is Nothing Then
For Each rngCell In rngData.Cells
With rngCell
.Offset(3, -2).ClearContents
.Offset(4, -2).ClearContents
End With
Next rngCell
Set rngData = Nothing ' Clear the range variable ready for next 'Click'
ActiveSheet.Protect Password:="PWD"
End If
End If
End Sub

[klutz]

pCAL45,
Absolutely great, it works as originally wanted. Now when I toggle (false) the data from module 16 is not there and whentoggle for true the data is inserted.

Thanks for all your help.

I LO:loveVE this SITE :cloud9:

The Klutz...

[p45cal]

What is IT in this situation, you keep calling it IT, but I have no idea what IT is. A Clintonesque moment?.. 'it depends on what the definition of it is'
(slightly misquoted)

[klutz]

ROFL

:rotlaugh:It happens sometimes...:think: