Dynamically Create and ID OptionButtons, then insert Row; Loop
 

This is a bit complex, so let me take a moment to explain. Im trying to
open a €˜template and connect the code to a button so a user can click it and
put a €˜1 in cell Z16, then put an OptionButton in A16 and another
OptionButton in B16, then assign an identifier to these OptionButtons (a name
or a value to identify these objects). Then a row is inserted at row 17.
Then if the user clicks the button again, a 2 goes into Z17, and an
OptionButton goes into A16 and another OptionButton goes into B17, these are
identified (somehow) and a row is inserted at row 18. If the user clicks the
button again, a 3 goes into Z18, and . . . so on and so forth.

Heres the code I have now.
Private Sub CommandButton3_Click()
Dim optBtn As OptionButtons

Range("Z16").Select
ActiveCell.Value = 1

i = 5
For i = 1 To j
ActiveSheet.OptionButtons.Add ActiveCell.Offset(0, -26).Left,
ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
ActiveSheet.OptionButtons.Add ActiveCell.Offset(0, -25).Left,
ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
ActiveCell.Selection.EntireRow.Insert

Set WS = Application.ActiveWorkbook.Worksheets("Worksheet")
With Targe
optBtn.Caption = ""
optBtn.GroupBox.Name = strGroupName & j
End With

Next i

End Sub

What am I doing wrong?

Thanks!
Ryan

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Dynamically Create and ID OptionButtons, then insert Row; Loop
 

Im making a little progress here, but not as much as Id like. I came up
with this idea for grouping the OptionButtons:
Dim sh As Worksheet
Dim oleObj As OLEObject

For Each sh In Worksheets

i = 1
For Each oleObj In sh.OLEObjects
If TypeOf oleObj.Object Is MSforms.OptionButton Then
oleObj.Object.GroupName = sh.Name & i
End If
i = i + 1
Next

Next
The scenario is that there will be 2 OptionButtons per row, from row 12 to
row whatever. Maybe I will go to row 20, or 30, not sure; thats why I want
to create these OptionButtons at runtime and insert rows (to push the stuff
below down by one row each time). I still havent come up with working-code
ot do this. Also, how would I group the OptionButtons so that each row has
the same €˜GroupName. It doesnt matter what the name is; could be
Worksheet1 for the two OptionButtons on row 12 and Worksheet2 for the two
OptionButtons on row 13. The GroupName is pretty darn important for the
OptionButtons to work. Does it make sense?

Im open to suggestions for this task. Perhaps there is a better way to do
this...


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

This is a bit complex, so let me take a moment to explain. Im trying to
open a €˜template and connect the code to a button so a user can click it and
put a €˜1 in cell Z16, then put an OptionButton in A16 and another
OptionButton in B16, then assign an identifier to these OptionButtons (a name
or a value to identify these objects). Then a row is inserted at row 17.
Then if the user clicks the button again, a 2 goes into Z17, and an
OptionButton goes into A16 and another OptionButton goes into B17, these are
identified (somehow) and a row is inserted at row 18. If the user clicks the
button again, a 3 goes into Z18, and . . . so on and so forth.

Heres the code I have now.
Private Sub CommandButton3_Click()
Dim optBtn As OptionButtons

Range("Z16").Select
ActiveCell.Value = 1

i = 5
For i = 1 To j
ActiveSheet.OptionButtons.Add ActiveCell.Offset(0, -26).Left,
ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
ActiveSheet.OptionButtons.Add ActiveCell.Offset(0, -25).Left,
ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
ActiveCell.Selection.EntireRow.Insert

Set WS = Application.ActiveWorkbook.Worksheets("Worksheet")
With Targe
optBtn.Caption = ""
optBtn.GroupBox.Name = strGroupName & j
End With

Next i

End Sub

What am I doing wrong?

Thanks!
Ryan

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Dynamically Create and ID OptionButtons, then insert Row; Loop
 

For anyone who is interested, I found a solution he
http://www.ozgrid.com/forum/showthread.php?t=51508


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Im making a little progress here, but not as much as Id like. I came up
with this idea for grouping the OptionButtons:
Dim sh As Worksheet
Dim oleObj As OLEObject

For Each sh In Worksheets

i = 1
For Each oleObj In sh.OLEObjects
If TypeOf oleObj.Object Is MSforms.OptionButton Then
oleObj.Object.GroupName = sh.Name & i
End If
i = i + 1
Next

Next
The scenario is that there will be 2 OptionButtons per row, from row 12 to
row whatever. Maybe I will go to row 20, or 30, not sure; thats why I want
to create these OptionButtons at runtime and insert rows (to push the stuff
below down by one row each time). I still havent come up with working-code
ot do this. Also, how would I group the OptionButtons so that each row has
the same €˜GroupName. It doesnt matter what the name is; could be
Worksheet1 for the two OptionButtons on row 12 and Worksheet2 for the two
OptionButtons on row 13. The GroupName is pretty darn important for the
OptionButtons to work. Does it make sense?

Im open to suggestions for this task. Perhaps there is a better way to do
this...


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

This is a bit complex, so let me take a moment to explain. Im trying to
open a €˜template and connect the code to a button so a user can click it and
put a €˜1 in cell Z16, then put an OptionButton in A16 and another
OptionButton in B16, then assign an identifier to these OptionButtons (a name
or a value to identify these objects). Then a row is inserted at row 17.
Then if the user clicks the button again, a 2 goes into Z17, and an
OptionButton goes into A16 and another OptionButton goes into B17, these are
identified (somehow) and a row is inserted at row 18. If the user clicks the
button again, a 3 goes into Z18, and . . . so on and so forth.

Heres the code I have now.
Private Sub CommandButton3_Click()
Dim optBtn As OptionButtons

Range("Z16").Select
ActiveCell.Value = 1

i = 5
For i = 1 To j
ActiveSheet.OptionButtons.Add ActiveCell.Offset(0, -26).Left,
ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
ActiveSheet.OptionButtons.Add ActiveCell.Offset(0, -25).Left,
ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
ActiveCell.Selection.EntireRow.Insert

Set WS = Application.ActiveWorkbook.Worksheets("Worksheet")
With Targe
optBtn.Caption = ""
optBtn.GroupBox.Name = strGroupName & j
End With

Next i

End Sub

What am I doing wrong?

Thanks!
Ryan

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.