formatting vba to work on a protected sheet.

[josephm]

hi guys

i have a pop up calendar and some code that changes the cells contents to a specified color when double clicked, that works when the sheet is not protected.
i have a workbook (i have attached) containing one sheet. on this sheet rows 4 and 5 contain month and week headings which i want to protect from being deleted by accident or changed at all
when i use excel's protection utility the pop up calendar and the change color vb code stop working.

how do i modify the code so that it still works when the sheet is protected?

i have frozen columns A and B but i also wanted to do for rows 1 and 2 but i read somewhere that that is not possible in excel 2003, is that true?

the code that allows the color change is show below...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Font.ColorIndex = 10 Then
Target.Font.ColorIndex = 3
Cancel = True
Exit Sub
End If
If Target.Font.ColorIndex = 3 Then
Target.Font.ColorIndex = xlAutomatic
Cancel = True
Exit Sub
End If
Target.Font.ColorIndex = 10
Cancel = True
End Sub

the code that runs the pop up calendar is shown below....

Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
' Assign shortcut to display calendar on SHIFT+CTRL+C
Application.OnKey "+^{C}", "Module1.OpenCalendar"
' Add item to shortcut menu on open
On Error Resume Next
Application.CommandBars("Cell").Controls("Insert Date").Delete
On Error GoTo 0
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Delete item from shortcut menu on close
On Error Resume Next
Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

[Bob Phillips]

You unprotect the sheet at the start, do your stuff, then protect the sheet again.

[greymalkin]

just use


Application.ScreenUpdating = False

Sheets("SheetName").UnProtect (password if needed)
<run your code>
Sheets("SheetName").Protect (password if needed)

Application.ScreenUpdating = True


They'll never know what happened.

[josephm]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False

Sheets(VACATION_TABLE).Unprotect ()

If Target.Font.ColorIndex = 10 Then
Target.Font.ColorIndex = 3
Cancel = True
Exit Sub
End If
If Target.Font.ColorIndex = 3 Then
Target.Font.ColorIndex = xlAutomatic
Cancel = True
Exit Sub
End If
Target.Font.ColorIndex = 10
Cancel = True
Sheets(VACATION_TABLE).Protect ()

Application.ScreenUpdating = True

End Sub

i didnt specify a password when prtecting the sheet to test it. then i used the above code but i got a debuging err what did i do wrong?

At first i had the sheets name as VACATION TABLE but i figured vb wont like the space between the two words in the name so i put an _

[Bob Phillips]

You don't need the brackets after Unprotect and Protect.

[greymalkin]

If you hit the debug button it should take you right to the line where the error(s) are. You will need to use this in the future to help find the cause of the issue.

I see 3 issues at first glance:
1 - Sheet names should be wrapped in double quotes and can contain spaces:

Sheets("VACATION_TABLE")

When you don't use a space it thinks it's a string variable and since it's not it will fail.

2 & 3 - The protect.unprotect doesn't require "()". You are probably used to doing this in other programming languages, not so with good ol' VB :).
So the full line should look like:
Sheets("VACATION_TABLE").Unprotect
AND
Sheets("VACATION_TABLE").Protect

hope this helps