Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date Control in userform

Posted on 2013-01-11
9
Medium Priority
?
1,076 Views
Last Modified: 2013-02-07
Hi,

I'm trying to add a calendar function in word 2010, through VBA. I used regedit to register the mscal since it is not incl. in the office package.

I have made a txtbox for the date to be added from the frmCalendar, but can't get it to work.

When I push the command button I get a run-time error 424 in line no. 3. Here is my code for that button:

Private Sub cmdDate_Click()
    
    Load frmCalendar
    frmCalendar.Show
    Me.txtDate.Text = frmCalendar.Calendar1.Value
     
     ' now unload
    Unload frmCalendar

End Sub

Open in new window

From frmCalendar

Private Sub cmdClose_Click()
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
   
    If IsDate(ActiveCell.Value) Then
        Calendar1.Value = DateValue(ActiveCell.Value)
    Else
        Calendar1.Value = Date
    End If
End Sub

Private Sub Calendar1_Click()
    Me.Hide
End Sub

Open in new window


I have used this code in excel before and it works fine there. Is it not possible to use this in word?
0
Comment
Question by:agepab
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38767605
I get a compile error because Word doesn't know what the ActiveCell object is in lines 7 and 8 of the second snippet.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38767620
Mine is different because I have Option Explicit at the top of every module's code.

I recommend that you set the option to do that automatically for any new modules
Tools/Options, Editor tab, set 'Require Variable Declaration' on. I always disable the Auto Syntax Check at the same time, because it gets very annoying.
0
 

Author Comment

by:agepab
ID: 38854087
Yes, it comes from a excel VBA that I had. Should I use ActiveDocument in word instead?

I want to ad a date into a vba userform from the calendar control (frmCalendar).

The main userform has a field called txtDate, next to this I have a command button that calls up frmCalendar. This works but what code can I use to make the calendar control insert a date to the txtDate field?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 2000 total points
ID: 38854155
If, by field, you mean a textbox, then you could add one to the form and have some code like this:
Private Sub cmdInsertDate_Click()
    txtDate.Text = Calendar1.Value
End Sub

Private Sub UserForm_Initialize()
    Calendar1.Value = Date
End Sub

Open in new window

0
 

Author Comment

by:agepab
ID: 38854264
No,

when the user click the calendar date that he want, the frmCalendar closes and date is put into the textbox "txtDate".

So it should be in the: Private Sub Calendar1_Click()

I tried to update the code I had above, but I do something is defiantly wrong.

'code for the frmCalendar

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub Calendar1_Click()

     ' Check if active  cell contains a date. If 'yes' show
     ' same date on calendar. If 'no' show today's date.
      
      If IsDate(ActiveDocument.FormField) Then
        Calendar1.Value = DateValue(ActiveDocument.Calendar1.Value)
    Else
      Calendar1.Value = Date
   End If
Unload Me

End Sub

Private Sub UserForm_Initialize()
Calendar1.Value = Date
End Sub



'From the cmdButton

Private Sub cmdDato_Click()
    
    Load frmCalendar
    frmCalendar.Show
    ActiveDocument.txtDate.Text = frmCalendar.Calendar1.Value
     
     ' now unload
    Unload frmCalendar

End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38854485
As a general rule, if your code stops with an error, tell us what it is, and where it is. That saves a lot of guessing.
However, this line must be wrong.
If IsDate(ActiveDocument.FormField) Then

Open in new window

You need to specify a particular FormField and which property is being tested
If IsDate(ActiveDocument.FormFields("MyDate").Result) Then

Open in new window

I don't understand this.
Calendar1.Value = DateValue(ActiveDocument.Calendar1.Value)

Open in new window

Do you have a Calendar control on the document as well as on the UserForm? I advise against putting ActiveX controls on the document itself. They don't print well, and they are resource-heavy.

Where and what is txtDate? Is it, too, an ActiveX control on the document?
0
 

Author Comment

by:agepab
ID: 38854718
No, I do not have a calendar control on the document it self, just on a userform.

I'll try to visualise how I want this function to work.

From the main userform, the field we see is the txtDate.
When I push the command button on the right of the txt.Date field (a little calendar icon on it) the calendar (frmCalendar) will pop up.

this is the frmCalendar. User can now pick the date he wants
When he click the date he wants, the frmCalendar closes and the date he picked are shown in txtDate field on the Main userform. (the first picture).

I think it is many lines that are not correct in this vba. That's why I posted the whole thing :) Not just the error codes, since I was trying to explain it..

I'am a big amateur in word VBA. But I'll try to learn :)
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 38855752
If the textbox is on another form, then you address it with the name of the form:
Private Sub cmdInsertDate_Click()
        MainForm.txtDate.Text = Calendar1.Value
        Me.Hide
End Sub

Open in new window

0
 

Author Closing Comment

by:agepab
ID: 38863030
Thanks works perfect now.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question