Check box or radio circle on and off in a cell by clicking

Topic

New Reply

I have a simple grocery list that I would like to be able to click on a cell and have it go to a filled radio circle from an empty radio circle and back if I make a mistake so I can quickly check off items I need on a grocery list. It can also go from a square to a square with a check mark.
Can’t seem to be able to do this without cutting and pasting all the time.
I like to keep multiple columns to see what I have actually bought in the rece

Reply | Quote

Replies

Jr,

Here is some code that will allow you to place a check in a cell by clicking on it then remove it by clicking on the cell again. Place the following code in the sheet module for the sheet you are using. Change the range in the code (“C3:E7”) to the range of cells you need.

HTH,
Maud

33596-Check

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
 If Target.Cells.Count > 1 Then Exit Sub             ‘IF SELECT MORE THAN ONE CELLTHAN EXIT THE ROUTINE
    If Not Intersect(Target, Range(“C3:E7”)) Is Nothing Then   ‘IF THE SELECTED CELL AND RANGE OVERLAP THEN….
       Target.Font.Name = “Marlett”                     ‘CHANGE THE FONT OF THE SELECTED CELL TO MARLETT
        If Target = vbNullString Then                       ‘IF THE SELECTED CELL IS EMPTY THEN…
       Target = “a”                                        ‘PLACE A CHECKMARK
       Range(“A1”).Select                                 ‘CHANGE TO ANOTHER CELL IF A1 IS PART OF THE RANGE
  Else                                                    ‘IF IT IS NOT EMPTY THEN….
      Target = vbNullString                               ‘MAKE IT EMPTY
      Range(“A1”).Select                                 ‘CHANGE TO ANOTHER CELL IF A1 IS PART OF THE RANGE
   End If
End If

End Sub

Reply | Quote

Maud, many many thanks, just wonderful.
1. Any way I can add formatting to increase the font size or change its color to say red bold and slightly larger check mark.
2. Also can the pointer not revert to a1 but to the next cell below as if you had just hit the enter key or something similar.
Right now it moves the whole workbook up and forces you to scroll down for a long list to get back to the area of the workbook you were last in??
Again my thanks
JR

Reply | Quote

jr,

Easily done: Highlight now only the range with the checkboxes then click bold, red font, 18 font size. See pic below. Adjust the settings to anything you like.

As for the cell that is selected after the check is placed, it can not jumpt to a cell in the range or the code is fired again. For example, if the code is set to select the cell one down, let’s see what happens: You select D3 and place a check then the code selects D4 but the code is fired again. It places a check in D4 then selects D5. The code is run again until i moves below outside of the range.

The best I can do here is select the same row but in column 1.

33610-Check2

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub             ‘IF SELECT MORE THAN ONE CELLTHAN EXIT THE ROUTINE
    If Not Intersect(Target, Range(“C3:E7”)) Is Nothing Then   ‘IF THE SELECTED CELL AND RANGE OVERLAP THEN….
        Target.Font.Name = “Marlett”                     ‘CHANGE THE FONT OF THE SELECTED CELL TO MARLETT
        If Target = vbNullString Then                       ‘IF THE SELECTED CELL IS EMPTY THEN…
            Target = “a”                           ‘PLACE A CHECKMARK
            Cells(Target.Row, 1).Select
        Else                                                    ‘IF IT IS NOT EMPTY THEN….
            Target = vbNullString                               ‘MAKE IT EMPTY
            Cells(Target.Row, 1).Select
    End If
End If

End Sub

Reply | Quote

JR,

This modification will make the code work like pressing the Enter key, e.g. move to the next row in same column.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
   On Error Resume Next
   If Target.Cells.Count > 1 Then Exit Sub             'IF SELECT MORE THAN ONE CELLTHAN EXIT THE ROUTINE
   
     If Not Intersect(Target, Range("C3:E7")) Is Nothing Then   'IF THE SELECTED CELL AND RANGE OVERLAP THEN....
       Target.Font.Name = "Marlett"                     'CHANGE THE FONT OF THE SELECTED CELL TO MARLETT
       If Target = vbNullString Then                       'IF THE SELECTED CELL IS EMPTY THEN...
         Target = "a"                           'PLACE A CHECKMARK
       Else                                                    'IF IT IS NOT EMPTY THEN....
        Target = vbNullString                               'MAKE IT EMPTY
       End If
    
    Application.EnableEvents = False
    Target.Offset(1, 0).Select
    Application.EnableEvents = True
    
End If

End Sub

However, please note that it does strange things if you use the keyboard inside the trapped range. Try the arrow keys, Enter key, and really weird the Space bar. You would have to go into keyboard capturing to resolve these behaviors. But the code works just fine if you use the mouse. Maud’s code does not display all of these behaviors because of the location of the cursor out of the trapped range. But try placing the cursor just outside the trapped range and then press the arrow key that will move the cursor into the trapped range. The point is to make this code fool proof will be a lot of work. If it is just for you no problem as you know it’s limitations but if you are distributing the code there is a lot of work to do. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Dear Maud and HTH, Many thanks, both work great, and will just try each out to see works better.
Thank you for the abundance of choices, many thanks
JR

Reply | Quote

So the “empty” cells of the spreadsheet do not show so “empty”
can the empty cells default from blank to an empty “circle” radio button may be easier to read a long grocery list Please see my original a.xls for the empty circles/radio buttons.
Another words, can the on off formatting of the cell show a different empty kind of symbol versus just being empty
THanks

Reply | Quote

JR,

This will do the empty box / checked box version:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
   On Error Resume Next
   If Target.Cells.Count > 1 Then Exit Sub             'IF SELECT MORE THAN ONE CELLTHAN EXIT THE ROUTINE
   
     If Not Intersect(Target, Range("C3:E7")) Is Nothing Then   'IF THE SELECTED CELL AND RANGE OVERLAP THEN....
       Target.Font.Name = "WingDings"                     'CHANGE THE FONT OF THE SELECTED CELL TO MARLETT
       If Target = vbNullString Or Target = Chr(168) Then                       'IF THE SELECTED CELL IS EMPTY THEN...
         Target = Chr(254)                           'PLACE A CHECKMARK
       Else                                                    'IF IT IS NOT EMPTY THEN....
        Target = Chr(168)                               'MAKE IT EMPTY
       End If
    
    Application.EnableEvents = False
    Target.Offset(1, 0).Select
    Application.EnableEvents = True
    
End If

End Sub

33615-CheckBoxes
Note: The code will change either a blank cell or one with an unchecked box into a checked box.

If you want to start with all unchecked boxes place this code in a standard module

Code:

Option Explicit

Sub BlankBoxes()

   Dim rngCell As Range

   Application.EnableEvents = False
   For Each rngCell In Selection
      rngCell = Chr(168)
   Next rngCell
   Application.EnableEvents = True
   
End Sub

Then just select the range by dragging press Alt+F8 and double-click BlankBoxes. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,
Thanks for the modification but that is why I wrote: Cells(Target.Row, 1).Select so that it would not be possible to display the behaviors you describe short of giong into design mode, clicking the in the range, then unclicking design mode.

Jr,
The limitation is the characters contained in the font. Your request can be done by changing the font to Wingdings and using different characters (done by altering code). See Wingdings.xls. If you do not have Windings installed use Marlett.xls

Wingdings
33617-Check3

Marlett
33618-Check4

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub             ‘IF SELECT MORE THAN ONE CELLTHAN EXIT THE ROUTINE
    If Not Intersect(Target, Range(“C3:E7”)) Is Nothing Then   ‘IF THE SELECTED CELL AND RANGE OVERLAP THEN….
        Target.Font.Name = “Wingdings”                     ‘CHANGE THE FONT OF THE SELECTED CELL TO MARLETT
        If Target = “¡” Then                       ‘IF THE SELECTED CELL IS EMPTY THEN…
            Target = “ü”                           ‘PLACE A CHECKMARK
            Cells(Target.Row, 1).Select
        Else                                                    ‘IF IT IS NOT EMPTY THEN….
            Target = “¡”                             ‘MAKE IT EMPTY
            Cells(Target.Row, 1).Select
    End If
End If

End Sub

Reply | Quote

RG,
Thanks for the modification but that is why I wrote: Cells(Target.Row, 1).Select so that it would not be possible to display the behaviors you describe short of giong into design mode, clicking the in the range, then unclicking design mode.

Maud,

I know that’s what I said:

Maud’s code does not display all of these behaviors because of the location of the cursor out of the trapped range.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Jr,

By the way, HTH=”Hope that helps”

Reply | Quote

Jr,

Here is a revised version to allow you to automatically add preformatted rows and columns. The trigger range will grow as you add. The row becomes activated when you add the item name in the first cell of that row. If you want to add a category (ex Vegetables) place an asterisk in front of it and it will be ignored from the range.

33628-Check6

Reply | Quote

Dear Maudibe
Many many thanks, Tried everything really great, Columns add works great
[Rows add fine but have to make certain there is an item in the a column for the new row or won’t work right
which is just fine
(sorry for the late response I was away and only had my Iphone and surprisingly could not view xls files on it)
Again my thanks
JR

Reply | Quote

The row becomes activated when you add the item name in the first cell of that row.

Your welcome. I purposely designed it that way so you could not mistakingly click a row that is not labeled.
Maud

Reply | Quote

Hi Maudibe

If someone posts a sample file of type .xls I always assume that they want a solution that works in Excel2003.
(Otherwise they would post a file of type .xlsx???)

Your file is incompatible with Excel2003, because it uses vba functions like
.ThemeColor
.TintAndShade
..so it will give a debug error you try to add rows or columns using your ActiveX control buttons.

Where possible I prefer to use ‘Form buttons’on a worksheet rather than ActiveX ‘Control buttons’.

Using the Selection change event can be problematic for the intended purpose requested.
For example, in your file, if you put the cellpointer in cell [K4] and then use the [Left Arrow] key,
the entry in cell [J4] changes as the cellpointer jumps to column [A].
This could easily go unnoticed (especially if there were lots more columns).

I changed the method to use the mouse right-click event instead.
This leaves the cellpointer on the selected cell and just toggles the value from tick to circle etc. each time you right-click the mouse.
(You could also use the double-click event instead)

So I have adapted your file to my attached version.
Rows can be added by clicking the [Add Row] button, and this does not require pre-existing entries in [A]

Check out my vba code to see different way of doing things.

zeddy

Reply | Quote

Zeddy,

Very Nice! :thewave:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

That is a whopping compliment.
I really appreciate that.
Thanks for taking the time to look at it!

zeddy

Reply | Quote