how do i protect a cell from being change but allow others

[beeman]

I have been tasked with writing a user friendly analytical template at work. It is for some of our staff that has very poor excel skills. I know more and better training would be the more logical approach, however they are all 5 years and lower to retirement and this would be harder then you can possibly imagin.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
What I have is a template with two buttons. One to add a line and copy the previous row formulas, as follows:
<o></o>
Private Sub CommandButton4_Click()<o></o>
'make new row<o></o>
ActiveCell.EntireRow.Insert shift:=xlDown<o></o>
'copy the row<o></o>
ActiveCell.Offset(1).EntireRow.Copy Cells(ActiveCell.Row, 1)<o></o>
On Error Resume Next<o></o>
End Sub
<o></o>
And another to delete a line
<o></o>
Private Sub CommandButton3_Click()<o></o>
'to delet row<o></o>
ActiveCell.EntireRow.Delete<o></o>
End Sub
<o></o>
They both work great provided the excel worksheet isn’t protected.
<o></o>
My problem is on the rows I want to be able to add a line and delete a line I also have some formulas I need protected. When I protect the document these macro’s stop working.
<o></o>
If I set the macro up to unlock the page do the change and then relock it non of the cells are protected from the delete button. Some of the staff are click happy and would end up deleteing the entire document before they relize it.
<o></o>
What I need is either a code to prevent these macro’s from working on a specified area but allow it on another. (This area would of course always change as rows are added and deleted), or I need a different way to protect my regular formulas and headings so these macro’s will work.
<o></o>
Thanks for your help and time.

 

[Gary McMaster]

The worksheet protect method has a "UserInterfaceOnly" property that you can use when you protect the sheet via code. This allows macros to change the sheet but won't allow changes from the GUI.

Sounds like that property may be of use to you.

Gary

 

[beeman]

Thanks for the reply
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Forgive my ignorance but I don’t know what a GUI is.
<o> </o>
The “UserInterfaceOnly” look like it will work great for my add line button, but will it protect my headings from the delete button?

 

[Gary McMaster]

I don’t know what a GUI is.

Graphical User Interface, the Excel windows, toolbars etc.

will it protect my headings from the delete button

I guess it depends on how you set up the protection. If you use “UserInterfaceOnly”, the only way you'll be able to change/delete protected items is via code and that, hopefully, will be completely under your control.

Gary

 

[beeman]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Gary</st1lace></st1:City>
<o> </o>
I get it now you are saying I should set up a tool bar with prompts in it to populate my sheet. I didn’t consider that.
<o> </o>
I still don’t understand how I could exclude my headings from a delete button using “UserInterfaceOnly”
<o> </o>
Should I be using a different code for the delete button or would it be possible to restrict the macro from deleting specific words.

 

[Gary McMaster]

You'll have to be more specific in exactly what you want. There are all kinds of ways to stop users from doing certain things. For example, you could disable the delete key and remove all the delete buttons from the GUI. That way the only way anything could be deleted is via your macro where you can decide if delete is allowed.

Gary

 

[beeman]

I understand it now
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I keep thinking of doing a stand alone macro instead of a GUI. I will write and set one up and then take it from there.
<o> </o>
Thanks for all your help Gary

--------------------------------------------------------------------------------
I don’t fail. I succeed at finding out what doesn’t work.
<o> </o>
Beeman

 

[Gary McMaster]

Here's a snip for the Workbook Selection Change event that won't let you select row 1 (headers). The theory being "if they can't select it they can't delete/change it". If the user wants to change/delete something in row 1 they must do it via your macro.

Gary

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.EnableEvents = False
If Target.Cells(1).Row = 1 Then
    MsgBox "You may not select row 1"
    Target.Offset(1, 0).Select
End If
Application.EnableEvents = True

End Sub

 

[beeman]

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Gary</st1lace></st1:City><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Great Idea works perfectly<o></o>
<o> </o>
I can use this instead of the password protection on entire columns.<o></o>
<o> </o>
Is there anyway to set this code to stay with a particular row if I insert one in fount of it. For example. I set row 14 to prevent selection my end total row, and insert a row above it. I now want row 15 to prevent selection.<o></o>
<o> </o>
This will save me so much time by not having to do a GUI
<o></o>
--------------------------------------------------------------------------------
I don’t fail at things. I succeed at finding out what doesn’t work.
<o> </o>
Beeman

 

[Gary McMaster]

Here's something similar. This sample uses a named range to track the row of interest. Name the range (entire row) "Test" for this demo.

Note that if there is ever an error in this procedure events will never be re-enabled and it will stop working. If this happens, you can copy/paste "Application.EnableEvents = True" (no quotes) into the immediate (debug) window and press enter to reset it.

You must have the Enable & Disable events in this procedure if you intend to change the selection while you are in the procedure otherwise it causes this procedure to call itself recursively. Under certain conditions this can cause your machine to run out of memory or stack space etc ... major problem.

Gary

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim oIsect As Range
Application.EnableEvents = False

'The following requires the entire row to be a named range called "Test"
Set oIsect = Application.Intersect(Target, ActiveSheet.Range("Test"))
If Not oIsect Is Nothing Then
    MsgBox "Row " & oIsect.Cells(1).Row & " is not selectable"
    oIsect.Offset(1, 0).Select
End If
Application.EnableEvents = True

End Sub