Calendar control with merged cells

[Guest]

I am using the calendar code
From Ron de Bruin's site:
http://www.rondebruin.nl/calendar.htm

Unfortunately, my form is a copy of a paper form and therefore has many
merged cells. The calendar control continues to show unless an unmerged cell
is selected after selecting the date. Is there a way to programmatically
select a none merged cell to make the calendar invisible before closing?

 

[Tom Ogilvy]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Goto Worksheets("Sheet1").Range("A1"), True
thisworkbook.Save
End Sub

this has the obvious disadvantage of forcing the workbook to be saved.

(if you don't save the workbook after changing the selection, there isn't
much point in doing it I wouldn't think)


this assumes A1 of Sheet1 is not merged - adjust to suit your needs.

This code would go in the Thisworkbook module

http://www.cpearson.com/excel/events.htm for an overview of events if you
are not familiar.

 

[Guest]

Not sure what happened, but now the calendar won't pop-up, it just inserts
current date in the cell. So I deleted your code, and the calendar still does
not pop-up.

 

[Tom Ogilvy]

Change it to goto to a cell outside the range where you are popping up the
calendar control, but on the same page.

There is nothing in my code that would stop Rons code from working.
Perhaps you have some other code that has disabled events.

In a general module, try running code like this

Sub ResetEvents()
Application.EnableEvents = True
End sub

 

[Guest]

Did something wrong, crashed the whole worksheet. Luckily had a recent
backup. I ended up using http://www.fontstuff.com/vba/vbatut07.htm code and
calling that on cell entry. It didn't seem to matter that the cells are
merged. So as soon as I select a date, the calendar closes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("R3:T3"), Target) Is Nothing Then
Call OpenCalendar
End If
End Sub