General Novice Questions (Protection, Format)

I have created a spreadsheet and have restricted a row and footing row
(totals) from being changed by using the "format cells / protection"
feature. I have left all the data entry rows
unprotected so that the entry people can make changes. These entry
rows have columns with specfics formats like Date, Currency, etc and I
make it all look nice with the grid feature. Finally,
I set "Tools /Protection/ Protect sheet" to active the protected
cells.

The problem is how can the user Insert new rows?
To get around this, I created enough rows so that the user has the
space they need (50 rows) to accomodate the necessary entries. The
new problem is that if the user forgets to make any entry they have to
Insert which they cannot do because of the protection. So, to get
around this they can drag the lower existing data rows down, but then
the opening they create loses the format that I set for that column
and the nice grid I put in disappears in those rows?

Any tips?

Thanks

Ron
Sat May 10 11:09:38 CDT 2008

Depending on your version of Excel, you may have this option....

From the Excel Main Menu:
<tools><protection><protect sheet>
Allow:
...Check: Insert rows
...Check: Format rows
Set the Password
Click [OK]

Now the users will be able to insert rows
and those rows will inherit the format
from the row above.

Another alternative is to put
the total row ABOVE the data.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Greg" <ApexData@gmail.com> wrote in message
news:777925a9-d63d-4b2a-b1f1-c56c16266bd8@j22g2000hsf.googlegroups.com...
> I have created a spreadsheet and have restricted a row and footing row
> (totals) from being changed by using the "format cells / protection"
> feature. I have left all the data entry rows
> unprotected so that the entry people can make changes. These entry
> rows have columns with specfics formats like Date, Currency, etc and I
> make it all look nice with the grid feature. Finally,
> I set "Tools /Protection/ Protect sheet" to active the protected
> cells.
>
> The problem is how can the user Insert new rows?
> To get around this, I created enough rows so that the user has the
> space they need (50 rows) to accomodate the necessary entries. The
> new problem is that if the user forgets to make any entry they have to
> Insert which they cannot do because of the protection. So, to get
> around this they can drag the lower existing data rows down, but then
> the opening they create loses the format that I set for that column
> and the nice grid I put in disappears in those rows?
>
> Any tips?
>
> Thanks
>
>

Earl
Sat May 10 11:20:14 CDT 2008

Greg,

Once the worksheet is protected, as you know, inserting is disallowed. Drag-moving is
allowed, but should not be used if there are formulas in the worksheet, as it's possible
that the formulas will change as a result of the move. Drag-moving is a design tool, not a
data entry tool.

The best way would be to have a macro available for the user. It would unprotect the sheet,
insert the new row (perhaps where the active cell is currently), then re--protect the sheet.
The macro could be invoked by a button, a keyboard shortcut, or even a new menu item. You'd
probably also want a macro to remove a row, in the event that a user adds a row, then later
doesn't want it.

We can write the macro for you if you're able to put a macro in your sheet. You can bone up
on how to work with macros it at www.mcgimpsey.com if you're interested. Post back for the
macro code.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------
"Greg" <ApexData@gmail.com> wrote in message
news:777925a9-d63d-4b2a-b1f1-c56c16266bd8@j22g2000hsf.googlegroups.com...
>I have created a spreadsheet and have restricted a row and footing row
> (totals) from being changed by using the "format cells / protection"
> feature. I have left all the data entry rows
> unprotected so that the entry people can make changes. These entry
> rows have columns with specfics formats like Date, Currency, etc and I
> make it all look nice with the grid feature. Finally,
> I set "Tools /Protection/ Protect sheet" to active the protected
> cells.
>
> The problem is how can the user Insert new rows?
> To get around this, I created enough rows so that the user has the
> space they need (50 rows) to accomodate the necessary entries. The
> new problem is that if the user forgets to make any entry they have to
> Insert which they cannot do because of the protection. So, to get
> around this they can drag the lower existing data rows down, but then
> the opening they create loses the format that I set for that column
> and the nice grid I put in disappears in those rows?
>
> Any tips?
>
> Thanks
>
>

Greg
Sat May 10 12:14:52 CDT 2008

I have programmed with MS Access frequently and am familiar with VBA.
Can you give sample code for a button to Add and one for Del and how
to go about it.
I think once I see it, I'll be on the way to getting this
accomplished.

Thanks Again
Greg







On May 10, 12:20=A0pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Greg,
>
> Once the worksheet is protected, as you know, inserting is disallowed. =A0=
Drag-moving is
> allowed, but should not be used if there are formulas in the worksheet, as=
it's possible
> that the formulas will change as a result of the move. =A0Drag-moving is a=
design tool, not a
> data entry tool.
>
> The best way would be to have a macro available for the user. =A0It would =
unprotect the sheet,
> insert the new row (perhaps where the active cell is currently), then re--=
protect the sheet.
> The macro could be invoked by a button, a keyboard shortcut, or even a new=
menu item. =A0You'd
> probably also want a macro to remove a row, in the event that a user adds =
a row, then later
> doesn't want it.
>
> We can write the macro for you if you're able to put a macro in your sheet=
. =A0You can bone up
> on how to work with macros it atwww.mcgimpsey.comif you're interested. =A0=
Post back for the
> macro code.
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
>
> -----------------------------------------------------------------------"Gr=
eg" <ApexD...@gmail.com> wrote in message
>
> news:777925a9-d63d-4b2a-b1f1-c56c16266bd8@j22g2000hsf.googlegroups.com...
>
>
>
> >I have created a spreadsheet and have restricted a row and footing row
> > (totals) from being changed by using the "format cells / protection"
> > feature. =A0I have left all the data entry rows
> > unprotected so that the entry people can make changes. =A0These entry
> > rows have columns with specfics formats like Date, Currency, etc and I
> > make it all look nice with the grid feature. Finally,
> > I set "Tools /Protection/ Protect sheet" to active the protected
> > cells.
>
> > The problem is how can the user Insert new rows?
> > To get around this, I created enough rows so that the user has the
> > space they need (50 rows) to accomodate the necessary entries. =A0The
> > new problem is that if the user forgets to make any entry they have to
> > Insert which they cannot do because of the protection. So, to get
> > around this they can drag the lower existing data rows down, but then
> > the opening they create loses the format that I set for that column
> > and the nice grid I put in disappears in those rows?
>
> > Any tips?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Greg
Sat May 10 12:16:04 CDT 2008

I'm using MS EXCEL 2000

Greg
Sat May 10 13:20:31 CDT 2008

I figured out how to create Add / Del buttons and tied this code to
it.
It seems to be working fine. Anyone recognize any problems or
oversights ?

Thanks
Greg

Private Sub CommandButton1_Click()
Me.Unprotect
Rows(ActiveCell.Row).Delete
Me.Protect
End Sub

Private Sub CommandButton2_Click()
Me.Unprotect
Rows(ActiveCell.Row).Insert
Me.Protect
End Sub

Earl
Sat May 10 13:29:13 CDT 2008

Greg,

Here are the macros. Put them in a regular module, and assign them to keyboard shortcuts, if
desired (Tools - Macros - Macros - Options). Or put a buttons on the sheet (drawing
toolbar), and assign them to the macros.

Sub InsertRow()
If ActiveCell.Locked = True Then
MsgBox "You can't insert a row here", vbOKOnly, ""
Exit Sub
End If
ActiveSheet.Unprotect Password:="aa"
ActiveCell.EntireRow.Insert
ActiveSheet.Protect Password:="aa"
End Sub

Sub DeleteRow()
If ActiveCell.Locked = True Then
MsgBox "You can't remove a row here", vbOKOnly, ""
Exit Sub
End If
ActiveSheet.Unprotect Password:="aa"
ActiveCell.EntireRow.Delete
ActiveSheet.Protect Password:="aa"
End Sub


These routines will allow inserting/deleting rows based on the active cell being protected.
If there are other cells in the sheet that are unprotected, they'll allow
inserting/deleting, which is undesirable. If there are such other cells, we'll need another
means of checking. Post back if that's the case.

If you're not using passwords in your sheet protection, remove the Password:="aa" part.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Greg" <ApexData@gmail.com> wrote in message
news:5083b55c-66ac-415c-8e04-6c57ffaf09cb@x41g2000hsb.googlegroups.com...
I have programmed with MS Access frequently and am familiar with VBA.
Can you give sample code for a button to Add and one for Del and how
to go about it.
I think once I see it, I'll be on the way to getting this
accomplished.

Thanks Again
Greg







On May 10, 12:20 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Greg,
>
> Once the worksheet is protected, as you know, inserting is disallowed. Drag-moving is
> allowed, but should not be used if there are formulas in the worksheet, as it's possible
> that the formulas will change as a result of the move. Drag-moving is a design tool, not a
> data entry tool.
>
> The best way would be to have a macro available for the user. It would unprotect the
> sheet,
> insert the new row (perhaps where the active cell is currently), then re--protect the
> sheet.
> The macro could be invoked by a button, a keyboard shortcut, or even a new menu item.
> You'd
> probably also want a macro to remove a row, in the event that a user adds a row, then
> later
> doesn't want it.
>
> We can write the macro for you if you're able to put a macro in your sheet. You can bone
> up
> on how to work with macros it atwww.mcgimpsey.comif you're interested. Post back for the
> macro code.
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
>
> -----------------------------------------------------------------------"Greg"
> <ApexD...@gmail.com> wrote in message
>
> news:777925a9-d63d-4b2a-b1f1-c56c16266bd8@j22g2000hsf.googlegroups.com...
>
>
>
> >I have created a spreadsheet and have restricted a row and footing row
> > (totals) from being changed by using the "format cells / protection"
> > feature. I have left all the data entry rows
> > unprotected so that the entry people can make changes. These entry
> > rows have columns with specfics formats like Date, Currency, etc and I
> > make it all look nice with the grid feature. Finally,
> > I set "Tools /Protection/ Protect sheet" to active the protected
> > cells.
>
> > The problem is how can the user Insert new rows?
> > To get around this, I created enough rows so that the user has the
> > space they need (50 rows) to accomodate the necessary entries. The
> > new problem is that if the user forgets to make any entry they have to
> > Insert which they cannot do because of the protection. So, to get
> > around this they can drag the lower existing data rows down, but then
> > the opening they create loses the format that I set for that column
> > and the nice grid I put in disappears in those rows?
>
> > Any tips?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Greg
Sat May 10 15:37:08 CDT 2008

Thanks Earl

This is what I came up with I had some additional issues that had to
be addressed:

Private Sub CommandButton1_Click()
'Insert Row Button
If ActiveCell.Row > 6 And ActiveSheet.UsedRange.Rows.Count >
ActiveCell.Row Then
Me.Unprotect
Rows(ActiveCell.Row).Insert
Me.Protect
Else
Beep
End If
End Sub

Private Sub CommandButton2_Click()
'Delete Row Button
If ActiveCell.Row > 6 And ActiveSheet.UsedRange.Rows.Count >
ActiveCell.Row Then
Me.Unprotect
Rows(ActiveCell.Row).Delete
Me.Protect
Else
Beep
End If
End Sub


Thanks Again
Greg

Earl
Sat May 10 22:53:15 CDT 2008

Greg,

Looks good, except the UsedRange can get bloated. When you've deleted rows, it still can
show a used range larger than it currently is. Closing and reopening the workbook is the
usual way to reset it. It sounds as though your records go to the bottom of your used
range, and there are no totals and other junk below them (your Access roots are showing!
:) ). In that case it probably doesn't matter if anyone inserts or deletes rows beyond your
last record.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------
"Greg" <ApexData@gmail.com> wrote in message
news:2f21b7bd-6a82-4459-bbd6-694cc1ec3113@p25g2000hsf.googlegroups.com...
> Thanks Earl
>
> This is what I came up with I had some additional issues that had to
> be addressed:
>
> Private Sub CommandButton1_Click()
> 'Insert Row Button
> If ActiveCell.Row > 6 And ActiveSheet.UsedRange.Rows.Count >
> ActiveCell.Row Then
> Me.Unprotect
> Rows(ActiveCell.Row).Insert
> Me.Protect
> Else
> Beep
> End If
> End Sub
>
> Private Sub CommandButton2_Click()
> 'Delete Row Button
> If ActiveCell.Row > 6 And ActiveSheet.UsedRange.Rows.Count >
> ActiveCell.Row Then
> Me.Unprotect
> Rows(ActiveCell.Row).Delete
> Me.Protect
> Else
> Beep
> End If
> End Sub
>
>
> Thanks Again
> Greg

Greg
Wed May 14 11:08:20 CDT 2008

Thanks Earl

Actually I do have totals at the bottom of the worksheet. I guest I
could move them up to the top.
The problem now is that the range does expand causing the worksheet to
get larger than I would like.
Is there a way to lock in the totals row to the specific location (say
row50). O create some kind of restrictive
window. Or do I need to build logic into my existing code to make the
adjustments somehow in response to the
button being pressed?

Thanks
Greg


On May 10, 11:53=A0pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Greg,
>
> Looks good, except the UsedRange can get bloated. =A0When you've deleted r=
ows, it still can
> show a used range larger than it currently is. =A0Closing and reopening th=
e workbook is the
> usual way to reset it. =A0It sounds as though your records go to the botto=
m of your used
> range, and there are no totals and other junk below them (your Access root=
s are showing!
> :) ). =A0In that case it probably doesn't matter if anyone inserts or dele=
tes rows beyond your
> last record.
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
>
> -----------------------------------------------------------------------"Gr=
eg" <ApexD...@gmail.com> wrote in message
>
> news:2f21b7bd-6a82-4459-bbd6-694cc1ec3113@p25g2000hsf.googlegroups.com...
>
>
>
> > Thanks Earl
>
> > This is what I came up with I had some additional issues that had to
> > be addressed:
>
> > Private Sub CommandButton1_Click()
> > =A0 =A0'Insert Row Button
> > =A0 =A0If ActiveCell.Row > 6 And ActiveSheet.UsedRange.Rows.Count >
> > ActiveCell.Row Then
> > =A0 =A0 =A0 =A0Me.Unprotect
> > =A0 =A0 =A0 =A0Rows(ActiveCell.Row).Insert
> > =A0 =A0 =A0 =A0Me.Protect
> > =A0 =A0Else
> > =A0 =A0 =A0 =A0Beep
> > =A0 =A0End If
> > End Sub
>
> > Private Sub CommandButton2_Click()
> > =A0 =A0'Delete Row Button
> > =A0 =A0If ActiveCell.Row > 6 And ActiveSheet.UsedRange.Rows.Count >
> > ActiveCell.Row Then
> > =A0 =A0 =A0 Me.Unprotect
> > =A0 =A0 =A0 Rows(ActiveCell.Row).Delete
> > =A0 =A0 =A0 Me.Protect
> > =A0 =A0Else
> > =A0 =A0 =A0 Beep
> > =A0 =A0End If
> > End Sub
>
> > Thanks Again
> > Greg- Hide quoted text -
>
> - Show quoted text -