Data input pop up box

[simonf]

Hi all,

I have written a simple macro to insert a row between each of a preset range of rows.

Sub InsertRows()

For i = 1 To 5

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

Next i

End Sub

Is it possible to have a pop up input box for the user to input the number of rows to insert when the macro is run?

eg. Alt F8 and select InsertRows macro
Pop up box to input number of rows
Click "ok" and the macro continues to run to insert the rows.

Thanks in advance.

 

[Scott Huish]

I'm not sure what you are trying to do. This code does not insert rows, it inserts page breaks.

 

[simonf]

My bad. You are correct. I am inserting page breaks rather than rows.

 

[Scott Huish]

Does this work for you?

Sub InsertRows()
Dim i, j As Long
Do Until IsNumeric(i) And i <> ""
    i = InputBox("Enter number of page breaks to insert")
    If i = "" Then Exit Sub
    If i < 1 Or i > 1026 Then i = ""
Loop

For j = 1 To i
    ActiveCell.Offset(1, 0).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Next j

End Sub

 

[simonf]

Wow, that's amazing! I never knew about this InputBox command. Thanks for all your help.