Dynamically Create and ID OptionButtons, then insert Row; Loop
in [Programming]
|
From: ryguy7272 on 7 May 2010 10:22 This is a bit complex, so let me take a moment to explain. I'm 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. Here's 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''.
From: ryguy7272 on 7 May 2010 12:52 I'm making a little progress here, but not as much as I'd 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; that's 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 haven't come up with working-code ot do this. Also, how would I group the OptionButtons so that each row has the same 'GroupName'. It doesn't 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? I'm 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. I'm 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. > > Here's 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''.
From: ryguy7272 on 7 May 2010 16:40 For anyone who is interested, I found a solution here: http://www.ozgrid.com/forum/showthread.php?t=51508 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: > I'm making a little progress here, but not as much as I'd 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; that's 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 haven't come up with working-code > ot do this. Also, how would I group the OptionButtons so that each row has > the same 'GroupName'. It doesn't 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? > > I'm 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. I'm 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. > > > > Here's 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''.
|
Pages: 1 Prev: Array to copy columns Next: Speciefie table and not cells to sum |