userform problem

[Brian Matlack]

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

 

[Guest]

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]

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

Thanks for the response

 

[Guest]

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

 

[Guest]

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]

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