Excel VBA Calling the Calendar via Command Button in a form

Question

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

Duncan Howe · Accepted Answer · 2011-05-09 21:30:36Z

1

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!!

edited May 9, 2011 at 21:30

answered May 9, 2011 at 21:05

Duncan Howe

3,03521 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bdubb Over a year ago

Thanks for the response but I now see using the DTPicker is the way to go as it keeps the control in a nice size and expands to the calendar view when clicked. Appreciate the help though

Tryphon · Accepted Answer · 2014-12-30 12:02:37Z

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 ...

Collectives™ on Stack Overflow

Excel VBA Calling the Calendar via Command Button in a form

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related