Thread: Allow Formatting of a single Cell?

I apologize ahead of time if this has been solved else where and would be grateful if someone could try and help out.

What I have been asked to do is to create a time/schedule chart for the company I work for.

I added code I found for a calendar/date choosing object. Now all works fine when I choose the date before protecting the sheet. After protecting I receive error 1004. I have realized after a little bit of searching that the cell format is being altered by the object.

What I would like to do now is to be able to allow formatting for that one single cell where the calendar object is used. I do now want to allow format changes to any other part of the entire document. I have tried a few items and still no luck. I appreciate the help.

Here is the code for the object.

Code:

Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "mmmm dd"
    ActiveCell.Select
    Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("C5"), Target) Is Nothing Then
        Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
        Calendar1.Top = Target.Top + Target.Height
        Calendar1.Visible = True
        ' select Today's date in the Calendar
        Calendar1.Value = Date
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
    End If
End Sub

Unprotect the cell.

Or protect the sheet with the AllowFormattingcells argument set to True to allow formatting cells.

Or protect the worksheet with the UserInterfaceOnly argument set to True to enable VBA to manipulate the sheet.


Help is your friend. See Help in the VBE for the Protect method