Sleeper: Calendar Object Destination Cell Formatting

[TriMacro]

Can anyone explain to me why the formatting of a destination cell for a calendar object won't always work? It seems there are times when I can change the formatting for the specified cell to read as I need it to (i.e. November 18, 2004) but most of the time it will only allow the default (11/18/2004). It's driving me crazy! I've tried adjusting the cell size, deleting any formatting, changing the format in the calendar properties and nothing seems to work. Is this one of those Excel quirks or am I once again missing the obvious?

[Zack Barresse]

Hi,

Are these on seperate computers? If so, do both have the Reference to the Calendar Control?

[TriMacro]

Same computer. I had originally had the control in the worksheet with the proper formatting and then deleted it. Upon adding it back, I couldn't get it back to the proper formatting.

[lindon]

Weird! Cruddy control.

If you explicitly write to a cell (eg on a click event), the problem goes away. Don't use the LinkedCell thingummy is my tip.

Private Sub Calendar1_Click()
  Dim myDate
  myDate = Calendar1.Value
  Range("A1").Value = myDate
End Sub

Lindon

[Jacob Hilderbrand]

Adding a bit to lindon's code. You can also format the value in the code as you like:

Private Sub Calendar1_Click()  
Range("A1").Value = Format(Calendar1.Value, "mm/dd/yyyy") 
End Sub

[TriMacro]

That is the format it is using that i don't want. I want the format to be November 18, 2004 but no matter what I do to the code or the cell, I get 11/18/2004.

[Jacob Hilderbrand]

Try this:

Range("A1").Value = Format(Calendar1.Value, "mmmm dd, yyyy")

[Zack Barresse]

Maybe as an additional, if that doesn't work, you might try Jacob's suggestion after the fact (I'm not too familiar w/ the Calendar Control)..

[vba]
Range("A1").Numberformat = "mmmm dd, yyyy"
[/vba]

[TriMacro]

Neither worked. This baffles me! Even when I try to format the cell the display never varies from the mm/dd/yyyy format - despite the formatting that I choose. I've tried different cells but get the same result. What doesn't make any sense is that it's worked perfectly before.

[Jacob Hilderbrand]

Can you post an attachment for us to test?