PDA

View Full Version : Creating Checkboxes that when click uncheck the other



mongoose
08-15-2019, 10:43 AM
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

Leith Ross
08-15-2019, 11:37 AM
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.

Artik
08-15-2019, 01:16 PM
When you need exclusive selection, one out of many, the control of choice is a Radio Button.Holy words. :)


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 SubArtik

p45cal
08-16-2019, 04:19 AM
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

snb
08-16-2019, 04:53 AM
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

LarryG
08-17-2019, 12:48 PM
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?!

p45cal
08-17-2019, 05:08 PM
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

Artik
08-17-2019, 06:18 PM
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

snb
08-18-2019, 06:35 AM
To illustrate what I mean:


@Artik

It's a matter of correctly defining the active-X's properties.

Artik
08-18-2019, 07:06 AM
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

snb
08-18-2019, 08:03 AM
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

Artik
08-18-2019, 08:31 AM
OK, let's see what life will show in the future.
The default property when inserting an object is xlMove.


Thx


Artik