josephm
06-25-2008, 05:30 AM
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
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