Hi, I have a sheet which I want the user to be able to specify the number or
rows to insert.

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

"Scott" wrote:

> Hi, I have a sheet which I want the user to be able to specify the number or
> rows to insert.

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.

--- "Scott" wrote:
> Hi, I have a sheet which I want the user to be able to specify the number or
> rows to insert.

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.
--
Cheers,
Ryan


"Scott" wrote:

> 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
>
> "Scott" wrote:
>
> > Hi, I have a sheet which I want the user to be able to specify the number or
> > rows to insert.

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

"Bob Bridges" wrote:

> 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.
>
> --- "Scott" wrote:
> > Hi, I have a sheet which I want the user to be able to specify the number or
> > rows to insert.

Did my post help you? Or is there something else?
--
Cheers,
Ryan


"Scott" wrote:

> 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
>
> "Bob Bridges" wrote:
>
> > 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.
> >
> > --- "Scott" wrote:
> > > Hi, I have a sheet which I want the user to be able to specify the number or
> > > rows to insert.

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

"RyanH" wrote:

> Did my post help you? Or is there something else?
> --
> Cheers,
> Ryan
>
>
> "Scott" wrote:
>
> > 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
> >
> > "Bob Bridges" wrote:
> >
> > > 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.
> > >
> > > --- "Scott" wrote:
> > > > Hi, I have a sheet which I want the user to be able to specify the number or
> > > > rows to insert.

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
--
Cheers,
Ryan


"Scott" wrote:

> 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
>
> "RyanH" wrote:
>
> > Did my post help you? Or is there something else?
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Scott" wrote:
> >
> > > 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
> > >
> > > "Bob Bridges" wrote:
> > >
> > > > 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.
> > > >
> > > > --- "Scott" wrote:
> > > > > Hi, I have a sheet which I want the user to be able to specify the number or
> > > > > rows to insert.

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. :-)

--- "Scott" wrote:
> 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.
>
> --- "Bob Bridges" wrote:
> > 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.
> >
> > --- "Scott" wrote:
> > > Hi, I have a sheet which I want the user to be able to specify the number or
> > > rows to insert.