Excel VBA Calling the Calendar via Command Button in a form

I have just added in Calendar 12.0 from the tools > Additional Controls. Calendar works fine and I have it spitting the date out to the right cells. What I would like, however, is to really make the calendar visible from a command button as my form contains a bunch of fields and I don'开发者_如何学编程t want to bog up the form with this calendar. I have tried Calendar1.show but the .show isn't an option.

So ultimately I need a command button to show the calendar, allow the user to select (I have that) and then close the calendar. Any help? I thank you in advance!!

bdubb

In this snippet, CommandButton1 is from the ActiveX controls, not the form controls. It requires that you click the button to show the calendar (which pops up next to the button you clicked), and click the button again to hide the calendar.

Private Sub CommandButton1_Click()

If Not Calendar1.Visible Then
    Calendar1.LinkedCell = "A1"
    Calendar1.Top = Sheet1.CommandButton1.Top
    Calendar1.Left = Sheet1.CommandButton1.Left + Sheet1.CommandButton1.Width + 1
    Calendar1.Visible = True
Else
    Calendar1.Visible = False
End If

End Sub

Obviously, different buttons would require different linked cells, but it does mean that you could have a single calendar control that it displyed by multiple buttons (if that is what you want).

Unfortunately, it would appear that you cannot hide the control while any of its events are firing (e.g AfterUpdate). It just doesn't want to disappear!!

Hide/Close a calendar control still not works (new year 2015 = almost four years later) but I think I found a workaround to hide the control after firing events.

I have a Calendar1_AfterUpdate(), which launches before Calendar1_Click(). Code is placed directly in a worksheet and NOT in a module.

Private Sub Calendar1_AfterUpdate()
  Range("a1") = Me.Calendar1.Value
  ' Next two lines does not work within AfterUpdate
  ' When running step by step it seems to work but the control is
  ' visible when End Sub has run
  Me.Calendar1.Visible = True
  Me.Calendar1.Visible = False
End Sub

To that I simply added

Private Sub Calendar1_Click()
  Me.Calendar1.Visible = True
  Me.Calendar1.Visible = False
End Sub

Please note that the control for some reason must be made visible before hiding.

Why it does not work directly in Calendar1_AfterUpdate() is a mystery to me.

Next problem is to hide the control when I remove the mouse. Mouse-events seems to be impossible in a calendar control ...