union range
 

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

union range
 

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
'<<========



---
Regards.
Norman


"Atishoo" wrote in message
...
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

union range
 

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??

"Norman Jones" wrote:

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
'<<========



---
Regards.
Norman


"Atishoo" wrote in message
...
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

union range
 

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" wrote in message
...
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??

"Norman Jones" wrote:

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
'<<========



---
Regards.
Norman


"Atishoo" wrote in message
...
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

union range
 

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

"Bob Phillips" wrote:

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" wrote in message
...
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??

"Norman Jones" wrote:

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
'<<========



---
Regards.
Norman


"Atishoo" wrote in message
...
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

union range
 

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





---
Regards.
Norman
"Atishoo" wrote in message
...
ahhh now it all makes sense! (well some of it does)

"Bob Phillips" wrote:

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" wrote in message
...
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??