text box to tell form how many lines to unhide

  • Thread starter Thread starter tpeter
  • Start date Start date
T

tpeter

I have a form for end users that gives them the ability to show 1-250 lines,
depending on how many changes need to be made. This is controled by a
userform with a text box asking them how many lines they would like. All 250
lines are there but not visible until they put a quantity in.

I have made a macro for every choice between 1 and 250 and sourced the
textbox to the each macro. Here is a sample of the macro:

Sub one()
'
' one Macro
' Macro recorded 10/14/2009 by tpeter
'

'
Rows("16:264").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-21
End Sub

Here is a sample of the textbox sourcing:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Range("m2").Value = TextBox1.Value

If Range("m2").Value = "1" Then
Call Unhide_Rows
Call one
End If

Is there a simpler way to do this, instead of making 250 macro's? Thanks for
your help.

Tim Peter
 
Why not eliminate all the buttons and just ask the user how many rows to
reveal (perhaps in response to a CommandButton Click event)? I'm thinking of
something like this...

Dim Response As String
Response = InputBox("How many rows should be visible?")
If IsNumeric(Response) Then
If CLng(Response) > 0 And CLng(Response) <= 250 Then
Rows(15).Resize(250).Hidden = True
Rows(15).Resize(Response).Hidden = False
End If
End If
 
Back
Top