• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel userform vba calendar control

S

Sameer.k21

Member
hello again,

I have below questions with which I need help/suggestions please.

1. A userform created in excel 2007 which has calendar control option in additional controls - will this work as it is if system having 2013 on it?

2. How do I get Calendar control in 2013? If not, the code?

3. I have a userform which has date label and a textbox with Dblclick event to bring thw calendar popup. The date, instead of showing up in the textbox on the form, goes to the activecell on the sheet.

Your suggestions and help is much appreciated.
 
1. Not sure. I use custom Calendar Control for backward compatibility with 2003.

2. http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB
Download and install Windows Common Controls from above link. Go to your module and in "Additional Controls" find Microsoft MonthView Control 6.0

3. Not sure what you are asking here.

http://chandoo.org/forum/threads/ge...-cell-in-excel-using-macro.25939/#post-155134
Above link is to thread where it has custom calendar code that's been modified (see attachment for post #4). Can't remember where I found original code though.

FYI - Don't forget to create sheet named "Calendar" first, or the code will not work.
 
Thank you so much, but this dint work for me.

I created a userform with Start Date, End Date, Received Date as labels and each label with a textbox. Addin for calendar control cannot be installed due to admin rights.
My search ends with installing addin to get the Calendar control 6.0 installed which fails with error "Access is denied" on my machine.
My userform works fine, user has to input date manually.
How to do I get a calendar popup here or any other suggestions to enter the date by just a click on Excel 2013 running on 64bit.
Appreciate your time here. Thank you.
 
Use custom calendar form from the link provided (in attachment for post #4).

You will need to go into VBA Module and export it out as *.frm format.
Import the *.frm file into your workbook.

In your workbook create "Calendar" sheet. This sheet can be hidden.

Add button and use "SHOW" function to bring up the calendar form.

You can adjust output field as your requirement by adjusting portion of the code indicated in Post #4 in the link.

Form tested and working for, Excel 2003, 2007, 2010 & 2013 (32 & 64 bit).
 
Almost getting there. Thank you so much Chihiro and NoSparks.
I now linked the my userform with the one which I just imported from the link shared above.

The date selected in the calendar form does not go to my userform which has Start Date textbox.

Code: 
Private Sub TextBox5_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.Show
    UserForm1.TextBox1 = Me.TextBox5
    Unload UserForm1

Userform1 is calendarform
Textbox1 is the textbox on the calendar
Textbox5 is the box on 1st userform

It selects the date and when I hit ok on the calendar date doesn't get copied to the Textbox5
 

Attachments

  • Sample.JPG
    Sample.JPG
    42.7 KB · Views: 25
You need to edit this portion to suite your need in the Calendar form (UserForm1).

Code: 
'~~> Ok Button
Private Sub CommandButton53_Click()
    If TextBox1.Value <> "" Then
        ThisWorkbook.Sheets("Sheet1").Cell(2, 1) = TextBox1.Value
    End If
    Unload UserForm1
End Sub
 
Ok, I fixed that. "s" was missing in "cells".
Will it go to the next blank cell in the row?
The userfrom with received date shows up the date - when I click "OK" on the calendar - the date gets pasted on the cell A2 and delets that from the form.
 
In your case you should pass it onto your form's TextBox5 instead of into sheet directly from Calendar Form.

So something like this.
Code: 
If TextBox1.Value <> "" Then
    "YourUserForm".TextBox5 = TextBox1.Value
End IF
 
I tried that already...but the value from Received date stays only till the calendarform is open. I click ok on the calendarform and the date disappears.
 
You need to replicate Calendar form for each Field in your form. Or modify code to Check if TextBox5 and is empty or not and only input data if certain conditions are met.

If you can upload sample file I can help you modify the code.
 
I have shared the file. Thank you for your time, Chihiro.
Please do confirm, once you are able to access the file.
 
Last edited:
You must log in or register to reply here.
Back
Top