S
Scott
Hi, I have a sheet which I want the user to be able to specify the number or
rows to insert.
rows to insert.
S
Scott
I'll try that again... I have a sheet(s) which I want the user to be able to
specify the number or rows to insert. I already have the code to get the
cursor to the right place in the sheet to insert the rows. What I would like
is to have button which brings up a dialog box asking "how many rows?" the
user types in "n" and clicks OK, then excel inserts "n" rows above the cursor.
Any thoughts?
cheers
Scott
specify the number or rows to insert. I already have the code to get the
cursor to the right place in the sheet to insert the rows. What I would like
is to have button which brings up a dialog box asking "how many rows?" the
user types in "n" and clicks OK, then excel inserts "n" rows above the cursor.
Any thoughts?
cheers
Scott
B
Bob Bridges
Cool, Scott! I've always wanted to have such a sheet. Congratulations.
How's it working for you?
Seriously, you gotta ask a question, man. As it is, you don't need to post
this on the Excel Programming forum; the solution is just to have your user
place the selection where he wants the new rows to go, then highlight as many
cells (in the downward direction) as he wants to insert - if he wants to
insert three rows, he specifies that by hitting <Shift-Down-Down>, thus
highlighting three cells. Then he can hit <Alt-I>nsert, Rows (I think that's
right, though I'm doing it by memory) and voila! three new rows inserted,
without your even having to write a program for it.
But you wouldn't have posted here if you were satisfied with that solution,
so obviously you have some constraints. What are they? What DON'T you want
your user to have to do, and what are you willing to do yourself, and what do
you want the solution to look like to the user, and what have you tried
already, and what happens when you try that? Give us something to go on.
How's it working for you?
Seriously, you gotta ask a question, man. As it is, you don't need to post
this on the Excel Programming forum; the solution is just to have your user
place the selection where he wants the new rows to go, then highlight as many
cells (in the downward direction) as he wants to insert - if he wants to
insert three rows, he specifies that by hitting <Shift-Down-Down>, thus
highlighting three cells. Then he can hit <Alt-I>nsert, Rows (I think that's
right, though I'm doing it by memory) and voila! three new rows inserted,
without your even having to write a program for it.
But you wouldn't have posted here if you were satisfied with that solution,
so obviously you have some constraints. What are they? What DON'T you want
your user to have to do, and what are you willing to do yourself, and what do
you want the solution to look like to the user, and what have you tried
already, and what happens when you try that? Give us something to go on.
R
RyanH
When you say you have code to bring your cursor to the right place on the
sheet I am assuming you are selecting a cell. If that is the case insert
this portion of code after you have selected the cell:
Dim x As Integer
On Error Resume Next
x = InputBox("How many rows do you want to insert?")
On Error GoTo 0
If x <> 0 Then
Rows(ActiveCell.Row).Resize(x).EntireRow.Insert
End If
Hope this helps! If so, please click "YES" below.
sheet I am assuming you are selecting a cell. If that is the case insert
this portion of code after you have selected the cell:
Dim x As Integer
On Error Resume Next
x = InputBox("How many rows do you want to insert?")
On Error GoTo 0
If x <> 0 Then
Rows(ActiveCell.Row).Resize(x).EntireRow.Insert
End If
Hope this helps! If so, please click "YES" below.
S
Scott
G'Day 'Bob'
You do a good job with sarcasm, makes an Australian blush. However you will
notice I immediately posted a reply to my question after a fat finger fault
cut the original post short.
Thank you for your concern and I agree to all your points.
Scott
You do a good job with sarcasm, makes an Australian blush. However you will
notice I immediately posted a reply to my question after a fat finger fault
cut the original post short.
Thank you for your concern and I agree to all your points.
Scott
R
RyanH
Did my post help you? Or is there something else?
S
Scott
G'Day Ryan,
Yep, it works a treat. I just have to figure out the easist way to apply the
formats and formulas from the row above the cursor start position. But thats
another problem, your code worked great, thanks.
Scott
Yep, it works a treat. I just have to figure out the easist way to apply the
formats and formulas from the row above the cursor start position. But thats
another problem, your code worked great, thanks.
Scott
R
RyanH
If you add 1 to the ActiveCell.Row it will insert the formats also. The
formulas you will have to do yourself.
Rows(ActiveCell.Row+1).Resize(x).EntireRow.Insert
Hope this helps! If so, please click "YES" below
formulas you will have to do yourself.
Rows(ActiveCell.Row+1).Resize(x).EntireRow.Insert
Hope this helps! If so, please click "YES" below
B
Bob Bridges
Aww, you spoil my enjoyment, Scott. If you just hit a button too soon,
that's nothing I may reasonably wax sarcastic about.
Oh, well, I can always hope for next time.
that's nothing I may reasonably wax sarcastic about.
Oh, well, I can always hope for next time.