VBA Toggle Button to change functionality

[nelsonk16]

Hello,


I am working on creating a toggle button for a complicated spreadsheet. What is supposed to happen is when the toggle is pressed a formula is supposed to drive 4 investment levels keeping all four at the same ratio as when the toggle was pressed. When the toggle is released, the formulas should be removed and only values should remain in the boxes.

What is happening is when the toggle is pressed or released the Else statement runs and the values just get recopied and pasted as values.

Any help is very much appreciated.

Here is my code:

Private Sub ToggleButton1_Click()
    If ToggleButtonState.Value = True Then
        Range("W18").Formula = "=N14/((N14 + Q14 + N18 + Q18))"
        Range("W19").Formula = "=Q14/((N14 + Q14 + N18 + Q18))"
        Range("W20").Formula = "=N18/((N14 + Q14 + N18 + Q18))"
        Range("W21").Formula = "=Q18/((N14 + Q14 + N18 + Q18))"
        Range("X18").Value = "=W18"
        Range("X19").Value = "=W19"
        Range("X20").Value = "=W20"
        Range("X21").Value = "=W21"
        Range("N14:N16").Select
        ActiveCell.FormulaR1C1 = "=ROUND((R[4]C[10]*RC[7]),0)"
        Range("Q14:Q16").Select
        ActiveCell.FormulaR1C1 = "=ROUND((R[5]C[7]*RC[4]),0)"
        Range("N18:N20").Select
        ActiveCell.FormulaR1C1 = "=ROUND((R[2]C[10]*R[-4]C[7]),0)"
        Range("Q18:Q20").Select
        ActiveCell.FormulaR1C1 = "=ROUND((R[3]C[7]*R[-4]C[4]),0)"
        Range("N14:N16,Q14:Q16,N18:N20,Q18:Q20").Select
        Range("Q18").Activate
        Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    Else
        Range("N14:N16").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("N18:N20").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("Q14:Q16").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("Q18:Q20").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
End If
End Sub

 

[nelsonk16]

Bump

 

[Norie]

What's the name of the toggle button?

Is it ToggleButton1 which you use in the Click sub name or is it ToggleButtonState which you use in the code?