General Novice Questions (Protection, Format)

[Greg]

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

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)

 

[Earl Kiosterud]

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.

 

[Greg]

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

 

[Greg]

I'm using MS EXCEL 2000

 

[Greg]

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

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

 

[Greg]

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

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.

 

[Greg]

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