Search code examples
excelvba

Protected Cell warning when returning to original cell after DoubleClick

Not really critical because there are ways around it but curious as to why it happens? Tested this on a brand new WB to be sure it wasn't something else buried in my code causing this. I have a workbook with a protected sheet where I'm calling up a UserForm and using a Job# in one of the cells as a reference to work with. Upon exiting the UserForm, I want to leave the user with that same cell activated. Seems simple enough. Using the DblClick event anywhere other than the Job# column it works perfectly but if I DblCLicked on the cell that I wanted to return to, I get the protection warning.

To duplicate:

In a standard Module

Public ActRow As Long

Open Event

Private Sub Workbook_Open()
Sheets("Sheet1").Protect userinterfaceonly:=True
End Sub

UserForm Code:

Private Sub CommandButton1_Click()
    Unload Me
    ' Doesn't matter whether the Unload is before or after
    Range("B" & ActRow).Activate
End Sub

Sheet1 Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ActRow = ActiveCell.Row
    UserForm1.Show
End Sub

With the above code, if I double-click anywhere other than Col "B" on Sheet1, the UserForm will open up with nothing other than CommandButton1. If I press the Button I will be return to Sheet1 with the cell in Col "B" of the original row selected. No warning messages. If I double-clicked a cell in Col "B" though, when I press the Button, I'm returned to Sheet1 and my original cell is activated but I get the 'Protected Sheet' warning message. Why? Application.DisplayAlerts doesn't cure it nor does Unlocking the cell beforehand. I'm left with activating the row below and then moving up a row to eliminate the warning message but that's just masking the problem.

Any Ideas? Thanks, John

Solution

  • If you run your code without protecting the sheet, you will see double clicking on a cell in Column B will start an edit in that cell. With the sheet protected, editing is not allowed, hence the error.

    To avoid this, update your code to this

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    ActRow = ActiveCell.Row
    UserForm1.Show
End Sub