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
Similar Threads
