Toggle button

[Bertha]

Is it possible to turn a cell into a toggle button?

Thank you for helping out,

Bertha

 

[Rick Rothstein]

I have put the code in the VBA editor (via insert module),

That is the wrong location for the code Mike posted... he posted event code (not a macro), so it needs to be put in the worksheet's code module. Right click the name tab for the sheet that is to have this functionality and click "View Code" from the popup menu that appears... copy paste the code Mike gave you (with the modification I posted below) in the code window that just opened up... that is it, do nothing else code-wise (well, except to delete the code you said you added in the general module you inserted), instead, go to the worksheet and double click a cell in Column B... Mike's code will execute automatically as a result of your double click action.

 

[Rick Rothstein]

Yes, here is the 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
            [B][COLOR="#FF0000"]Cancel = True[/COLOR][/B]
        End If
    End With
End Sub

Note that both the Value and the color change. Making sorting and downstream calculations easier.

Mike... I think you might want to assign True to the Cancel argument to stop the double click in Column B from causing the cell to go into Edit Mode.

 

[mikerickson]

Thanks for the catch Rick.

 

[Bertha]

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

Maybe I should first have made a (ActiveX or Form??)-checkbox for all cells in column B?

 

[Bertha]

Ignore my question about checkbox, I had not yet read the additional replies...

 

[My Aswer Is This]

I did not know about "Cancel= True". I have some macro's assigned to cells when I double click. Sometimes not always the double click causes the cell to go into edit mode. I now know how to fix that.

Mike... I think you might want to assign True to the Cancel argument to stop the double click in Column B from causing the cell to go into Edit Mode.

 

[MarcelBeug]

Hi,

Edit: I missed the posts on page 2 when adding this post. My remark/question below regarding language dependency seems still valid.

First my apologies for taking your question too literaly, partly because I'm not well experienced with VBA.

Having said that, I tried to get the code running and this is what I did:
- in the VBA editor press F7 (show program code) instead of inserting a module,
- in the right pane choose "worksheet" in the upper left dropdown box and "BeforeDoubleClick" in the right dropdown box,
- than insert the code from Mike (excl. first and last line, because these are already generated).

An important note and question from my side: the code is language dependent.
As I'm running Dutch version of Excel, I needed to adjust "TRUE" to "Waar".
My question is if this code can be adjusted to make it language independent?

When I tried I noticed it was working but you stay in edit mode when double clicking. Edit: Rick's cancel avoids this.
Coincidentally I also noticed that if you choose "SelectionChange" instead of "BeforeDoubleClick" it works with 1 click.

Hope this makes sense; any comment from Mike / Rick or another VBA expert would be appreciated.

 

[mikerickson]

This would be language independent

.Value = Not(CStr(.Value) = CStr(True))

 

[MarcelBeug]

Thanks Mike, appreciated. Possibly also beneficial for Bertha.

 

[Mad Moose]

Can you change the colour of an actual control tool toggle button
ie when clicked it turns green