union range

[Atishoo]

Im trying to get a callendar to appear whenever the active cell falls within
a series of large pre defined ranges reffered to as date1, date2 etc up to
date9
I keep getting error on this code i know im missing something really dumb
can anyone help?

With ActiveSheet.Calendar1
Set bigdaterange = Application.Union(Range("date1"), Range("date2"),
Range("date3"), Range("date4"), Range("date5"), Range("date6"),
Range("date7"), Range("date8"), Range("date9"))
If Not Intersect(Target, Range("bigdaterange")) Is Nothing Then
.Visible = True
.Top = Target.Top + Target.Cells.Height
.Left = Target.Left
Else

 

[Norman Jones]

Hi Atishoo,

BigDateRange is an object variable, not
a named range. so try removing the
surrounding quotes.

Try something like:

'========>>
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim BigDateRange As Range

With Me.Calendar1
Set BigDateRange = Application.Union( _
Range("date1"), _
Range("date2"), _
Range("date3"), _
Range("date4"), _
Range("date5"), _
Range("date6"), _
Range("date7"), _
Range("date8"), _
Range("date9"))
If Not Intersect(Target, BigDateRange) Is Nothing Then
.Visible = True
.Top = Target.Top + Target.Cells.Height
.Left = Target.Left
Else
.Visible = False
End If
End With

End Sub
'<<========

 

[Atishoo]

Oh yeees its so good when something works!!
thankyou very much that really hits the spot!!
works like a dream
thanks john
ps what does the command "Dim" actually stand for??

 

[Bob Phillips]

Dimension, it declares a variable, putting As ... declares the data type.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Atishoo]

ahhh now it all makes sense! (well some of it does)

 

[Norman Jones]

Hi Atishoo,

You should always use the Option Explicit
statement at the top of your code modules
and always explicitly declare all variables.

See Chip Pearson's suggestions at:

Using Variables (Properly) In VBA
http://www.cpearson.com/excel/variables.htm