Create a ToggleButton

SchneeBaer · Mar 10, 2019

I believe I would like to create a ToggleButton.

With my limited development background, I believe I want to layer an invisible togglebutton over a cell, so that the user, when they click on the cell will change the visual properties between three states:

TRUE - green background with the bold text "Stocked"
FALSE - red background with the bold text "Never"
#N/A - a blank cell - white with no wording

Ideally, I would like to be able to copy the togglebutton along with the cell properties when adding new rows & columns

Is this feasible?

bobsan42 · Mar 11, 2019

Re: How to Create a ToggleButton

Doable - sort of. But no layers, just objects.
feasible - Not sure. What exactly are you trying to achieve?

You Want to create an invisible button and then change it's visual properties when you click in a cell ???!!!
The button can move with the cell when rows/columns are inserted or deleted.
The button can be tripleState so you can have three values. Finally you can write some code, triggered by the change event, that will change text, color etc.
Additional copies will be a bit more tricky, because you have to add code to them or you have to use classes.

Also - you cannot click an invisible button, but you can make it transparent. Still it is not transparent while it is active (when you click on it).

Another way to do it is to use the LinkedCell property of the button - then you may do it without coding for Change event, but you will have to use additional cell to display what you need.

Handling multiple copies of the button will quickly become a tedious task - they will not be deleted when you delete a cell with a button and will pile one over the other.

My Aswer Is This · Mar 11, 2019

Re: How to Create a ToggleButton

I'm not sure why you think you need to do things this way.

Why not just enter "Stocked" Or "Never" into a cell in column(1) and have the cells interior color change

No need for Toggle buttons or problems with adding more Toggle buttons.

Using this script any time you enter "Stocked" Or "Never" into any cell in column(1) the cells interior color will change.
If your cell is changed to nothing the cells color is white.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/11/2019  1:39:27 PM  EDT
If Target.Column = 1 Then
If Target.Cells.CountLarge > 1 Then Exit Sub
With Target
    Select Case Target.Value
        Case "Stocked"
            .Interior.Color = vbGreen
            .Font.Bold = True
        Case "Never"
            .Interior.Color = vbRed
            .Font.Bold = True
        Case ""
            .Interior.Color = xlNone
            .Font.Bold = False
    End Select
End With
End If
End Sub

JoeStellar · Mar 11, 2019

Re: How to Create a ToggleButton

You could also use a dropdown combo box, with three options, Stocked, Never, and #N/A. I also wouldn't bother with any VBA code, it's always nicer to keep files without a macro extension. Use conditional formatting instead. Combo Boxes are pretty easy and so is conditional formatting. I like the toggle button idea, and it can be done this way, but copying and pasting the toggle button and having it work the way you want is probably more complicated then you are ready for.

My Aswer Is This · Mar 11, 2019

Re: How to Create a ToggleButton

You said:
I also wouldn't bother with any VBA code

Combo Boxes are pretty easy

How do you use a Combobox or Toggle button without using VBA Code?

_{^{<strike>

</strike>}}

JoeStellar said:
You could also use a dropdown combo box, with three options, Stocked, Never, and #N/A. I also wouldn't bother with any VBA code, it's always nicer to keep files without a macro extension. Use conditional formatting instead. Combo Boxes are pretty easy and so is conditional formatting. I like the toggle button idea, and it can be done this way, but copying and pasting the toggle button and having it work the way you want is probably more complicated then you are ready for.

bobsan42 · Mar 11, 2019

Re: How to Create a ToggleButton

My Aswer Is This said:
How do you use a Combobox or Toggle button without using VBA Code?

I believe what Joe meant was setting up a data validation drop down box.
This would not require VBA, Conditional formatting would suffice.

JoeStellar · Mar 11, 2019

Re: How to Create a ToggleButton

Make a list of you options, put one choice in each cell in a column. In Stock, Never, #N/A. Then insert a combo box. Right click on it and select format control (then control tab). In the item list select the column of your list options, In Stock, Never, #N/A, and then in the Cell Link select the cell that you want the selection to go in. Then use conditional formatting to change the formatting of the destination cell. You can google excel combobox dropdown and excel conditional formatting.

JoeStellar · Mar 11, 2019

Re: How to Create a ToggleButton

https://www.dropbox.com/s/5y69yni6k120mqo/in stock.xlsx?dl=0

I don't know if this link will work. I created an example and put it in my dropbox.

https://www.dropbox.com/s/5y69yni6k120mqo/in stock.xlsx?dl=0

bobsan42 · Mar 11, 2019

Re: How to Create a ToggleButton

So JoeStellar is really talking about a ComboBox, which IMHO is pretty much the same as TriState togglebutton - the only difference is you get to choose the values.

Do you really need a button to insert 3 values in a column of data - once you have a value present Excel will give you suggestions after the first letter you type. So with 2 keystrokes you can have a cell value entered (Using just the keyboard is usually much faster for me.)

SchneeBaer · Mar 11, 2019

Re: How to Create a ToggleButton

Wow! I posted this on a TechNet forum and didn't get a response for 2 weeks! You guys are amazing!

My apologies for not presenting what I was trying to achieve.

I'm wanting to create a spreadsheet that I can post on our Sharepoint site with edit rights. Column headers would be people (5-15 per week) and rows would be items (about 25). I was wanting to create a way for these people to manage the status of the various items that they have. A toggle would be super easy to change the status with 1-2 clicks, and with the red/green/white - we really wouldn't need the wording. This would give everyone the ability to see at a glance where an item can be found. The people and items can change slightly from week to week.

I'm needing something really super easy, quick and simple to get user adoption.

Create a ToggleButton

SchneeBaer

New Member

Excel Facts

bobsan42

Well-known Member

My Aswer Is This

Well-known Member

JoeStellar

New Member

My Aswer Is This

Well-known Member

bobsan42

Well-known Member

JoeStellar

New Member

JoeStellar

New Member

bobsan42

Well-known Member

SchneeBaer

New Member

Similar threads

Forum statistics

Share this page

Create a ToggleButton

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock