klutz
07-28-2009, 12:09 PM
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
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