userform problem

Hi!
I have this code in my calendar userform and I want the range into which the date is allowed to be placed ("Dates") to expand to include other ranges some of which are on other sheets. How can I do this?

<start code>
Option Explicit
Private Sub Calendar1_Click()
If Not Intersect(ActiveCell, Range("Dates")) Is Nothing Then
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Value = Calendar1.Value
End If
End Sub
<end code>

Thanks for any help or guidance!!

Brian,

If it's all on one sheet you can define a range by using Union. If it spans
multiple sheets (as it sounds like) more extravigant measures are needed.
How many sheets are you looking at?

Mike

"Brian Matlack" wrote:

>
> Hi!
> I have this code in my calendar userform and I want the range into
> which the date is allowed to be placed ("Dates") to expand to include
> other ranges some of which are on other sheets. How can I do this?
>
> <start code>
> Option Explicit
> Private Sub Calendar1_Click()
> If Not Intersect(ActiveCell, Range("Dates")) Is Nothing Then
> ActiveCell.NumberFormat = "mm/dd/yy"
> ActiveCell.Value = Calendar1.Value
> End If
> End Sub
> <end code>
>
> Thanks for any help or guidance!!
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=560028
>
>

Mike:
There are 5 sheets in all that I wish the userform to work on

Thanks for the response

Brian,

I'm unclear as to what you are trying to do in your subsequent code. What
is Calendar1? I thought it was the name of your userform, but then you have
Calendar1.Value, which doesn't make sense.

Mike

"Brian Matlack" wrote:

>
> Mike:
> There are 5 sheets in all that I wish the userform to work on
>
> Thanks for the response
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=560028
>
>

Brian,

My apologies. Didn't realize until now you probably have "Microsoft
Calendar Control 11.0" referenced and are using a calendar control.

To accomplish what you want you need to know which sheet you are on. In the
following code I use Select Case to include variable ranges on multiple
sheets. If you want specific cells on each sheet you will need to create a
union of cells for each sheet.

In the following code, I only allow dates on Sheet 1 in columns A, B, & C;
on sheet 2 in columns D, E, & F; etc. You need to change these ranges and
add Cases for Sheet4 and Sheet5.

Option Explicit

Private Sub Calendar1_Click()
Dim gocal As Boolean
gocal = False
Select Case ActiveCell.Parent.Name
Case "Sheet1"
If Not Intersect(ActiveCell, Sheet1.Range("A:C")) Is Nothing Then gocal = True
Case "Sheet2"
If Not Intersect(ActiveCell, Sheet2.Range("D:F")) Is Nothing Then gocal = True
Case "Sheet3"
If Not Intersect(ActiveCell, Sheet3.Range("G:I")) Is Nothing Then gocal = True
Case Else
End Select
If gocal Then
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Value = Calendar1.Value
End If
End Sub

Mike

"Brian Matlack" wrote:

>
> Mike:
> There are 5 sheets in all that I wish the userform to work on
>
> Thanks for the response
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=560028
>
>

Thanks Mike!! Works just like I wanted it to!