Inserting rows syntax + row #'ing code in protected sheet?

[StargateFanFromWork]

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 sheet works just great! I'm
testing it out here as I work. The code, courtesy of Dave Peterson, is:

With ActiveSheet
.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 sheet 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 sheet
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 sheet 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.

 

[JulieD]

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
ActiveSheet.Unprotect
k = j + i - 1
Range("" & j & ":" & k & "").Insert shift:=xlDown
ActiveSheet.Protect
End Sub

 

[StargateFanFromWork]

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
ActiveSheet.Unprotect
k = j + i - 1
Range("" & j & ":" & k & "").Insert shift:=xlDown
ActiveSheet.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?

 

[JulieD]

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

ActiveSheet.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
ActiveSheet.Protect
Exit Sub

dontdothat:
Resume

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

Let me know how you go

Cheers
JulieD

 

[StargateFanFromWork]

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 -

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