Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts

    Check boxes whithout checkboxes

    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

    Check.jpg

    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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-06 at 16:40. Reason: Improved coded so changed cell is placed in ready state

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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
    Last edited by jrklein; 2013-04-07 at 02:01.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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.

    Check2.jpg

    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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-07 at 02:43.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    CheckBoxes.JPG
    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.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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
    Check3.jpg

    Marlett
    Check4.jpg

    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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-07 at 11:22.

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Jr,

    By the way, HTH="Hope that helps"

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by Maudibe View Post
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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.

    Check6.jpg
    Attached Files Attached Files
    Last edited by Maudibe; 2013-04-08 at 21:26.

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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
    Last edited by jrklein; 2013-04-08 at 16:56.

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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
    Last edited by Maudibe; 2013-04-11 at 22:28.

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    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
    Attached Files Attached Files

  16. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2013-04-09)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •