Data input pop up box

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207
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. :biggrin:
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I'm not sure what you are trying to do. This code does not insert rows, it inserts page breaks.
 

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207
My bad. You are correct. I am inserting page breaks rather than rows. :oops:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does this work for you?

Code:
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

Board Regular
Joined
Jun 18, 2002
Messages
207
Wow, that's amazing! I never knew about this InputBox command. Thanks for all your help. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,634
Members
410,804
Latest member
bluepinky
Top