Excel VBA problems with locked sheet and macros

Question

I have a strange issue, I have a sheet in excel 2013 which uses the doubleclick to select a cell( which has some text in it that I want locked so the user does not change), change the text colour to highlight the chosen cell and then put a value based on the target.column in a cell further along the row.

It works fine if I unlock the sheet but all attempts to lock the sheet end up with a error in the value of "Target.row" I put the msg statement in and I can see that at the end of each call the active cell then moves to the next unlocked cell and wont then move/update the values when you next click on a wanted cell to highlight.

I have tried all sorts of fixes for this and made many code changes. Currently the code is:

Private Sub Workbook_Open()
' protect worksheets but allow macros
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="Purple15", userinterfaceonly:=True
Next ws

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Sheet3.Protect Password:="Purple15", UserInterFaceOnly:=True
Dim i As Integer
Dim d As Integer

MsgBox " row is " & Target.Row & " col is " & Target.Column

'Cells(Target.Row, 1).Value = "S"
For d = 1 To 1000
Next d
' above just slows it down to see effect

Sheet3.Unprotect Password:="Purple15"
'Cells(Target.Row, 1).Value = "U"

' check if double click is in the range cols 9 to 13 as these are the only ones they should choose
' if so then set font colour to red for the chosen cell and put  chosen col number in col 16 for the sheet to then pick up from
'Cells(5, 1).Value = "U"
If Target.Column >= 9 And Target.Column <= 13 Then
   'Cells(Target.Row, 1).Value = 2
    Cells(Target.Row, 9).Font.Color = vbBlack
    Cells(Target.Row, 10).Font.Color = vbBlack
    Cells(Target.Row, 11).Font.Color = vbBlack
    Cells(Target.Row, 12).Font.Color = vbBlack
    Cells(Target.Row, 13).Font.Color = vbBlack
    Target.Font.Color = vbRed
    Cells(Target.Row, 16) = (Target.Column)

' also check if the double click is in the reset box, if so then reset all the values in col 16 to the starting condition of 9

ElseIf Target.Column = 2 And Target.Row = 3 Then
    'Cells(3, 1).Value = 3
    For i = 8 To 300
        Cells(i, 1).Value = " "
        Cells(i, 9).Font.Color = vbBlack
        Cells(i, 10).Font.Color = vbBlack
        Cells(i, 11).Font.Color = vbBlack
        Cells(i, 12).Font.Color = vbBlack
        Cells(i, 13).Font.Color = vbBlack
        If Cells(i, 16).Value >= 10 Then Cells(i, 16).Value = 9
    Next i
Else

End If
'Cells(5, 1).Value = " "
'Cancel = True

Sheet3.Protect Password:="Purple15", userinterfaceonly:=True

'Cells(5, 1).Value = "L"
End Sub

Not clear what you're asking. The code posted doesn't do anything with Target.Row after you protect it. Is the code you posted the code that generates the error, and if so, what is the error and what line? — Comintern

Rhino Rhino · Accepted Answer · 2015-04-10T15:35:17

You may want to look into using buttons. That way you don't have to worry about end-users changing values (as easily). I created a spreadsheet with only one row and "SomeText 1" in column A, "SomeText 2" in column B and "SomeText 3" in column C. Below is the code and I think it is working as you expect.

In "ThisWorkbook" I used your exact code:

Private Sub Workbook_Open()
   Dim ws As Worksheet

   For Each ws In ThisWorkbook.Worksheets
      ws.Protect Password:="Purple15", userinterfaceonly:=True
   Next ws
End Sub

In "Sheet1" I used this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column >= 1 And Target.Column <= 3 Then
        'Reset all of the "Buttons" to black
        Cells(Target.Row, 1).Font.Color = vbBlack
        Cells(Target.Row, 2).Font.Color = vbBlack
        Cells(Target.Row, 3).Font.Color = vbBlack
        'Makes the one they clicked red
        Target.Font.Color = vbRed
        'Change focus so you don't get the "Protected Worksheet"
        ' warning after a double-click
        Cells(Target.Row, 6).Select
        'Unlock the cell you're editing
        Cells(Target.Row, 6).Locked = False
        'Set the cell's value
        Cells(Target.Row, 6) = (Target.Column)
        'Lock it again
        Cells(Target.Row, 6).Locked = True
    End If
End Sub

Is that what you are looking for?

Excel VBA problems with locked sheet and macros

1 Answers