1

I have no experience with vba and would much appreciate your assistance with the implementation of a pop-up calendar user form. This link will direct you to the source of the calendar that I am trying to introduce into my workbook:

http://www.ozgrid.com/forum/showthread.php?t=142603

I have copied the class module, the user form, and the module into my workbook. When I launch the macro, this opens up a user form where i can select the date wished on a calendar. What I miss is to be able to send the date selected in the calendar to a specific cell in my workbook. I would much appreciate if somebody could guide me through how to write a few lines of code that would send the date selected in the user form to a specific cell in my workbook! Again, I am very new to this so let me know if anything is unclear in my explanation. I have spent a lot of time on this so any support is highly appreciated! It probably only takes a few moments for you but would mean a lot to me!

Community
  • 1
  • 1
Miles
  • 159
  • 2
  • 6
  • 12
  • I have added some tips for you with a code to get the `selected` value. Please take a look and comment :) – bonCodigo Dec 18 '12 at 14:45

2 Answers2

1

Try this post. It semes to give a better guide to work with datepicker control. However it shows coding to make an add-in.

Hence most basic approach for you would be to,

  • Add a Form
  • Add datepicker control
  • code from there

per this article.

But do remember calendar control in Excel/Access can sometimes disappear due this reason mentioned in my post.

If you are planning to use date picker control, here is the code to pass the value from form to anywhere you want ;)

Private Sub myDtPicker_Change()
Dim dtDateSelected as Date
   dtDateSelected = myDtPicker.Value
   '-- do anything else
End Sub
Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • figured it out! Thanks for the help! – Miles Dec 18 '12 at 15:05
  • @user1752810 I read the link you have provided, it says this " You would also need another control on the form somewhere to be bound to the class, which would receive the user's selected date. Mine is called "TextBoxDate" and it is set in the UserForm code like this:" Good this is, the author says it's a `MSCOMCT2.OCX` :) I thought adding frames would be the harder part. – bonCodigo Dec 18 '12 at 15:17
0

The class writes the selected Date into a Textbox. After you selected the Date, you can use the value of the textbox to set the value of the Cell.

Private Sub UserForm_Initialize()
    Set clsCal = New clsCalendar           'Initialize the Class'
    Set clsCal.Form(Me.TextBoxDate) = Me   'Tells the class to write the Selected date'
                                           '  into the textbox "Me.TextBoxDate"'
End Sub

So in that example, whenever you select a date, the class will automaticaly store the selected Date in the Textbox.

After you selected the date, you can use the following code to add the value to a cell:

 Range("A1").value=TextBoxDate.Value
CaBieberach
  • 1,748
  • 2
  • 17
  • 26