Solved: Union Range Error for Pop Up Calendar

[TSmith]

Here is the problem: I have a worksheet with 84 "cards" on it for the user to fill out. Each card has a date line with a popup calendar object. I created named ranges for all of the date cells (had to use multiple ranges since one range is well over the 255 character limit) and then use a union to create one overall range from the sub ranges. I get the Method Range of Object Worksheet Failed message every time I run this. The error occurs on the "If Not Application.Intersect". I have watched the "BigCalRange" object and it shows as a range containing 84 objects.

Here is the code:

[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'When any cell on the worksheet is selected this routine hides the calendar

Sheets("Trip Cards 1").Activate

'Create multiple ranges to cover all of the date cells on "Trip Cards 1" sheet
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
Dim C4 As Range
Dim C5 As Range
Dim C6 As Range
Dim C7 As Range
Dim C8 As Range
Dim C9 As Range
Dim C10 As Range

'Create the master range
Dim BigCalRange As Range

'Set each sub range to the named range on the worksheet
Set C1 = Range("Cal1")
Set C2 = Range("Cal2")
Set C3 = Range("Cal3")
Set C4 = Range("Cal4")
Set C5 = Range("Cal5")
Set C6 = Range("Cal6")
Set C7 = Range("Cal7")
Set C8 = Range("Cal8")
Set C9 = Range("Cal9")
Set C10 = Range("Cal10")

'Union the sub ranges into the master range
Set BigCalRange = Union(C1, C2, C3, C4, C5, C6, C7, C8, C9, C10)

'Show the calendar if the date cell is selected.
If Not Application.Intersect(Range("BigCalRange"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True

'set the calendar date to today
Calendar1.Value = Date

ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If

End Sub
[/VBA]
Any ideas?

Tim

Edit Lucas: vba tags added to code. If you select your code when posting and hit the vba button your code will be formatted for the forum.

[lucas]

When I see popup calendars posted I always refer the poster to this thread. It's worth the read. Post #7 and down are the most relevant:

http://www.vbaexpress.com/forum/showthread.php?t=10914

[Kenneth Hobs]

Posting a samle xls would make it easier to help.

Not sure why you are using Activate.

You probably need to make sure that Selection.Count=1 too. Be sure to turn off events, do your thing, and then turn it back on.

[TSmith]

Original Problem:
Here is the problem: I have a worksheet with 84 "cards" on it for the user to fill out. Each card has a date line with a popup calendar object. I created named ranges for all of the date cells (had to use multiple ranges since one range is well over the 255 character limit) and then use a union to create one overall range from the sub ranges. I get the Method Range of Object Worksheet Failed message every time I run this. The error occurs on the "If Not Application.Intersect". I have watched the "BigCalRange" object and it shows as a range containing 84 objects.

Here is the code:

VBA:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'When any cell on the worksheet is selected this routine hides the calendar Sheets("Trip Cards 1").Activate 'Create multiple ranges to cover all of the date cells on "Trip Cards 1" sheet Dim C1 As Range Dim C2 As Range Dim C3 As Range Dim C4 As Range Dim C5 As Range Dim C6 As Range Dim C7 As Range Dim C8 As Range Dim C9 As Range Dim C10 As Range 'Create the master range Dim BigCalRange As Range 'Set each sub range to the named range on the worksheet Set C1 = Range("Cal1") Set C2 = Range("Cal2") Set C3 = Range("Cal3") Set C4 = Range("Cal4") Set C5 = Range("Cal5") Set C6 = Range("Cal6") Set C7 = Range("Cal7") Set C8 = Range("Cal8") Set C9 = Range("Cal9") Set C10 = Range("Cal10") 'Union the sub ranges into the master range Set BigCalRange = Union(C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) 'Show the calendar if the date cell is selected. If Not Application.Intersect(Range("BigCalRange"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True 'set the calendar date to today Calendar1.Value = Date ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub

VBA tags courtesy of www.thecodenet.com

Any ideas?

Tim

Since the code would not recognize the union range object, I changed the range to include each of the named calendar ranges:


If Not Application.Intersect (Range("C1, C2, C3, C4, C5, C6, C7, C8, C9), Target) Is Nothing Then

An now it works for all 80 calendars.

[lucas]

Tim, I merged the thread where you declared victory to the thread where you actually asked the question.

How did you think we would know what you were talking about? I had to look up your profile and find all the threads you had posted and research till I found what you were commenting about so, do you think others would go to that much trouble?

I know you only have a few posts but please read our faq. When you post code, select it and hit the vba button to format it for the forum.

Just to be sure you understand, mark the thread solved where you ask the question so others will not think you still need help and waste time reading through it just to find that it has been solved.

Thanks for your understanding and cooperation.