Enable/activate toggle button with a macro

[RPM7]

I'm trying to create a macro to reset the information in my worksheet to its original settings/ cell values however I have two toggle buttons in the worksheet.

I thought I could just insert:

If ToggleButton1.Value = False Then
ToggleButton1.Value = True
End if

As you can see from my post, this obviously didn't work.
I've looked online but can't see this referenced anywhere.
Can anyone help? Thanks.

 

[MickG]

Hi, This worked for me.

Private Sub ToggleButton1_Click()
If ToggleButton1 = False Then
ToggleButton2 = True
Else
ToggleButton2 = False
 End If
[a1] = ToggleButton1
End Sub
 
Private Sub ToggleButton2_Click()
If ToggleButton2 = False Then
ToggleButton1 = True
Else
ToggleButton1 = False
 End If
[B1] = ToggleButton2
End Sub

Regards Mick

 

[RPM7]

Thanks Mick, but the problem I have is that I'm trying to run a macro button that resets everything and if the two toggle buttons are active it should switch them off.

 

[MickG]

Hi, What do you mean by switch then Off.
Do you mean if they are True then set them to False or set them both to False Or stop the from working as toggle buttons or something else !!.

If you want to reset them to the opposite of what they are, why can't you use this code, but in a Command Button.

If your trying to change the settig to the way they where when you opened the sheet, you could save the toggle Buttons setting in a variable, Using a "sheet activate" event , then Reset the Buttons when closing the sheet or when required.

Regards Mick

 

[RPM7]

Basically, if the toggle buttons have being enabled. they would be turned off (both of them).

This worksheet is used as a calculator by myself and some of my colleagues at work. I've set the default values to match our most common machine. The toggle buttons allow us to adjust the calculations for customizing.

At the minute if I press the reset button all cells return to the default value but the two toggle buttons could remain switched on which is not ideal and I'd like to deactivate them with the same macro button. (One button to do the whole lot).

 

[MickG]

Hi, You could try this, and incorporate it into your ,command Button.
Nb:- Click once for "True" , click Again for "False".

Option Explicit
[COLOR=navy]Dim[/COLOR] State [COLOR=navy]As[/COLOR] Boolean
Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]If[/COLOR] State = True [COLOR=navy]Then[/COLOR] State = False Else State = True
ToggleButton1.Enabled = State
ToggleButton2.Enabled = State
CommandButton1.Caption = State
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Hi, Bit of over Kill with previous code, all part of the thinking process, This works !!!

Private [COLOR="Navy"]Sub[/COLOR] CommandButton3_Click()
[COLOR="Navy"]With[/COLOR] ToggleButton1
    [COLOR="Navy"]If[/COLOR] .Enabled = True [COLOR="Navy"]Then[/COLOR] .Enabled = False Else .Enabled = True
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] ToggleButton2
    [COLOR="Navy"]If[/COLOR] .Enabled = True [COLOR="Navy"]Then[/COLOR] .Enabled = False Else .Enabled = True
    CommandButton3.Caption = .Enabled
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[RPM7]

thanks for all your help Mick. I'm still having a bit of trouble even when using your code.
I tried using it on its own and then along with what I'm currently using.

This is the whole lot for the reset:


Code:

Sub Reset_cells_imperial()
'
' Reset_cells_imperial Macro
'
'
Application.ScreenUpdating = False
Sheets("Sheet3").Select
Range("D2,D4,A12").Select
Selection.ClearContents
Range("D3").Select
ActiveCell.FormulaR1C1 = "2"
Range("D2").Select
Sheets("Sheet1").Select
Range("G2:H2").Select
ActiveCell.FormulaR1C1 = "8000"
Range("G7:H7").Select
ActiveCell.FormulaR1C1 = "5000"
Range("G8:H8").Select
ActiveCell.FormulaR1C1 = "4500"
Range("J7:K7").Select
ActiveCell.FormulaR1C1 = "600"
Range("G10:K15,J10:K11,E18:E23,J28:L28,C18:C27").Select
Selection.ClearContents
Range("G10").Select
ActiveCell.FormulaR1C1 = "Attach. 1"
Range("C28").Select
ActiveCell.FormulaR1C1 = "600"
Range("F28").Select
ActiveCell.FormulaR1C1 = "24"
Range("G28").Select
ActiveCell.FormulaR1C1 = "30"
Range("H28").Select
ActiveCell.FormulaR1C1 = "36"
Range("E27").Select
ActiveCell.FormulaR1C1 = "10.5"
Range("E26").Select
ActiveCell.FormulaR1C1 = "15"
Range("E25").Select
ActiveCell.FormulaR1C1 = "18"
Range("E24").Select
ActiveCell.FormulaR1C1 = "20"
Range("G11").Select
Sheets("Sheet2").Select
Range("D13,D13,E13,g14").Select
Selection.ClearContents



Range("H16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=1

Range("I16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=2

Range("J16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=3

Range("K16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=4

Range("L16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=5

Range("M16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=6

Range("N16").Select
ActiveSheet.Range("$H$16:$N$47").AutoFilter Field:=7

Range("c13").Select
Sheets("Sheet1").Select


With ToggleButton1
If .Enabled = True Then .Enabled = False Else .Enabled = True
End With
With ToggleButton2
If .Enabled = True Then .Enabled = False Else .Enabled = True
End With

Application.ScreenUpdating = True

End Sub

 

[MickG]

Hi, I'm not sure how all your code relates to your Data, but I've reduced it somewhat in size to get it working, hopefully all the detail is still there.
NB:- It is very seldom that you need to "Select" ranges to work with them.
Have a play with this see what you make of it, I've tried not change too much, although it could do with a bit more attention.
It worked on Concocted data I used.
NB:- I did note that when I first ran this code, the code stopped at the fist ".clearcontents" statement, but run succesessfully when tried again, So I put a Msgbox "Complete" at the end , so you can see if the code runs all the way through.

Sub Reset_cells_imperial()
'
' Reset_cells_imperial Macro
'
'
Application.ScreenUpdating = False
With Sheets("Sheet3")
.Range("A12,D2,D4").ClearContents
.Range("D3").FormulaR1C1 = "2"
End With
With Sheets("Sheet1")
.Range("G2:H2").FormulaR1C1 = "8000"
.Range("G7:H7").FormulaR1C1 = "5000"
.Range("G8:H8").FormulaR1C1 = "4500"
.Range("J7:K7").FormulaR1C1 = "600"
.Range("G10:K15,J10:K11,E18:E23,J28:L28,C18:C27").ClearContents
.Range("G10").FormulaR1C1 = "Attach. 1"
.Range("C28").FormulaR1C1 = "600"
.Range("F28").FormulaR1C1 = "24"
.Range("G28").FormulaR1C1 = "30"
.Range("H28").FormulaR1C1 = "36"
.Range("E27").FormulaR1C1 = "10.5"
.Range("E26").FormulaR1C1 = "15"
.Range("E25").FormulaR1C1 = "18"
.Range("E24").FormulaR1C1 = "20"
End With
With Sheets("Sheet2")
.Range("D13,D13,E13,g14").ClearContents
.Range("$H$16:$N$47").AutoFilter Field:=1
.Range("$H$16:$N$47").AutoFilter Field:=2
.Range("$H$16:$N$47").AutoFilter Field:=3
.Range("$H$16:$N$47").AutoFilter Field:=4
.Range("$H$16:$N$47").AutoFilter Field:=5
.Range("$H$16:$N$47").AutoFilter Field:=6
.Range("$H$16:$N$47").AutoFilter Field:=7
End With
With Sheets("Sheet1")
With .ToggleButton1
If .Enabled = True Then .Enabled = False Else .Enabled = True
End With
With .ToggleButton2
If .Enabled = True Then .Enabled = False Else .Enabled = True
End With
End With
Application.ScreenUpdating = True
MsgBox "Complete"
End Sub

Regards Mick

 

[RPM7]

Hi Mick,

Thanks for tidying up my code. (Much much better now + you've taught me a new way of writing it. Thanks)
Regarding the toggle buttons. The new code for some reason actually disables the toggle buttons completely & not just turn them off.

If I have a toggle button switched on. The text in the button changes and it turns red.

If I run your code whilst the toggle button is on, it disables the button completely.
The button remains red the text stays the same and the button cannot be pressed (it does nothing). If I press reset once again, it enables the buttons. They stay red and must be pressed to deactivate. (Overall its a bit pointless as the user must reset twice & then manually switch off the toggle buttons)/

I thought if I changed the code from .enabled to .value it might help but that only flicked from one toggle button to another.