Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Matlack ------------------------------------------------------------------------ Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508 View this thread: http://www.excelforum.com/showthread...hreadid=560028 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform problem
Thanks Mike!! Works just like I wanted it to! -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform problem? | Excel Discussion (Misc queries) | |||
UserForm problem | Excel Programming | |||
userform problem | Excel Programming | |||
Userform from a Userform Problem | Excel Programming | |||
UserForm Problem | Excel Programming |