Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to copy and paste row of controls on userform
I have a userform with three rows of comboboxes and textboxes (13 controls
total) into which the user enters search criteria for an advancedfilter. This works great and I don't want to change the overall idea because there is too much code already written and the intended user likes it. What I would like to do is, instead of having three pre-defined rows of controls, just have one with a button so the user could add a new row for additional criteria if needed. That way, the user would not be limited to three criteria rows. What would be the best way to go about this using VBA? Thanks, --elizabeth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to copy and paste row of controls on userform
I'm not sure if this is a fit for you, but I'd still limit the number of rows
(maybe 10??). And I would create the userform with all 10 rows. But I'd hide rows 2-10. Then add a button to show another row (and maybe a button to hide the last row??). You can resize the userform and move buttons/controls down (or up) when you show/hide the next row. Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp Is pretty impressive in how it handles resizing and options. You may want to look at that for ideas. Anyway, if you follow this suggestion, then I think you're going to have some work to do. I created a small userform and added two buttons (show/hide) and a few controls (labels/checkboxes/textboxes) to the userform -- just 3 rows, though. But I named them nicely. Label_01, Label_02, Label_03 TextBox_01, Textbox_02, Textbox_03 etc. The _## was important. That was my indicator for what row the control was associated with. (The setup is gonna be a lot of work for you! And you'll have to modify/debug all that existing code that used the old names, too.) Anyway, this worked ok for me: Option Explicit Dim LastVisibleRow As Long Const MaxRows As Long = 3 'for testing Const IncSize As Long = 25 'worked ok for me Private Sub CommandButton1_Click() 'show another row Dim ctrl As Control If LastVisibleRow = MaxRows Then 'this shouldn't happen Beep Exit Sub End If With Me .Height = .Height + IncSize With .CommandButton1 .Top = .Top + IncSize End With With .CommandButton2 .Top = .Top + IncSize End With End With LastVisibleRow = LastVisibleRow + 1 For Each ctrl In Me.Controls If ctrl.Name Like "*_" & Format(LastVisibleRow, "00") Then ctrl.Visible = True End If Next ctrl 'set focus to the first control in the new row??? Me.Controls("Textbox_" & Format(LastVisibleRow, "00")).SetFocus Me.CommandButton1.Enabled = CBool(LastVisibleRow < MaxRows) Me.CommandButton2.Enabled = CBool(LastVisibleRow 1) End Sub Private Sub CommandButton2_Click() 'hide the last visible row Dim ctrl As Control If LastVisibleRow <= 1 Then 'this shouldn't happen Beep Exit Sub End If With Me .Height = .Height - IncSize With .CommandButton1 .Top = .Top - IncSize End With With .CommandButton2 .Top = .Top - IncSize End With End With For Each ctrl In Me.Controls If ctrl.Name Like "*_" & Format(LastVisibleRow, "00") Then ctrl.Visible = False If TypeOf ctrl Is MSForms.TextBox Then ctrl.Value = "" ElseIf TypeOf ctrl Is MSForms.ComboBox Then ctrl.ListIndex = -1 ElseIf TypeOf ctrl Is MSForms.CheckBox Then ctrl.Value = False ElseIf TypeOf ctrl Is MSForms.Label Then ctrl.Caption = "" End If End If Next ctrl LastVisibleRow = LastVisibleRow - 1 'set focus to the first control in the last visible row??? Me.Controls("Textbox_" & Format(LastVisibleRow, "00")).SetFocus Me.CommandButton1.Enabled = CBool(LastVisibleRow < MaxRows) Me.CommandButton2.Enabled = CBool(LastVisibleRow 1) End Sub Private Sub UserForm_Initialize() Dim ctrl As Control Dim iCtr As Long With Me.CommandButton1 .Caption = "Show another" .Enabled = True End With With Me.CommandButton2 .Caption = "Hide Last" .Enabled = False End With LastVisibleRow = 1 For Each ctrl In Me.Controls 'hide all the controls except for row 1 For iCtr = 2 To MaxRows If ctrl.Name Like "*_" & Format(iCtr, "00") Then ctrl.Visible = False End If Next iCtr Next ctrl End Sub I figured that it would be a good idea to clear the controls when they're hidden. You could also just loop looking to see if any control on any of the visible rows is used before you do stuff in that row. There's nothing sacred about my naming convention. If I recall correctly, you had nice names before. But you may want to limit the number of new rows to 9. Then you could just examine the last character in the name. (Then you don't have to worry about textbox1 and textbox11 being the same.) But you may have to worry about the other controls that aren't associated with the input rows. I don't want to hide Commandbutton2 when I hide row 2. --elizabeth wrote: I have a userform with three rows of comboboxes and textboxes (13 controls total) into which the user enters search criteria for an advancedfilter. This works great and I don't want to change the overall idea because there is too much code already written and the intended user likes it. What I would like to do is, instead of having three pre-defined rows of controls, just have one with a button so the user could add a new row for additional criteria if needed. That way, the user would not be limited to three criteria rows. What would be the best way to go about this using VBA? Thanks, --elizabeth -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to copy and paste row of controls on userform
I'm not sure I like this method.
It doesn't scale very well when you want to double the number of rows -- or even add 3 more! How about another alternative. Add a (hidden) worksheet to your workbook with the userform (an addin???). Then redesign the form to use just a single row where you can populate the fields. Then click an add button to add this new entry to the hidden sheet. You could display the current rules for your advanced filter in a listbox (nice titles and scroll right/left and up/down if you thought it was important. If you do this, you could keep your horizontal layout or even use something like Data|form (xl2003 menus). Take a look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform/index.htm The source code is available for a small fee ($20 USA, IIRC). So you can modify it as much as you want. Dave Peterson wrote: I'm not sure if this is a fit for you, but I'd still limit the number of rows (maybe 10??). And I would create the userform with all 10 rows. But I'd hide rows 2-10. Then add a button to show another row (and maybe a button to hide the last row??). You can resize the userform and move buttons/controls down (or up) when you show/hide the next row. Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp Is pretty impressive in how it handles resizing and options. You may want to look at that for ideas. Anyway, if you follow this suggestion, then I think you're going to have some work to do. I created a small userform and added two buttons (show/hide) and a few controls (labels/checkboxes/textboxes) to the userform -- just 3 rows, though. But I named them nicely. Label_01, Label_02, Label_03 TextBox_01, Textbox_02, Textbox_03 etc. The _## was important. That was my indicator for what row the control was associated with. (The setup is gonna be a lot of work for you! And you'll have to modify/debug all that existing code that used the old names, too.) Anyway, this worked ok for me: Option Explicit Dim LastVisibleRow As Long Const MaxRows As Long = 3 'for testing Const IncSize As Long = 25 'worked ok for me Private Sub CommandButton1_Click() 'show another row Dim ctrl As Control If LastVisibleRow = MaxRows Then 'this shouldn't happen Beep Exit Sub End If With Me .Height = .Height + IncSize With .CommandButton1 .Top = .Top + IncSize End With With .CommandButton2 .Top = .Top + IncSize End With End With LastVisibleRow = LastVisibleRow + 1 For Each ctrl In Me.Controls If ctrl.Name Like "*_" & Format(LastVisibleRow, "00") Then ctrl.Visible = True End If Next ctrl 'set focus to the first control in the new row??? Me.Controls("Textbox_" & Format(LastVisibleRow, "00")).SetFocus Me.CommandButton1.Enabled = CBool(LastVisibleRow < MaxRows) Me.CommandButton2.Enabled = CBool(LastVisibleRow 1) End Sub Private Sub CommandButton2_Click() 'hide the last visible row Dim ctrl As Control If LastVisibleRow <= 1 Then 'this shouldn't happen Beep Exit Sub End If With Me .Height = .Height - IncSize With .CommandButton1 .Top = .Top - IncSize End With With .CommandButton2 .Top = .Top - IncSize End With End With For Each ctrl In Me.Controls If ctrl.Name Like "*_" & Format(LastVisibleRow, "00") Then ctrl.Visible = False If TypeOf ctrl Is MSForms.TextBox Then ctrl.Value = "" ElseIf TypeOf ctrl Is MSForms.ComboBox Then ctrl.ListIndex = -1 ElseIf TypeOf ctrl Is MSForms.CheckBox Then ctrl.Value = False ElseIf TypeOf ctrl Is MSForms.Label Then ctrl.Caption = "" End If End If Next ctrl LastVisibleRow = LastVisibleRow - 1 'set focus to the first control in the last visible row??? Me.Controls("Textbox_" & Format(LastVisibleRow, "00")).SetFocus Me.CommandButton1.Enabled = CBool(LastVisibleRow < MaxRows) Me.CommandButton2.Enabled = CBool(LastVisibleRow 1) End Sub Private Sub UserForm_Initialize() Dim ctrl As Control Dim iCtr As Long With Me.CommandButton1 .Caption = "Show another" .Enabled = True End With With Me.CommandButton2 .Caption = "Hide Last" .Enabled = False End With LastVisibleRow = 1 For Each ctrl In Me.Controls 'hide all the controls except for row 1 For iCtr = 2 To MaxRows If ctrl.Name Like "*_" & Format(iCtr, "00") Then ctrl.Visible = False End If Next iCtr Next ctrl End Sub I figured that it would be a good idea to clear the controls when they're hidden. You could also just loop looking to see if any control on any of the visible rows is used before you do stuff in that row. There's nothing sacred about my naming convention. If I recall correctly, you had nice names before. But you may want to limit the number of new rows to 9. Then you could just examine the last character in the name. (Then you don't have to worry about textbox1 and textbox11 being the same.) But you may have to worry about the other controls that aren't associated with the input rows. I don't want to hide Commandbutton2 when I hide row 2. --elizabeth wrote: I have a userform with three rows of comboboxes and textboxes (13 controls total) into which the user enters search criteria for an advancedfilter. This works great and I don't want to change the overall idea because there is too much code already written and the intended user likes it. What I would like to do is, instead of having three pre-defined rows of controls, just have one with a button so the user could add a new row for additional criteria if needed. That way, the user would not be limited to three criteria rows. What would be the best way to go about this using VBA? Thanks, --elizabeth -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to copy / paste in userform multiline text box | Excel Programming | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
Question for Peter T - Copy Paste controls at runtime | Excel Programming | |||
Copy & paste image from UserForm | Excel Programming | |||
copy-paste in a userform | Excel Programming |