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.

 

[combo]

Depending on the purpose of workbook and necessity of other code - if it is the only reason to introduce VBA, I would use data validation instead, with list as entry type and range consisting of two cells, with 'x' in one of them (hard-coded blank in DV list does not work). With entry and error messages it is simple and descriptive UI option.

 

[shytott]

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.

Hi Mintjulep

Thanks for your reply. I use “x” in different ways, but in this case, it was to quickly select/deselect different sets of data to display on a graph. There may be a correct way of doing this which I haven’t yet seen, but using the “x” works.

Cheers

 

[shytott]

Depending on the purpose of workbook and necessity of other code - if it is the only reason to introduce VBA, I would use data validation instead, with list as entry type and range consisting of two cells, with 'x' in one of them (hard-coded blank in DV list does not work). With entry and error messages it is simple and descriptive UI option.

Hi Combo

Thanks for your response, but my use of the “x” in this instance was just to select/deselect different sets of data to be displayed on a graph. It might look clumsy, but Im not very experienced in Excel and using the “x” in this way works!

Many Thanks