asked on

Make command button work as a toggle button vba

I currently have a command button set with the code below. I would like to make this command button more like a toggle button so that when you click it the first time it will produce the totals as it does now, but if you click it again it will remove the totals (or undo the command)..

Private Sub CommandButton1_Click()
 
Dim lrow As Long
lrow = Cells(65536, "D").End(xlUp).Row
Cells(lrow + 2, "I").Formula = "=sum(I16:I" & lrow & ")"
Cells(lrow + 2, "J").Formula = "=sum(J16:J" & lrow & ")"
Cells(lrow + 2, "K").Formula = "=sum(K16:K" & lrow & ")"
Cells(lrow + 2, "L").Formula = "=sum(L16:L" & lrow & ")"
Cells(lrow + 2, "M").Formula = "=sum(M16:M" & lrow & ")"
Cells(lrow + 2, "N").Formula = "=sum(N16:N" & lrow & ")"
Cells(lrow + 2, "O").Formula = "=sum(O16:O" & lrow & ")"
Cells(lrow + 2, "F") = "Total Hours"
Range("I" & lrow + 2 & ":O" & lrow + 2).Font.Bold = True
Cells(lrow + 2, "F").Font.Bold = True
End Sub

Open in new window

Steve Dubyo

There is a toggle button that you can insert instead of a command button. When you use the toggle button you can check the true/false value property of the button in the click event, so your code would look something like this..

Private Sub ToggleButton1_Click()
    If ToggleButton1.Value Then
        'Toggle on code
        Dim lrow As Long
        lrow = Cells(65536, "D").End(xlUp).Row
        Cells(lrow + 2, "I").Formula = "=sum(I16:I" & lrow & ")"
        Cells(lrow + 2, "J").Formula = "=sum(J16:J" & lrow & ")"
        Cells(lrow + 2, "K").Formula = "=sum(K16:K" & lrow & ")"
        Cells(lrow + 2, "L").Formula = "=sum(L16:L" & lrow & ")"
        Cells(lrow + 2, "M").Formula = "=sum(M16:M" & lrow & ")"
        Cells(lrow + 2, "N").Formula = "=sum(N16:N" & lrow & ")"
        Cells(lrow + 2, "O").Formula = "=sum(O16:O" & lrow & ")"
        Cells(lrow + 2, "F") = "Total Hours"
        Range("I" & lrow + 2 & ":O" & lrow + 2).Font.Bold = True
        Cells(lrow + 2, "F").Font.Bold = True
    Else
        'Toggle Off code
        Dim lrow As Long
        lrow = Cells(65536, "D").End(xlUp).Row
        Cells(lrow + 2, "I").Formula = ""
        Cells(lrow + 2, "J").Formula = ""
        Cells(lrow + 2, "K").Formula = ""
        Cells(lrow + 2, "L").Formula = ""
        Cells(lrow + 2, "M").Formula = ""
        Cells(lrow + 2, "N").Formula = ""
        Cells(lrow + 2, "O").Formula = ""
        Cells(lrow + 2, "F") = ""
        Range("I" & lrow + 2 & ":O" & lrow + 2).Font.Bold = False
        Cells(lrow + 2, "F").Font.Bold = False
    End If
End Sub

Open in new window

RRUTLAND5

ASKER

Thanks....I'm getting a "compile error: Duplicate declaration in current scope" at: lrow As Long in the Toggle Off Code

ASKER CERTIFIED SOLUTION

Steve Dubyo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial