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.

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" wrote in message
...
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" wrote in message
...
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?

Thank you! Great code. <g

"StargateFanFromWork" wrote in message
...
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.

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" wrote in message
...

"JulieD" wrote in message
...
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?

Thank you! Great code. <g

"StargateFanFromWork" wrote in message
...
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.