Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - Toggle Button Code 1

Status
Not open for further replies.

shytott

Technical User
Aug 25, 2003
131
GB
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
 
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.
 
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
 
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
 
Being a OCD Access developer, I would give C5 a descriptive range name and then use the name in the VBA.
 
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
 
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.
 
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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top