-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Toggle button
- Thread starter Bertha
- Start date
MarcelBeug
Well-known Member
- Joined
- Apr 25, 2014
- Messages
- 1,812
No. Buttons are at a different layer.
However you can position a control over a cell, e.g. a check box. If you link that check box to the same cell you are about as close as you can get.
The cell value will toggle between TRUE and FALSE.
By the way there are a lot of different "toggles" in Excel (e.g. turn filter on/off, turn bold on/off), so it's not clear to me what kind of toggle you require.
However you can position a control over a cell, e.g. a check box. If you link that check box to the same cell you are about as close as you can get.
The cell value will toggle between TRUE and FALSE.
By the way there are a lot of different "toggles" in Excel (e.g. turn filter on/off, turn bold on/off), so it's not clear to me what kind of toggle you require.
Upvote
0
Thank you very much, MarcelBeug,
What I would like to do is:
Have a column B with toggable (deselectable/selectable) cells that are all green (selected) by default, green meaning that the value of those cells is TRUE by default. Subsequently I would like to be able to click on some of the cells (to deselect them) and make the colour of those clicked cells change from green to red, red meaning that the value of those cells will change from TRUE to FALSE. After having clicked on all the cells in column B that I want to flag as FALSE, I would like to sort the spreadsheet on the colour of the cells in column B, so that the rows flagged as TRUE (green) will be displayed in the top part of the spreadsheet, and the rows flagged as FALSE (red) will be displayed under the green ones in the second part of the spreadsheet. (Column A will be a column with digits from 1 to [n], this information is probably irrelevant).
Best,
Bertha
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
Yes. Well sort of.
If you put this code into the code module for the sheet, double clicking on B2 will toggle it between True and False, just like a toggle button.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Address = "$B$2" Then
.Value = Not (CStr(.Value) = "True")
Cancel = True
End If
End With
End Sub
Upvote
0
Great, mikerickson,
And is it possible to make all cells in column B have a toggle button (maybe checkbox is a better name?) that has a green colour by default (green meaning TRUE) and that can be clicked to make it displayed as red and getting the value FALSE? After that is it possible to sort the spreadsheet on TRUE (green)/FALSE (red)?
Bertha
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
Yes, here is the code
Note that both the Value and the color change. Making sorting and downstream calculations easier.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Column = 2 Then
.Value = Not (CStr(.Value) = "True")
If .Value Then
.Interior.Color = RGB(0, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If
End If
End With
End SubNote that both the Value and the color change. Making sorting and downstream calculations easier.
Upvote
0
Upvote
0
Do I understand correctly that I first have to assign green checkboxes to all cells in column B with RGB colour 255 to the checkboxes? I have selected all cells in column B, used rightclick -> Format cells ->Fill. After that I can select a green colour, but I do not see an option to choose for the exact colour RGB 255?
Bertha
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
No, you don't have to select anything or color any cells.
Double click on a cell in column B, it will toggle between True and False.
It will also color the cell green if True and red if False.
Forget about the colors, they are trivial decorations only. The important part is the cell value changing between True and False.
No initializing is necessary, if you want to initialize, it would be done by putting True or False into the appropriate cells.
Double click on a cell in column B, it will toggle between True and False.
It will also color the cell green if True and red if False.
Forget about the colors, they are trivial decorations only. The important part is the cell value changing between True and False.
No initializing is necessary, if you want to initialize, it would be done by putting True or False into the appropriate cells.
Upvote
0
Thank you for your clear explanation. I have put the code in the VBA editor (via insert module), after which I am asked for a name. I have given the name TOGGLE. After that the following is displayed:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Column = 2 Then
.Value = Not (CStr(.Value) = "True")
If .Value Then
.Interior.Color = RGB(0, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If
End If
End With
End Sub
Sub Toggle()
End Sub
After I have pressed F5 to run the macro, it seems nothing is happening, when I doubleclick on a cell in column B nothing happens. After that I tried to copy the code and place it between Sub Toggle () and End Sub:
Sub Toggle()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Column = 2 Then
.Value = Not (CStr(.Value) = "True")
If .Value Then
.Interior.Color = RGB(0, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If
End If
End With
End Sub
End Sub
The result is an error message box stating Compile error: Expected End Sub.
What basic thing am I doing wrong here?
Bertha
Upvote
0
