Solved: Macro in a Toggle Button

[klutz]

Hello ,

I was wondering if it's possible to use this macro code in a toggle to perform this action when it's true and when the toggle is click again it can undo it.

This macro does lookups and finds the max on sets of value when it is run and i was wondering if by clicking the button again it can undo what it placed in the cells...

Any thoughts?

Gracias...

[vba]Sub LargestGPW()

ActiveSheet.Unprotect Password:="PWD"

Dim lngLastRow As Long, rngData As Range, rngCell As Range
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
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

ActiveSheet.Protect Password:="PWD",

End Sub[/vba]

[GTO]

Greetings,

If I am understanding correctly, an easy way should be to duplicate the procedure, changing to clear the cells...
[vba]
With rngCell
.Offset(3, -2).ClearContents
' etc
[/vba]

Then use the ToggleButton's Click event, and an IF statement:
[VBA] If Togglebutton.Value Then
'run one macro
Else
'run the other
End If
[/VBA]

Hope that helps,

Mark

[klutz]

Ok Kool,


Your suggestions is great. did it, it works...

This forum is great, full of selflessness wonderful contributors.

Gracias...