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?

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.

--
Regards,
Tom Ogilvy




"copteral" wrote in message
...
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?

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" wrote:

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.

--
Regards,
Tom Ogilvy




"copteral" wrote in message
...
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?

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

--
Regards,
Tom Ogilvy


"copteral" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy




"copteral" wrote in message
...
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?

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"copteral" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy




"copteral" wrote in message
...
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?