Drop-Down Calendar in Worksheet Cell

[MeGustaXL]

I'm sure I've seen a thread very recently which described how to have a Data Validation List created by the WorksheetOpen()event, so that you can have a DropDown 'Calendar' to pick from. Despite searching all over the web, I'm unable to find it again!

Tek-Tips moderators/experts - was it on here? If so, where??

Yours hopefully,
Chris

 

[Bowers74]

Check out this FAQ: faq68-3216

It will lead you to a link that shows you something like what you're looking for!

Good Luck!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Hi,

Right-click the toolbar to select the Control Toolbox

Select the More Controls icon

Select Calendar for dropdown

VOLA

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Thanks for your V.Quick replies Guys!

Bowers; That's nice to look at, but the bloke who'll be using my application doesn't have mscal.ocx on his machine, and his IT police won't let him download or install it!

Skip; I can only find the Calendar 8.0 and MS Date&Time Picker controls

Both; The technique I'm thinking of definitely used Data Validation based on a list generated at WorkbookOpen(). This is necessary 'cos I could be using a whole column, and having a calendar Pop-Up in each would make the filesize humungous!

Thanks for your efforts, though I guess it's back to Google & Dogpile again

Chris

 

[SkipVought]

You use ONE calendar object!

You control the Visible property with the Worksheet_Select event -- when the user selects within the range that you determine, you set the Visible property of the calendar to True -- otherwise False

You also set the Top and Left properties based on the selected cell's top and left properties.

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Sorry Skip - my DUH!; you are spot-on of course.

Still, what if my User hasn't got either of the above controls on his PC? Is the Date&Time Picker carried along with the parent workbook when you change machines?

Chris.

 

[SkipVought]

Private Sub Calendar1_Click()
    With Calendar1
        .TopLeftCell.Value = .Value
        .Visible = False
    End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("DateRange")) Is Nothing Then
        With Calendar1
            .Visible = True
            .Top = Target.Top
            .Left = Target.Left
        End With
    End If
End Sub
[/code


Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[SkipVought]

I'd have to try coding this in Excel 2002 and then running it on 97. I think that it would work. No special library refeence is needed.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com