date picker

[alcorjr]

Hi, on some programs I have seen, that when a date must be selected, you can do it through a button that opens uo a little calendar page on which you just click on the date selected.

Is there anything similar in Excel Office XP, or is this asking too much??

Thankyou all.

 

[mikeprice53]

exactly what the man says but it is called MS.Calendar10.0

 

[alcorjr]

excuse my ignorance, but I right-click on th toolbox bar, click on more controls, a list of optional controls unfold, but there's none called Ms calendar 10.0 I found one called Microsoft office Data Source COntrol 10.0.
I tried pasting it on the sheet, but it's a weird looking control that looks more like a thumbtack than anything, and nothing like a calendar.

What am I doing wrong???

HELP!!

 

[mikeprice53]

try here if your using an old version of excel

http://www.microsoft.com/exceldev/articles/CalExcel.htm

if its an old version i reckon you wont have it or that your company ( if your at work ) poss disabled em !

Good luck

 

[PaddyD]

Couple of questions here, so one by one:

"Is there a way to make it work for a range of cells? And make the control appear only when one of those cells is selected?"

Yes. Having got used to the basic set-up, you need to control it with vba. Something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Target, [A1:A10]) Is Nothing Then
            Calendar1.Visible = True
     Else: Calendar1.Visible = False
    End If
End Sub

"i was trying to do a vlookup..."

For a reason that remains unclear, the calendar control returns a text string! Again, this can be fixed with some vba. If you've already got the control set up to only show when you're ina certain range of cells, then something like:

Private Sub Calendar1_Click()
    ActiveCell.NumberFormat = "dd/mm/yy"
    ActiveCell = Calendar1
    Calendar1.Visible = False
End Sub

should fix it.

However, vba's not really my area, so there mayh be alternative approaches / solutions / inaccuracies in my reply.

 

[mikeprice53]

Beautiful :0)

that works a treat....

VB not your area ! heheheh well your not bad my friend not bad at all.

Thank you

Mike

 

[Ranger]

Re: date picker (beware)

Hi Mike,

Just a word from experiences that I had using that Calendar control...

If you plan on others using your finish application then they must have that exact control registered on their machine. Furthermore, I found that you really can't count on that being the case.

To get around this I built a VBA user form Date Picker that after testing seems to work well every time. If interested in checking it out, send me your email address and I'll forward. It's small but powerful.

Good luck and best regards.

Phil

 

[SHEETMAGIC]

Couple of questions here, so one by one:

"Is there a way to make it work for a range of cells? And make the control appear only when one of those cells is selected?"

Yes. Having got used to the basic set-up, you need to control it with vba. Something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Target, [A1:A10]) Is Nothing Then
            Calendar1.Visible = True
     Else: Calendar1.Visible = False
    End If
End Sub

Hi Paddy,
I am keen to figure out how to make the calendar only appear when the cell it links to is clicked on. I am not sure how to get this piece of code you've provided to do that.

The cell I am dealing with is: H9

Can you suggest what I do to make this happen?

Thanks a lot,

Jonathan

 

[erik.van.geit]

Hi, Jonathan,

do you mean this ?
change this line
If Not Intersect(Target, [A1:A10]) Is Nothing Then
to
If Not Intersect(Target, [H9]) Is Nothing Then

kind regards,
Erik

 

[SHEETMAGIC]

Hi, Jonathan,

do you mean this ?
change this line
If Not Intersect(Target, [A1:A10]) Is Nothing Then
to
If Not Intersect(Target, [H9]) Is Nothing Then

kind regards,
Erik

Hi Erik,
Nice to hear from you again.
I did try that before making that post and it didn't seem to do anything.
I've tried it again and now I get an error when I click the cell H9. It says:
"Compile error. Can't find project or library"
It then jumps to the debugger and has the [H9] highlighted.

Here's the code in full:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [ H9 ]) Is Nothing Then
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub


Any idea what might cause that error?

J

 

[erik.van.geit]

no
you shouldn't have spaces
If Not Intersect(Target, [H9]) Is Nothing
(I didn't put spaces, but making Bold added them)
but this doesn't trigger an error for me
perhaps use Range("H9")