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" <(E-Mail Removed)> wrote in message
news:85BB2519-E669-4183-8F30-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:85BB2519-E669-4183-8F30-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:A4D99B4F-73E7-43FD-A2C2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:85BB2519-E669-4183-8F30-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
> news:A4D99B4F-73E7-43FD-A2C2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:85BB2519-E669-4183-8F30-(E-Mail Removed)...
> >> >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?
> >> >
> >>
> >>
> >>
>
>
>