Best way to copy and paste row of controls on userform

[--elizabeth]

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]

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.

 

[Dave Peterson]

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.