acdecal
asked on
VBA for Excel to add radio buttons with unique group name
I am trying to develop code to add radio buttons to the active row, link to a cell in the active row and create a unique group name for this row. I have code as follows but I understand that the form type of control does not support group name.
Private Sub ToggleButton1_Click()
Dim Nrcb As Variant
Dim i As Integer
Dim c As Range
Dim Name As String
Nrcb = InputBox("how many checkboxes to place?")
Name = InputBox("Group name?")
ActiveCell.Select
For i = 1 To Nrcb
Set c = ActiveCell
ActiveSheet.OptionButtons. Add(c.Left , c.Top, 72, 12).Select
With Selection
.Characters.Text = ""
.Value = xlOff
.LinkedCell = "L" & c.Row
.Display3DShading = False
.GroupName = "Name"
ActiveCell.Offset(0, 2).Activate
End With
Next i
End Sub
How can I work around this?
Private Sub ToggleButton1_Click()
Dim Nrcb As Variant
Dim i As Integer
Dim c As Range
Dim Name As String
Nrcb = InputBox("how many checkboxes to place?")
Name = InputBox("Group name?")
ActiveCell.Select
For i = 1 To Nrcb
Set c = ActiveCell
ActiveSheet.OptionButtons.
With Selection
.Characters.Text = ""
.Value = xlOff
.LinkedCell = "L" & c.Row
.Display3DShading = False
.GroupName = "Name"
ActiveCell.Offset(0, 2).Activate
End With
Next i
End Sub
How can I work around this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, but how do I Link a cell to the group?
If you had 3 option buttons, would you want it to say 1,2,3? A linked cell only says TRUE/FALSE for one option button. You can add a .LinkedCell property in the with block, but unless you're going to have Nrcb linked cells for Nrcb option buttons, a linked cell wouldn't do you much good (especially if they were all linked to the same cell!)
What you could do is have code for each option button that looked something like:
Private Sub OptionButton1_Click()
Range("L" & OptionButton1.TopLeftCell. Row).Value = 1
End Sub
Private Sub OptionButton2_Click()
Range("L" & OptionButton2.TopLeftCell. Row).Value = 2
End Sub
Private Sub OptionButton3_Click()
Range("L" & OptionButton3.TopLeftCell. Row).Value = 3
End Sub
That puts the number into the L cell of the same row. If this sounds good for you, I can see if I can come up with something tomorrow that doesnt require a separate _Click event for each option button added. I know it is doable, but I have no more free time today, and likely won't tonight. But if you'd like I can do something tomorrow for you
Matt
What you could do is have code for each option button that looked something like:
Private Sub OptionButton1_Click()
Range("L" & OptionButton1.TopLeftCell.
End Sub
Private Sub OptionButton2_Click()
Range("L" & OptionButton2.TopLeftCell.
End Sub
Private Sub OptionButton3_Click()
Range("L" & OptionButton3.TopLeftCell.
End Sub
That puts the number into the L cell of the same row. If this sounds good for you, I can see if I can come up with something tomorrow that doesnt require a separate _Click event for each option button added. I know it is doable, but I have no more free time today, and likely won't tonight. But if you'd like I can do something tomorrow for you
Matt
ASKER
Thanks Matt,
Let me explain in more detail what I am trying to accomplish. The option buttons are part of a code that copies other cells which link to new worksheets as they are created. The main sheet becomes a summary for the workbook and the user can then pick from seven linked vales for each worksheet (row). The chosen option button will link the corresponding value to a summary column for each data row. So I may end up with 20 groups of 7 option buttons. This workbook will be created for many different jobs.
Hope this makes sense. Thanks,
Andy
Let me explain in more detail what I am trying to accomplish. The option buttons are part of a code that copies other cells which link to new worksheets as they are created. The main sheet becomes a summary for the workbook and the user can then pick from seven linked vales for each worksheet (row). The chosen option button will link the corresponding value to a summary column for each data row. So I may end up with 20 groups of 7 option buttons. This workbook will be created for many different jobs.
Hope this makes sense. Thanks,
Andy
ASKER
More on that: If a cell was linked to each group then a conditional function could chose the corresponding value and link it to a summation column.
ASKER
Hey Matt,
I think I figured it out:
Private Sub ToggleButton1_Click()
Dim Nrcb As Variant
Dim i As Integer
Dim c As Range
Dim Name As String
Dim cb As OLEObject
Nrcb = InputBox("how many checkboxes to place?")
Name = InputBox("Group name?")
ActiveCell.Select
For i = 1 To Nrcb
Set c = ActiveCell
Set cb = ActiveSheet.OLEObjects.Add ("Forms.Op tionButton .1", Left:=c.Left, _
Top:=c.Top, Width:=72, Height:=12)
cb.LinkedCell = c.Offset(1, 0).Address
cb.Object.Caption = ""
cb.Object.Value = False
cb.Object.GroupName = Name
ActiveCell.Offset(0, 2).Activate
Next i
End Sub
Each option button is linked to the cell below and
I then can use the =SUMIF(TRUE) function to capture the value selected for each set.
Thanks for your help. I'll give you the points.
I think I figured it out:
Private Sub ToggleButton1_Click()
Dim Nrcb As Variant
Dim i As Integer
Dim c As Range
Dim Name As String
Dim cb As OLEObject
Nrcb = InputBox("how many checkboxes to place?")
Name = InputBox("Group name?")
ActiveCell.Select
For i = 1 To Nrcb
Set c = ActiveCell
Set cb = ActiveSheet.OLEObjects.Add
Top:=c.Top, Width:=72, Height:=12)
cb.LinkedCell = c.Offset(1, 0).Address
cb.Object.Caption = ""
cb.Object.Value = False
cb.Object.GroupName = Name
ActiveCell.Offset(0, 2).Activate
Next i
End Sub
Each option button is linked to the cell below and
I then can use the =SUMIF(TRUE) function to capture the value selected for each set.
Thanks for your help. I'll give you the points.
I've actually been playing around with this the past couple hours, and I have to say that VBA doesnt like to play by the rules sometimes. I have a class module to control the events for the option buttons, but after the subroutine runs that adds them the collection storing them seems to forget itself. But running two subs separately (to add buttons, then to get them working) works fine! At least we can get the option buttons working on workbook_open so your end users won't know the difference.
Anyways, I know you are happy with your method, but out of my own personal stubbornness I want to get to the bottom of this. If you'd like, take a look at where I'm at right now: https://filedb.experts-exchange.com/incoming/ee-stuff/3113-hmmm.zip
Use the "CreateOptionButtons" macro to create buttons the same way your sub above works.
Use the "GetOptionButtonsWorking" macro to get them working
Use the "DeleteAllOptionButtons" to delete them from the active sheet
On workbook open it will get any existing optionbuttons working, but you'll still have to run "getoptionbuttonsworking" after adding your buttons. The # of the option button for each row will be put into column L (easily changeable). I know you're happy with what you have, as I said I guess I'm just stubborn sometimes :)
Matt
Anyways, I know you are happy with your method, but out of my own personal stubbornness I want to get to the bottom of this. If you'd like, take a look at where I'm at right now: https://filedb.experts-exchange.com/incoming/ee-stuff/3113-hmmm.zip
Use the "CreateOptionButtons" macro to create buttons the same way your sub above works.
Use the "GetOptionButtonsWorking" macro to get them working
Use the "DeleteAllOptionButtons" to delete them from the active sheet
On workbook open it will get any existing optionbuttons working, but you'll still have to run "getoptionbuttonsworking" after adding your buttons. The # of the option button for each row will be put into column L (easily changeable). I know you're happy with what you have, as I said I guess I'm just stubborn sometimes :)
Matt
ASKER