Consulting

Results 1 to 12 of 12

Thread: Creating Checkboxes that when click uncheck the other

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    51
    Location

    Creating Checkboxes that when click uncheck the other

    I'm trying to create some checkboxes in a certain range. The range I have defined has sets of checkboxes next to each other; for example I5 & J5 is a basic yes or no. Same with I7 & J7 etc.
    I'd like to make the checkboxes in each set uncheck each other. If yes is checked it will make sure no is unchecked, etc.
    I'm having some trouble trying to assign the function with the .OnAction or .OnClick etc. Not sure what I am doing wrong here.

    Public Sub Add_Checkboxes()
    
    
        'Delete all preexisting checkboxes
        For Each c In Worksheets(1).CheckBoxes
            c.Delete
        Next
        
        'Generate the checkboxes in a predefined range
        Dim rngChkBoxes As Range: Set rngChkBoxes = Range("I5, J5, I7, J7, I9, J9, I18, J18")
        For Each Rng In rngChkBoxes
            Worksheets(1).CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height).Select
            
            With Selection
                .Caption = ""
                '.Name = "chk_"
                '.OnClick = IsChecked()
            End With
            
        Next Rng
    End Sub
    
    
    Public Sub IsChecked()
        MsgBox ("testing")
    End Sub


  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello mongoose,

    When you need exclusive selection, one out of many, the control of choice is a Radio Button. Trying to alter the inherent functionality of a control when you don't need to is not a good use of your time, unless you just want to know how it is done.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by Leith Ross View Post
    When you need exclusive selection, one out of many, the control of choice is a Radio Button.
    Holy words.

    Quote Originally Posted by Leith Ross View Post
    unless you just want to know how it is done.
    And it gets done in this way:
    Sub Add_Checkboxes()
        Dim c As Excel.CheckBox
        Dim rngChkBoxes As Range
        Dim Rng As Range
        
        'Delete all preexisting checkboxes
        For Each c In Worksheets(1).CheckBoxes
            c.Delete
        Next
        
        'Generate the checkboxes in a predefined range
        Set rngChkBoxes = Range("I5, J5, I7, J7, I9, J9, I18, J18")
        
        For Each Rng In rngChkBoxes
            Set c = Worksheets(1).CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height)
            
            With c
                .Caption = ""
                .Name = "chk_" & IIf(Rng.Column = 9, "Yes", "No") & "_" & Rng.Row
                .OnAction = "IsChecked"
            End With
            
        Next Rng
    End Sub
    
    
    
    Private Sub IsChecked()
        Dim c As Excel.CheckBox
        Dim varName As Variant
        
        With ActiveSheet
        
            Set c = .CheckBoxes(Application.Caller)
            
            varName = Split(c.Name, "_")
            
            If varName(1) = "Yes" Then
                ActiveSheet.CheckBoxes("chk_No_" & varName(2)).Value = -4146
            Else
                ActiveSheet.CheckBoxes("chk_Yes_" & varName(2)).Value = -4146
            End If
        
        End With
    End Sub
    Artik

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Leith Ross View Post
    When you need exclusive selection, one out of many, the control of choice is a Radio Button. Trying to alter the inherent functionality of a control when you don't need to is not a good use of your time, unless you just want to know how it is done.
    Yes. OptionButtons (RadioButtons) (I'm talking the Form type, not the ActiveX type) will work as a group if enclosed in a GroupBox. Enclosing the OptionButtons in a GroupBox can be fraught as it seems that the GroupBox should completely enclose the OptionButtons - but I've found that still isn't always the case! Anyway here's a bit of code to add some OptionButtons, and a few (invisible) enclosing GroupBoxes on the active sheet:
    Sub blah()
    With ActiveSheet
      Set rngChkBoxes = .Range("I5,I7,I9,I18")    'only the first cell of each pair
      
      'Delete ALL existing Form groupboxes and Form optionbuttons on the sheet:
      For Each c In .OptionButtons
        c.Delete
      Next
      For Each c In .GroupBoxes
        c.Delete
      Next
        
      For Each cll In rngChkBoxes.Cells
        Set cll2 = cll.Offset(, 1)    'the second optionbutton location
        With .OptionButtons.Add(cll.Left, cll.Top, cll.Width, cll.Height)
          .Caption = ""
          .Height = cll.Height    'needed because it doesn't take on the correct height
        End With
        With .OptionButtons.Add(cll2.Left, cll2.Top, cll2.Width, cll2.Height)
          .Caption = ""
          .Height = cll2.Height    'needed because it doesn't take on the correct height
          '.LinkedCell = cll.Offset(, -1).Address(external:=True) 'this will also be the linked cell for all the option buttons in the same group.
        End With
        Set myRng = .Range(cll, cll2)
        With .GroupBoxes.Add(myRng.Left, myRng.Top, myRng.Width, myRng.Height)
          '.Caption = ""
          .Height = myRng.Height    'needed because it doesn't take on the correct height
          .Visible = False
        End With
      Next cll
    End With
    End Sub
    Last edited by p45cal; 08-16-2019 at 04:30 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Simply so:

    Sub M_snb()
      With Sheet1.OLEObjects
       For j = 1 To 5
         With .Add("Forms.OptionButton.1", , , , , , , 200, 100 + j * 36, 100, 24)
            .Name = "O_" & Format(j, "00")
            .Object.Caption = "Choice " & j
            .Object.groupname = "snb"
         End With
       Next
       End With
    End Sub
    But..., you'd better create these objects in Design Mode and make them invisible.
    In real time you only have to make them visible by VBA code
    Last edited by snb; 08-16-2019 at 05:23 AM.

  6. #6
    VBAX Regular
    Joined
    Aug 2019
    Posts
    9
    Location
    If I can intrude on the thread to ask some questions about the posts here, here they are; but I'll edit them away and put in another thread if asked:

    I've always wondered how Excel knows whose option buttons are whose, but out of habit I've always just drawn a frame around them (or dragged them into a frame, actually). Is a GroupBox something different? [Disclaimer: the scope of my knowledge is what appears in the form Toolbox.] Anyway, I just tested a blank form with 1 option button, copied it twice, hit F5, and the three worked in concert (correctly) without anything to "group" them except the form boundary itself.

    One more thing, is invisibility advantageous for the frame or Groupbox? It would seem easier to keep things straight if visible. I guess since it's visible in design, and can't be clicked on or tabbed to at runtime, it's not problematic; just wondering why you would though. (And I've actually deliberately made a invisible/transparent control before myself - as a trick to do a flyover message when moused over.)

    And by the way, this whole discussion is about UserForm controls, rather than controls directly on a worksheet, right? If not, maybe ignore all my scribbling!

    EDIT: I just drew a frame and dragged them in, but now I can't cycle around with the tab key. I can tab from button 1 to button 2 to button 3, but it stops there. Was I just wrong to use a frame at all?!
    Last edited by LarryG; 08-17-2019 at 12:53 PM. Reason: Added last paragraph

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by LarryG View Post
    this whole discussion is about UserForm controls, rather than controls directly on a worksheet, right?
    Worksheet I'm afraid.

    snb's solution adapted to fit more closely to the OP's requirements (these are ActiveX option buttons):
    Sub blah2()
    margin = 1 'small adjustment to size and placement of optionbuttons to keep sheet grid visible.
    With ActiveSheet
      Set rngChkBoxes = .Range("I5,I7,I9,I18")    'only the first cell of each pair
      'Delete ALL existing activex optionbuttons on the sheet:
      For Each obj In .OLEObjects
        If TypeName(obj.Object) = "OptionButton" Then obj.Delete
      Next obj
      'Add new ones:
      For Each cll In rngChkBoxes.Cells
        Set cll2 = cll.Offset(, 1)    'the second optionbutton location
        With .OLEObjects.Add("Forms.OptionButton.1", , , , , , , cll.Left + margin, cll.Top + margin, cll.Width - 2 * margin, cll.Height - 2 * margin).Object
          .Caption = "Yes"
          .GroupName = "row" & Format(cll.Row, "000")
          .Font.Size = 9
        End With
        With .OLEObjects.Add("Forms.OptionButton.1", , , , , , , cll2.Left + margin, cll2.Top + margin, cll2.Width - 2 * margin, cll2.Height - 2 * margin).Object
          .Caption = "No"
          .GroupName = "row" & Format(cll.Row, "000")
          .Font.Size = 9
        End With
      Next cll
    End With
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Much has already been said in this thread. I will only add one. Avoid ActiveX controls embedded in a worksheet. They will often be unstable.

    Artik

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    To illustrate what I mean:


    @Artik

    It's a matter of correctly defining the active-X's properties.
    Attached Files Attached Files
    Last edited by snb; 08-18-2019 at 07:28 AM.

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    snb, can you tell me how to set the properties of the ActiveX control so that, for example, the ComboBox does not change its position on the sheet by itself or that it does not change its size when clicking on the control. Or that strange artifacts of these controls are created. These behaviors are unpredictable. Sometimes these go crazy, other times everything is OK. Because I meant such unstable behavior.

    Artik

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I'd suggest:

    Sub M_snb()
       For j = 0 To 1
         With Sheet1.OLEObjects.Add("Forms.Combobox.1", , , , , , , Columns(10).Left, Rows(4 + 2 * j).Top, 60, 18)
            .Name = "C_" & Format(j, "00")
            .Object.List = Split("A B C D E F G")
            .Placement = 3
         End With
        Next
    End Sub

  12. #12
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    OK, let's see what life will show in the future.
    The default property when inserting an object is xlMove.


    Thx


    Artik

Tags for this Thread

Posting Permissions

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