call macro by double clicking on cell

[hajiali]

End If
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B1")) Is Nothing Then
        Call PrintEmployeeList
        End If
    End If
End Sub

the code above is in the Sheet code along with other double click functions i have for the sheet.

the code below is in the module

Sub PrintEmployeeList()
ActiveSheet.Unprotect Password:="TEST"
Dim lastrow As Long
    Sheets("EMPLOYEE LIST").Copy Before:=Sheets(1)
    Sheets("EMPLOYEE LIST (2)").Select
    Columns("G:I").EntireColumn.Hidden = True
    Columns("M:M").ClearContents
    Range("B2:K1000").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.PageSetup.Orientation = xlPortrait
    lastrow = Cells.SpecialCells(xlLastCell).Row
    lastrow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & lastrow
       ActiveSheet.PageSetup.LeftMargin = _
    Application.InchesToPoints(1.5)
    ActiveSheet.PageSetup.TopMargin = _
    Application.InchesToPoints(1)
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Application.DisplayAlerts = False
    Sheets("EMPLOYEE LIST (2)").Delete
    Application.DisplayAlerts = True
    Sheets("EMPLOYEE LIST").Select
    ActiveSheet.Protect Password:="TEST"
End Sub

when double click on B1 it runs the macro great except after the completion it comes up with the error " The cell or chart you're trying change is on a protected sheet......" i can simply unlock cell B1 but to avoid the message however, I prefer if i kept it locked while protected.

when i tried just putting the code with the rest of the double click function I get an error on line

 Range("B2:K1000").Select

not sure how else to go about it.

any suggestions.

 

[6StringJazzer]

To avoid the error, you need to unprotect the sheet in your code, then re-protect when you are done. You seem to be aware of this because you are unprotecting the sheet (well, some sheet) at the beginning of your code. However, you are using ActiveSheet. (I recommend that all sheets be referenced explicitly rather than using ActiveSheet, except in the case where you are writing a general-purpose macro that could be invoked while any sheet is active. That is not the case here.) It appears that you are unprotecting the sheet where you double-click and then protecting sheet EMPLOYEE LIST at the end of the code. That looks like an error to me, and suggests there are other similar errors.

I would rewrite this code to explicitly refer to the sheets intended rather than selecting sheets and then depending on it to be ActiveSheet somewhere else in the code. I would offer to do it for you but without knowing what you are actually trying to do and what sheets you want to do it on, I can't tell what part of your code is correct and what part might be an error.

(Also regarding your thread title, although you certainly are double-clicking, that is unrelated to the error you are getting, and you have not shown us Worksheet_BeforeDoubleclick, so the title is a bit misleading.)

 

[hajiali]

Thanks 6StringJazzer that worked. much appreciated it.