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.

 

[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>
This is exactly what I am looking for. It works great
<o> </o>
So If I want to include several name ranges can I do it with comma’s ie
<o> </o>
(“test”,”test2”,”test3”)
<o> </o>
Or do I need a separate code for each one
<o> </o>
The Enable & Disable events in this procedure you are talking about is already written into the code right? Application.EnableEvents = True. I know silly question but I want to make sure I understand.<o></o>
<o> </o>
If the error does happen the "Application.EnableEvents = True” is inputted at the top of this code again. Im not sure what you mean by the immediate (debug) window <o></o>
<o> </o>
--------------------------------------------------------------------------------
I don’t fail at things. I succeed at finding out what doesn’t work.
<o> </o>
Beeman

 

[Gary McMaster]

So If I want to include several name ranges can I do it with comma’s ie

The Intersect method takes (I believe) up to 30 arguments. You could possibly have something like this:

Set oIsect = Application.Intersect(Target, ActiveSheet.Range("Test"), ActiveSheet.Range("Test2"), ActiveSheet.Range("Test3"))

The above (oIsect) would return a range of cells that are common to the selection and all the listed ranges. Depending on what you plan to do with the results it could get very complicated. Maybe some ranges are rows and others are columns etc.

You could test for intersections individually like this:

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

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

'The following requires the entire row to be a named range called "Test3"
Set oIsect = Application.Intersect(Target, ActiveSheet.Range("Test3"))
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

The above sample would allow you more flexibility to take action depending on which range(s) intersect. Maybe on one you want to display a message box and on another just move the cursor (activecell) to A1

The Enable & Disable events in this procedure you are talking about is already written into the code right?

Yes

Im not sure what you mean by the immediate (debug) window

Many people call it the "Debug" window. In the VBA IDE main menu, select: View > Immediate Window. In your main code you can put statements like "Debug.Print MyVariable" (no quotes) and the value of your variable(s) will be printed to the "debug" window as your code is running. It is very helpful during development when things are not going as expected. You can see if your variables contain the values you expect. In this window you can also execute single lines of code. If you paste "Application.EnableEvents = True" (no quotes) into the "debug" window and make sure the cursor is still on the same line before you press enter, it will execute that line of code and turn the events back on. You could also simply add a new procedure to one of your modules named "Reset", for example, and put "Application.EnableEvents = True" (no quotes) in there and just run it like any other sub if needed.

Gary

 

[beeman]

Thanks for your help Gary

I will play around with it and see if I can get things working

 

[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>
I have been working with the code that you gave me and I cant seem to get the macro to turn on.
<o> </o>
I named my ranges test ($A$1:$D$4), test2 ($A$38:$D$41), test3 ($A$27:$$D34)
<o> </o>
These are my steps
<o> </o>
I open vba, click on insert and module fill in the code and then close VBA
<o> </o>
When I select the cell excel will let me do it when Im not suppose to be able to

do you have any idea what i am doing wrong?
<o> </o>
--------------------------------------------------------------------------------
I don’t fail at things. I succeed at finding out what doesn’t work.

Beeman

 

[Gary McMaster]

The code doesn't go in a module it goes in the "Workbook_SheetSelectionChange" event.

Double click on "ThisWorkbook" in the VBA project explorer and paste the code in there.

Gary

 

[beeman]

Thanks Garry<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I did that now I get an error “Run Time Error 1004 Application defined or object defined error”<o></o>
<o> </o>
I typed in the Application.EnableEvents = True into the debug window but it will still not turn on the code<o></o>
<o> </o>
--------------------------------------------------------------------------------
I don’t fail at things. I succeed at finding out what doesn’t work.

Beeman

 

[Gary McMaster]

Can you post all of the code you have in "Workbook_SheetSelectionChange" event, top to bottom?

Gary

 

[beeman]

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:City w:st="on">Gary</st1:City></st1lace>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I Cut and passed in the code almost exactly as you sent me. I took out the “” around the test range names. To make things work better I used test as the range names. The code cased an error the first time I tried it.
<o> </o>
But its ok that its not working.. I thought up a work around.
I put in a macro on the insert line and delete line that turns off and on the password protection see below, but when I initially go to protect the sheet I unchecked the “select locked cells”. This prevents the user from selecting any of my headings, total, or formulas and will also prevent my delete button from erasing all my stuff.
<o> </o>
The solution was so simple, I cant believe I didn’t think of it before.
<o> </o>
Thanks for all of your help Gary
<o> </o>
This is the code I tried using
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)<o></o>
<o> </o>
Dim oIsect As Range<o></o>
Application.EnableEvents = False<o></o>
<o> </o>
'The following requires the entire row to be a named range called "Test"<o></o>
Set oIsect = Application.Intersect(Target, ActiveSheet.Range(Test))<o></o>
If Not oIsect Is Nothing Then<o></o>
MsgBox "Row " & oIsect.Cells(1).Row & " is not selectable"<o></o>
oIsect.Offset(1, 0).Select<o></o>
End If<o></o>
<o> </o>
'The following requires the entire row to be a named range called "Test2"<o></o>
Set oIsect = Application.Intersect(Target, ActiveSheet.Range(Test2))<o></o>
If Not oIsect Is Nothing Then<o></o>
MsgBox "Row " & oIsect.Cells(1).Row & " is not selectable"<o></o>
oIsect.Offset(1, 0).Select<o></o>
End If<o></o>
<o> </o>
'The following requires the entire row to be a named range called "Test3"<o></o>
Set oIsect = Application.Intersect(Target, ActiveSheet.Range(Test3))<o></o>
If Not oIsect Is Nothing Then<o></o>
MsgBox "Row " & oIsect.Cells(1).Row & " is not selectable"<o></o>
oIsect.Offset(1, 0).Select<o></o>
End If<o></o>
<o> </o>
Application.EnableEvents = True<o></o>
<o> </o>
End Sub<o></o>

<o> </o>
<o> </o>
This is the code for the add line and delete line

Private Sub CommandButton4_Click()
'make new row<o></o>
Sheets(“Sheet 1”) .unprotect “password”
ActiveCell.EntireRow.Insert shift:=xlDown
'copy the row
ActiveCell.Offset(1).EntireRow.Copy Cells(ActiveCell.Row, 1)
On Error Resume Next<o></o>
Sheets(“Sheet 1”).protect “password”
End Sub

And another to delete a line

Private Sub CommandButton3_Click()
'to delet row<o></o>
Sheets(“Sheet 1”) .unprotect “password”
ActiveCell.EntireRow.Delete<o></o>
Sheets(“Sheet 1”).protect “password”
End Sub

--------------------------------------------------------------------------------
I don’t fail at things. I succeed at finding out what doesn’t work.

Beeman <o></o>

 

[Gary McMaster]

Glad you got something working.

I took out the “” around the test range names.

For the record:

Those quotes are needed unless you intend them to be variables named Test, Test2 & Test3 and initialize them to the actual names of the named ranges. The quotes tell VBA that this is not a variable but rather literal text. If you use "Option Explicit" VBA will tell you that there is a problem with a variable name and that probably would have alerted you to the fact that it wasn't supposed to be a variable.

If you add some code: debug.print Test, debug.print Test2, debug.print Test3 you will probably see that those variables = nothing but should contain the valid names of existing named ranges. Thus the error, nothing (or empty string) is not a legal range name.

Gary

 

[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>
That’s great I was wondering why it wasn’t working. Even though I didn’t end up using this code for this project I can see lots of applications for other sheets I work with.
<o> </o>
Where did you learn the VBA language? Could you recommend a book or course?
--------------------------------------------------------------------------------
I don’t fail at things. I succeed at finding out what doesn’t work.

Beeman <o></o>