Excel - Toggle Button Code 1

[shytott]

Hi

I would like to create a toggle button which places or deletes an “X” in cell C5, but I am struggling with the syntax. Could anyone point me in the right direction please?

Many Thanks

 

[Jim E]

I use this approach for something similar to save time balancing my check register. I have a 'Form Control' (not Active X) check box in each cell next to the check number. When the check is cleared, I click the check box, and it turns the check number cell green. First, the control only generates a text "TRUE" or "FALSE". I added a conditional formatting formula to check and turn the cell green if True. Further, the I changed the font color to white in the cell I placed the control in so it would not be confusing. Instead of using conditional formatting, a simple formula to test for True/False value and place the "X" would also work. With the Form Control, no VBA is required.

 

[shytott]

Hi Jim
Thanks for your reply. I think what I'm in need of is possibly an 'If, Then, Else' statement which toggles between the cell contents being either "X" or blank, but all my attempts end up with 'Compile errors'. I know it will be something really simple, but its eluding me so far.

Cheers

 

[Jim E]

Hi, I used something like this for another application, You need to create a button and assign the VBA to it when you want it to run. Try this VBA:
Sub Button1_Click()
Dim cell As Range
Set cell = ThisWorkbook.Sheets("Sheet1").Range("C5")
If cell.Value = "X" Then
cell.Value = "" ' Delete the "X"
Else
cell.Value = "X" ' Place an "X"
End If
End Sub

 

[dhookom]

Being a OCD Access developer, I would give C5 a descriptive range name and then use the name in the VBA.

 

[shytott]

Hi, I used something like this for another application, You need to create a button and assign the VBA to it when you want it to run. Try this VBA:
Sub Button1_Click()
Dim cell As Range
Set cell = ThisWorkbook.Sheets("Sheet1").Range("C5")
If cell.Value = "X" Then
cell.Value = "" ' Delete the "X"
Else
cell.Value = "X" ' Place an "X"
End If
End Sub

Brilliant! That does the job.

Many Thanks jim, much appreciate your help!
Andy

 

[shytott]

Being a OCD Access developer, I would give C5 a descriptive range name and then use the name in the VBA.

Cheers Duane, I did just that!
Many thanks.

 

[mintjulep]

What's the use of the "X"?

Are you using it for something like "=if(C5="X","C5 is an X", "C5 is not X")

If so, then toggle a BOOLEAN in C5 so that it's directly useable.