TSmith
01-08-2009, 12:32 PM
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:
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
Any ideas?
Tim:banghead:
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.
Here is the code:
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
Any ideas?
Tim:banghead:
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.