Excel03 Calendar Control 11.0 Issue with code issue.

[Hootywho]

I am using the following code found here on the board to manipulate the "insert / object / Calendar Control 11.0" into a worksheet. My goal is when the specified cell is selected the calendar pops and user selects the date. Their selection then shows in the referenced cell. This worked great and now the calendar itself is to small to read when it pops. Nor sure what I did to change things. Any help would be appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Calendar1.Visible Then Calendar1.Visible = False
    If Target.Column <> 19 Or Target.Row <> 329 Or Target.Count > 1 Then Exit Sub
    Calendar1.Left = Target.Left + Target.Width
    Calendar1.Top = Target.Top + Target.Height
    Calendar1.Visible = True
    Calendar1.Value = Date
End Sub
Private Sub Calendar1_Click()
    ActiveCell = Calendar1.Value
    Calendar1.Visible = False
    ActiveCell.Select
End Sub

 

[Tom Urtis]

Looks to me like you selected a cell which you want the calendar to be fitted to be the same size as. Why are you doing that in the first place...can you not just keep the calendar control its standard size?

To fix it, try making a column much wider, and a row height much greater, and select that intersecting cell. I'd think the calendar control will respond to your code and resize itself in that scenario.

 

[Hootywho]

Thanks for the response Tom. Making the cell larger did not make a difference. The calendar that pops is still very small. My goal is when cell "S329" is selected, the calendar should pop-up. The user would then select the date and click off to another cell. This causing the calendar to go away and their selected date to be shown in S329.

The code I am using is something I found here that I modified to try and make work for my application.

 

[Hootywho]

Also looks like there may be another calendar behind the one seen???

 

[Tom Urtis]

Could be...seeing as you are only hiding the contro and not deleting it as you go. In my opinion, from a distance without knowing anything about your workbook, I'd show the Calendar control when you need to show it, and delete it after its date has been selected, with the example line...

ActiveSheet.Shapes("Calendar1").Delete


I'm not faulting the author of the code you found, but maybe the situation for which that code was serving was very different than how you are applying it. Again, just thinking out loud about why you are seeing what you're describing.

 

[Hootywho]

Thanks Tom, I understand. Knowing what I am trying to accomplish, can you offer an alternate solution? I believe I should delete what I have and start from scratch on this one.