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

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

Date: Mon, 5 Jul 2004 12:56:57 -0400

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:#1PPZjqYEHA.3664@TK2MSFTNGP12.phx.gbl...
> Hi
>
> thanks for the v. generous comments about my code ... i'm always scared to

Hey, it's great stuff! <g>

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

Well, I know what that's like. I need to take a course or something in VB
if one day I can ever afford to <sigh>. I do have extensive scripting
experience with WordPerfect macros and Filemaker Pro scripting which has
helped tremendously! Unlike VB, must admit I find them very intuitive (plus
WP has a command builder with pretty straightforward code easy to find).
But I mean to learn VB! It's so much fun to get things to work to make life
easier!

> 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

Great having the defaults in, you're right!

> formula in column A - hope this helps

The is like magic. The coding for column A works perfectly, thanks!

****************************
I ran into a bit of trouble with the error coding, though, but it should be
easy enough to fix I think.

It still forces user to input something or at least press enter as the
cancel and X in the right-hand corner just cause dialogue box to loop. I
mentioned WP above because I'm going to reference a handy piece of syntax I
always used in conjunction with an error one. If memory serves (no WP here
to take a peek at), I do something like this:

Label (Step 1)
OnCancel (Step 2)
OnError (Step 2)

(my code here)

and there was a third such operation, but I can't remember it now. (It's
been about 2 years since I touched WP code as they're all pretty much
written up and I haven't needed to update my templates at all and just use
them like that. The above syntax is probably not 100% right, but I think
you get the idea (?).)

Anyway, is there is an extra command to handle "cancel" in VB? That way, if
the user _ever_ presses that or the X in the upper right-hand corner to
_either_ of the insert questions, the macro just exits without doing
anything. Since Cancel is handled separately from Error in one program, it
might be in others, maybe?? Anyway, this would take care of when we
accidentally click the button when we didn't want to, or get interrupted any
time while the macro is running and we need to abort.

Thanks! This is great stuff. I'm always so impressed with how all this
coding looks and acts. <g>

> *********
> 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>