Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

OUTLOOK MACRO - VBA - INPUTBOX - MINI CALENDAR

Posted on 2009-02-20
10
Medium Priority
?
1,741 Views
Last Modified: 2012-08-14
I have been helped by many here and I truly appreciate it. As I get new ideas.. I come here for guidance..

I have an outlook macro that asks a bunch of questions then generates a calendar event and opens MS word and fills in a template..

What I want to know know is the first question it asks is date.. and the user has to enter something like 2/27 for February 27th.. instead of that is there some type of mini calendar they can use to select the date?  Either and example or link is greatly appreciated.
0
Comment
Question by:DMTechGrooup
  • 6
  • 4
10 Comments
 
LVL 11

Assisted Solution

by:Antagony1960
Antagony1960 earned 2000 total points
ID: 23699620
I'm not certain about earlier versions (I only have access to Outlook 2007 presently) but I think they'll be the same or very similar.

You can add the "Microsoft Date and Time Picker Control" (for a drop-down type calendar) or the "Microsoft MonthView Control" (for an open mini calendar) to the toolbox and place them on a user form.
0
 
LVL 24

Author Comment

by:DMTechGrooup
ID: 23700628
Ok I found the Month View Control.. created a UserForm1 with that control and all defaults left alone.. how do I call the form when you run the macro?

Here is the begining of the macro as it is cycling through the questions.
Sub RRAAppointment()
    Const MACRONAME = "RRA Appointment"
    Dim strName As String, strAddress As String, strCity As String, strState As String, strZip As String, strCAT As String, _
        strPhone As String, strAltPhone As String, strCBA As String, strMakeModel As String, strLoc As String, strPT1 As String, _
        strWarranty As String, strIssue As String, strPT As String, strDTE As Date, strTM As String, strDIR As String, olkAppt As Outlook.AppointmentItem, strDT As String, strPO As String
    
    strDTE = InputBox("Enter a date #/##", MACRONAME)
    strTM = InputBox("Enter a time slot :" & vbCrLf & vbCrLf & "(1) 8-11" & vbCrLf & "(2) 9-12" & vbCrLf & "(3) 10-1" & vbCrLf & "(4) 11-2" & vbCrLf & "(5) 3-6" & vbCrLf & "(6) 4-7" & vbCrLf, MACRONAME)
    strName = InputBox("Customer Name", MACRONAME)
    strAddress = InputBox("Address", MACRONAME)
    strDIR = InputBox("Directions", MACRONAME)
    strPT = InputBox("City/Location :" & vbCrLf & vbCrLf & "(1) E. St George" & vbCrLf & "(2) W. St George" & vbCrLf & "(3) S. St George" & vbCrLf & "(4) Bloomington" & vbCrLf & "(5) Bloomington Hills" & vbCrLf & "(6) Sun River" & vbCrLf & "(7) Ivins" & vbCrLf & "(8) Santa Clara" & vbCrLf & "(9) Washington" & vbCrLf & "(10) Hurricane" & vbCrLf & "(11) Corral Caynon" & vbCrLf & "(12) Mesquite" & vbCrLf & "(13) Cedar City" & vbCrLf & vbCrLf & "OR SPECIFIY CITY" & vbCrLf, MACRONAME)
    strPhone = InputBox("Phone", MACRONAME)
    strAltPhone = InputBox("Alterante phone", MACRONAME)
    strCBA = InputBox("Call Before Arrival minutes", MACRONAME)
    strMakeModel = InputBox("Enter a make/model", MACRONAME)
    strWarranty = InputBox("(C)ash (W)arranty or (P)arts Warranty", MACRONAME)
    strIssue = InputBox("Issue", MACRONAME)
    strPO = MsgBox("Print out work order?", vbQuestion + vbYesNo, "Work Order Printout")

Open in new window

0
 
LVL 11

Accepted Solution

by:
Antagony1960 earned 2000 total points
ID: 23700728
This is what I suggest:

1. Because the MonthView's change events fire as the user picks years, months or days, I would add an OK button to the form. The user can then change the date to their hearts content and click OK when they're done.

2. Double-click the button to initiate the Click event code block and have it set the form's Tag property to the value of the MonthView and then hide the form, like this:

    Private Sub CommandButton1_Click()
        Me.Tag = MonthView1.Value
        Me.Hide
    End Sub

3. Add the function below to your code module and call it from your own routine using something like this:

    strDTE = GetDate("Select a date")

Of course you may want to refine it somewhat: give the controls and forms proper names, add a Cancel button, pass the MACRONAME constant for the caption and add a label for the prompt string etc... let me know if you need help with any of that.

Function GetDate(ByVal sPrompt As String) As Date
    Load UserForm1
    With UserForm1
        .Caption = sPrompt
        .Show vbModal
        GetDate = CDate(.Tag)
    End With
    Unload UserForm1
End Function

Open in new window

0
Industry Leaders: 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 24

Author Comment

by:DMTechGrooup
ID: 23700895
Thanks for your help.. I am very rusty as VB..

I added the OK button.. but I cant figure out what to put in the OnClick properties..

I googles around and nothing has worked.
Private Sub OK_Click()
vbOK
 
End Sub

Open in new window

0
 
LVL 11

Assisted Solution

by:Antagony1960
Antagony1960 earned 2000 total points
ID: 23700921
It's in step 2 of my last post, you just need to change the first line to match your new button name.

BTW, it looks as though you've named the button "OK"--which will work but it is generally good practice to prefix object names with something which identifies what they are, to make it easier to debug/follow the code later--might I suggest you rename it to "cmdOK"?

In which case the code block would look like this:

    Private Sub cmdOK_Click()
        Me.Tag = MonthView1.Value
        Me.Hide
    End Sub
0
 
LVL 24

Author Closing Comment

by:DMTechGrooup
ID: 31549516
Very helpful, Thanks for your time and help it works great!  I am sure I will be asking new stuff in the future and hope I can get your help!  Thanks again.
0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 23701199
Glad to have helped. :-)
0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 23704076
@DMTechGrooup:

I forgot to mention that if you've used the form's Tag and the function, as I described above, you will need to handle the eventuality of a user closing the form using its close button (x) rather than the OK button (you know that if a user can do something the wrong way they probably will). As things stand it will unload the form and cause the function to error. The workaround is easy though, just add this code to your user form:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        Cancel = True 'Prevents the form unloading so its Tag property can still be read
        Me.Tag = "Cancel"
        Me.Hide
    End Sub

Or, if you don't want to give users an option to cancel, do something like this instead:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        Cancel = True
        MsgBox "Select a date and click OK.", vbExclamation, "Not Allowed!"
    End Sub

0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 23704095
Oops.. that UserForm code block should have this line at the top:

        If CloseMode > 0 Then Exit Sub

Otherwise it will still execute when it is closed legitimately from the function.
0
 
LVL 24

Author Comment

by:DMTechGrooup
ID: 23705914
Thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses
Course of the Month20 days, 17 hours left to enroll

810 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