PDA

View Full Version : Solved: Macro in a Toggle Button



klutz
07-27-2009, 06:51 PM
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?:hi:

Gracias...

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

GTO
07-27-2009, 08:31 PM
Greetings,

If I am understanding correctly, an easy way should be to duplicate the procedure, changing to clear the cells...

With rngCell
.Offset(3, -2).ClearContents
' etc


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


Hope that helps,

Mark

klutz
07-28-2009, 09:12 AM
Ok Kool, :beerchug:


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

This forum is great, full of selflessness wonderful contributors.

Gracias...:thumb