Calendar

[JohnUK]

Hi,
I have a cell that when you enter, it brings up a calendar, which works fine
apart from when I want to delete that particular cell and others at the same
time using a macro, the calendar comes up when I don’t need it. Is there
something I can enter into the code that would temporarily disable the
calendar so that the cell can be deleted?
Many thanks
John

 

[Chip Pearson]

It should not display the Calendar object when the row is deleted unless you
Select the range or row before doing the delete. For example, to NOT do

Range("3:3").Select
Selection.Delete

Instead, reference the range directly without Select:

Range("3:3").Delete

Note, also, that deleting the row does not delete the calendar object. You
might want to post the code you are using and indentify the line(s) that
have the problem.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JohnUK]

Hi Chip,
Thank you for your help.
The calendar is activated on H6 and used the following code to delete cells,
but the Calendar keeps coming up:

Range("H6,J6,L6,N6,P6:R6,T6,V6").Select
Selection.ClearContents

I have also tried
Range("H6,J6,L6,N6,P6:R6,T6,V6").Delete

And still it comes up.
Any other ideas?
Regards
John

 

[Dave Peterson]

I'm guessing that the code that brings up the calendar is a worksheet_selection
change event.

And you're selecting a range and that fires the event.

You could use:

application.enableevents = false
Range("H6,J6,L6,N6,P6:R6,T6,V6").Select
application.enableevents = true
Selection.ClearContents

or just

Range("H6,J6,L6,N6,P6:R6,T6,V6").clearcontents

to avoid selecting anything in the first place.

If you have other code that fires (like a worksheet_change event), you may want
to stop that from firing, too.

application.enableevents = false
Range("H6,J6,L6,N6,P6:R6,T6,V6").clearcontents
application.enableevents = true

 

[JohnUK]

Many thanks Dave, they both worked.
Much appreciate the help from both you and Chip
Best Regards
John