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

From: JulieD (JulieD_at_hctsReMoVeThIs.net.au)
Date: 07/05/04 

Date: Mon, 5 Jul 2004 23:56:00 +0800

Hi

thanks for the v. generous comments about my code ... i'm always scared to
post code here as its never as neat or as compact as some of the other
people who post - but it (generally) gets the job done at least -

in regards to your first question - that's why i put a default of 1 in for
both questions - just in case someone just pressed Enter without reading the
question ...but i've included on error code and also code to copy down the
formula in column A - hope this helps

*********
Sub insertrows()

    Dim i As Long
    Dim j As Long
    Dim k As Long

On Error GoTo dontdothat
    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
        j = j - 1
        Range("A" & j & "").Select
        Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""),
Type:=xlFillDefault
    Active***.Protect
    Exit Sub

dontdothat:
    Resume

End Sub
**********

Let me know how you go

Cheers
JulieD

"StargateFanFromWork" <NoJunk@NoJunkMail.com> wrote in message
news:OpFe$bqYEHA.396@TK2MSFTNGP10.phx.gbl...
>
> "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.
> > >
> > >
> >
> >
>
>