Re: Inserting rows syntax + row #'ing code in protected ***?

From: StargateFanFromWork (NoJunk_at_NoJunkMail.com)
Date: 07/05/04 

Date: Mon, 5 Jul 2004 11:44:44 -0400

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:uP34JAqYEHA.1764@TK2MSFTNGP10.phx.gbl...
> Hi
>
> here's one method for you
>
> Sub insertrows()
> Dim i As Long
> Dim j As Long
> Dim k As Long
> Do
> i = InputBox("How many rows do you want to insert?", "Insert
Rows",
> 1)
> Loop Until i <> 0
> Do
> j = InputBox("At what row number do you want to start the
> insertion?", "Insert Rows", 1)
> Loop Until j <> 0
> Active***.Unprotect
> k = j + i - 1
> Range("" & j & ":" & k & "").Insert shift:=xlDown
> Active***.Protect
> End Sub

This is ingenious. Very neat. You were so very quick with a response that
this is code that probably gets pulled a lot, eh? <g>

2 questions:

1) I see that it seems to loop over and over until a user enters a number
for each item, # of rows and where to enter. Any way to get an "on error go
to end" type of syntax? Sometimes we press a button by mistake (as I just
did <g>) so that would be neat. I think that I'd put two of these
statements, one above each of the two "loop" statements above?

2) Any way to deal with the row coding =ROW()-1 in column A? It doesn't
get plugged in with a simple insert. I usu. just ^D from cell above. But
maybe I'll just leave column A unprotected and user can copy/paste from cell
above?

Thank you! Great code. <g>

> "StargateFanFromWork" <NoJunk@NoJunkMail.com> wrote in message
> news:esJCP6pYEHA.2840@TK2MSFTNGP11.phx.gbl...
> > Good morning! <g>
> >
> > The syntax to put basically at beginning and end of a macro that's
> assigned
> > to a button to allow it to work in a protected *** works just great!
> I'm
> > testing it out here as I work. The code, courtesy of Dave Peterson, is:
> >
> > With Active***
> > .Unprotect
> > (my code goes here)
> > .Protect
> > End With
> >
> >
> > But, as always seems to be the case, I've run into a particular snag. I
> > didn't realize that the insert function is gone once a *** is
protected
> > <g>. So we can't insert new rows to accommodate new data once it's
> > protected.
> >
> > Just as an fyi, the rows are set up with conditional formatting so that
> > there is a light yellow fill on odd or even rows (can't remember which,
> but
> > one of those). And in column A I have the automatic row numbering
syntax
> > "=ROW()-1". Thank goodness nothing more complicated than that! <g>
> >
> > Anywho, I've thought and thought just what would be easiest way to do
this
> > because I'm just no good at figuring out VB code. I don't even know
where
> > to go to get syntax other than these ngs as I've had no success online
> when
> > searching for VB coding, so it would be like pulling magic out of thin
air
> > for me. So to keep it simple, perhaps I can create a button on the
***
> > that says "insert row" whose function would be to pull up an insert row
> > dialogue box? I could plug in the unprotect/protect code there for it
at
> > the appropriate spots to make it work. I suppose user could be prompted
> > with a message box to go to next empty row in the print area? Or perhaps
> > there's a better way?
> >
> > Of course, there is the option, I suppose, of just making that button
and
> > then assigning a message box macro to it to say "unprotect the *** to
> > insert rows, then protect again" if worse came to worst, but I feel that
> > would defeat the purpose! <g> And I may not know a lot, but there's
> plenty
> > of people who always know less, too! <g> I'd like to make it as easy as
> > possible for my successor.
> >
> > <sigh> They want us clerks to perform miracles, yet not give us the
> tools!
> > Wish they'd send me on VB training, but nothing can ever be justified
when
> > you're a contract worker! <g> Where would I be without this ng??
> >
> > Thanks so much.
> >
> >
>
>