Solved: Pop-up calendar confusion

[muthill001]

Hello all,
My name is Tim and i'm having some issues with using the pop-up calendar control form in VBA with Excel.

In the attached workbook i've just used the calendar control 11.0 to generate a pop-up calendar whenever a user clicks in cells a1:a100 on sheet12("January").

the issues i'm having involve the bit of code attributed to
private sub userform_initialize() in the userform

[vba]Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else:
Calendar1.Value = Date
End If
End Sub[/vba]

On the computer i made the userform on initially this code works fine so that when the user clicks on cells a1:a100, if the cell has a date entered the calendar starts on that state, but if the target cell does not have a date already entered, the calendar sets itself to Today's date.

But...i have another laptop i use to make sure that whatever i'm working on will work on more than one computer and this code doesn't work on it.

i can't for the life of me figure out why. any thoughts?

both computers have all available office and windows updates
both computers have the mscal.ocx file registered and calendar control 11.0 active
both computers have the same version of and release of VBA
both have excel 2003

[Ken Puls]

I hear you. Actually, that is the primary reason why I abandoned the Calendar Control. Depsite having the same OS, same Office versions, same OCX files registered, I haven't found that you can rely on the Calendar Control at all. I don't profess to be an expert in ActiveX controls, by any means though.

What I did, though, was change to a 100% VBA based calendar userform built by one of our members. The thread is here. Scroll down to the last post for the final version.

It would be a bit of work to convert it, for certain, but overall you'll have a much more stable and portable project. (IMO, anyway.)

[muthill001]

Thanks Ken. that seems like it could work really well for me as i had not even begun to think of how to deal with my mac users. I think i'll be able to tweak the trigger event as well as some of the formats to make it a little more basic and thusly perfect for my use. Again, thanks Ken for pointing me in the right direction.

[Bob Phillips]

I don't know if this is anything to do with it, but when I opened your workbook I got an error. This was the code as implemented

[vba]

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else:
Calendar1.Value
End If
End Sub
[/vba]

easily fixed by

[vba]

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else:
Calendar1.Value = Date
End If
End Sub
[/vba]

It also seems to me that you should turn events off in the change event so that the calendar doesn't pop-up if the date is inserted in column A (manually that is, not via the calendar control).

[johnske]

... this code doesn't work on it...

Hi Tim,

Exactly what do you mean by that - what part doesn't work? Your code works fine for me...

[johnske]

You could also try this alternative...
[VBA]
Private Sub UserForm_Activate()
With Calendar1
If IsDate(ActiveCell) Then
.Day = Day(ActiveCell)
.Month = Month(ActiveCell)
.Year = Year(ActiveCell)
Else
.Day = Day(Date)
.Month = Month(Date)
.Year = Year(Date)
End If
End With
End Sub
[/VBA]

[lucas]

Alternative KB entry:
http://vbaexpress.com/kb/getarticle.php?kb_id=543

[Ken Puls]

Steve, I didn't know that was there!

Nice one!

[lucas]

Hi Ken,
Only reason I remembered is when I went to your link in post 2 of this thread. If you read the earlier entries to the thread......that was a while back but I agree about the portability.....these type of solutions are much better than the native calender control...after you get them set up.

[Ken Puls]

LOL!

Honestly, I remembered that Lief had done several revisions of that code, so I didn't re-read the thread.

[muthill001]

"Hi Tim,

Exactly what do you mean by that - what part doesn't work? Your code works fine for me..."



yeah...that's exactly the problem i was having. it was working fine (with the addition of the "= Date" in the else expression of the code...i guess i had uploaded the wrong version of the workbook) on the laptop i made the workbook on, but when i opened it up on another, almost identical machine it would throw a "can't find object or library" error. it was driving me crazy. But, there have been some awesome resources made known to me through this thread that i think have taken care of the problem thanks again all.

[muthill001]

Alright. So the non activeX based pop-up Calendar from the KB that i was directed to earlier in this thread worked great...for the most part. I'm still having a problem wherein the workbook works great on one computer but not another.

Here is the code that is throwing the error: in the attached file it is found in the sheet12(January) object

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("$A$6:$A$100")) Is Nothing Then
Exit Sub
Else: Target.Value = getUserDate(Date)
End If
End Sub[/vba]

the error pulls one of those "can't find object or library" and highlights the word "Date" highlighted orange above

i want a user to be faced with picking a date from the pop-up calendar any time a cell in the range a6:a100 is selected

I followed the implementation steps for putting the userform and module from the sample file exactly (making user of the transfer button in the file) : vbax_calendar.xls obtained from the KB and while the attached file works perfectly on this computer it does not on the one upstairs.

Both have excel 2003
both have VBA 6.3

what am i missing?

thanks in advance for any responses.

[Bob Phillips]

In the VBIDE, go to Tools>References, and see if there are any items with MISSING besides them. If they are, uncheck them.

[muthill001]

In the VBIDE, go to Tools>References, and see if there are any items with MISSING besides them. If they are, uncheck them.

AHHH. that is why sleep is good: so you remember to do the most basic, elementary things.

If we learned anything from the 1985 Marth Coolidge cult classic film Real Genius, it's this: "...Always, no, never....for to check your referencs."

thanks so much el xld for being smarter than i am.

[Bob Phillips]

Not smarter, just went there before you.

[Ken Puls]

Not smarter, just went there before you.

No substitute for experience.