VBA Calendar Control without Control

[Ranger]

A challenge for all...

Has anyone out their ever seen an Calendar type of control totally built in an Excel vba UserForm?

My problem that I’ve tried to resolve for some time is utilizing some type of pop-up calendar to eliminate format issues in my published Excel forms. I have tried a number of calendar controls but all have to be registered on the local machine and this cannot be guarantied for every machine.

If someone could direct me to a vba UserForm that has this built in that might do the trick. Or is their another way to deal with this?

Any help would be greatly appreciated.

Phil

 

[Iridium]

A few links to look at

http://www.cpearson.com/Zips/CALENDAR.ZIP
http://www.freevbcode.com/ShowCode.Asp?ID=3471
http://www.fontstuff.com/vba/vbatut07.htm

HTH

 

[mpfx]

This is from the archives that I found and copied some time ago, I didnt copy who posted it so sorry for no acknoweldgement.. Works a treat.
The Resulting Method
( This is the result if the VBA code and form is produced )
When you want a date in a particular cell
1- Click once on the cell where you would like date
2- Press Ctrl+d ( a calender will appear)
3- Double click on date in Calender
4- Date is entered in selected cell
5- Calender disappears

HOW TO MAKE IT HAPPEN

First: Make a VBA form
1- Open VBA editor
2- Goto VBA toolBar
3- Select "Insert" from VBA toolbar
4- Select "UserForm" from drop down menu
(you have created a userform called "userform1"

Second: Put calander on userform
1-In VBA window select the "toolbox"
2- Right click on "toolbox"
3- Select "additional controls" item from drop down menu
4- check/select "calender control" from the window that will appear
5- a calender object will appear on the "toolbox"
6- Drag and size the calender object from the "toolbox" to "userform1"

Third : Write VBA code for calender
1- Once calender is on userform1 then "double click" the calender...the VBA code window for the calender will open
2- Insert the following code

Code:
Private Sub Calendar1_DblClick()ActiveCell.Value = Calendar1.ValueUserForm1.HideUnload UserForm1End Sub



Fourth Write code to make calender appear
-1 In VBA window select "insert" from VBA toolbar
-2 From drop down menu select "Module"
-3 In the new VBA "Module" window that will appear paste the following code

Code:
Public Sub GetDate()Load UserForm1UserForm1.ShowEnd Sub



Fifth : Assign Shortcut key to Macro
-1 Close VBA window with the X in upper right corner
-2 You should now be looking at excel worksheet
-3 On excel toolbar select "Tools"
-4 From drop down menu select "Macro"
-5 From next drop down select "Macros..."
-6 From the list of macros that appear select the name "GetDate"
-7 Select the "command button " in the same window labeled "Options"
-8 Assign the shortcut key for Macro to be "Ctrl+d"
-Press OK and exit window

IT IS NOW READY FOR USE

 

[Ranger]

Thanks, but, still perplexed

Chris

The links that you provided were very helpful. I have explored these in my past research but cannot figure out how to make any of this work every time on multiple machines.

The active x controls have to registered on the resident machine or the form will have an error.

I can register the control with vba but that makes for a tricky download for the unknowing user.

Still perplexed and searching. Just short of offering a bounty…

 

[psawyer]

hier an alternative to Control Calendar. http://www.psawyer.de/VBA/PickDateForm.xls

 

[bulevardi]

another alternative here:

(multi calendar with 3 month overview )
- ability to select multiple dates and to add messages to them
- features a tool to edit and delete messages
- fully modifiable
http://bulevardi.be/?content=scripting&example=exvb9

or a simple one:
http://bulevardi.be/?content=scripting&example=exvb7

 

[bulevardi]

another alternative here:

(multi calendar with 3 month overview )
- ability to select multiple dates and to add messages to them
- features a tool to edit and delete messages
- fully modifiable
http://bulevardi.be/?content=scripting&example=exvb9

or a simple one:
http://bulevardi.be/?content=scripting&example=exvb7

The link changed to bulevardi
As the other site went offline.

 

[PanchoSV]

Re: Thanks, but, still perplexed

Ranger,

I've been searching for days now, and one lucky set of keywords in google brought me here. I'm trying to the exact same thing in the 2010 version. Have you found a way to do it yet? It's been nearly 10 years since your post, so if you have, could you tell me? Otherwise, if you're still looking, I might just join you in offering that bounty, it's driving me nuts...

Pancho

 

[bulevardi]

Re: Thanks, but, still perplexed

I might just join you in offering that bounty, it's driving me nuts...

Pancho

Pancho, you make my day

 

[psawyer]

Re: Thanks, but, still perplexed

here the latest version of : an alternative to Control Calendar (Pick Date Form). http://www.psawyer.de/VBA/PickDateForm.xls