Putting a default value in a InputBox

Putting the current year date in a InputBox?  or even better validating that the entry is in yyyy-mm-dd format?  or is this already validating the result?

.Body = .Body & "TargetDevCompleteDate##" & Format(DateValue(InputBox("TargetDevCompleteDate: MUST BE IN yyyy-mm-dd format")), "yyyy-mm-dd") & "##" & Chr(10)

Open in new window

RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The Format function which you used to format the input date will format the date entry in yyyy-mm-dd even if the user enters in another date format provided input is a date entry.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Moreover if you want to make sure that only a valid date is entered by the user, you may try something like this...
Dim strDate As String
strDate = InputBox("TargetDevCompleteDate: MUST BE IN yyyy-mm-dd format", "User date", Format(Date, "dd/mm/yy"))
If IsDate(strDate) Then
    strDate = Format(CDate(strDate), "yyyy-mm-dd")
    .Body = .Body & "TargetDevCompleteDate##" & strDate & "##" & Chr(10)
Else
    MsgBox "Invalid date!", vbExclamation
    Exit Sub
End If

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Have you considered a UserForm with a DatePicker? Much easier to work with.
Calendar-Form-1-02--2-.zip
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RWayneHAuthor Commented:
I like this DatePicker option...   if this will work in OutLook?  There are a couple of things that I would need help with.  First is changing the date format to yyyy-mm-dd and the other is to be able to save the result as a variable.. so it will work asi:  strDate

.Body = .Body & "TargetDevCompleteDate##" & strDate & "##" & Chr(10)

Open in new window


My plan was to use this for a few other dates too...  so what happens when I need another date, like?  Are there further edits to make it work?

.Body = .Body & "ActualDevCompleteDate##" & strDate & "##" & Chr(10) 

.Body = .Body & "CreateDate##" & strDate & "##" & Chr(10) 

Open in new window


I using these to build a email...
0
Roy CoxGroup Finance ManagerCommented:
I thought it was for Excel.
0
RWayneHAuthor Commented:
So, instead of clicking button, i show.userform?

This should work in OutLook too right?
0
Roy CoxGroup Finance ManagerCommented:
The UserForm is written for Excel, It will place the date into the selected cell. I can't see why you want to use it with OutLook which is not mentioned in the question previously.
0
RWayneHAuthor Commented:
Sorry, I though it was assumed when I used:  .Body = .Body &  in the question.

It not being in the right date format is going to be an issue and the need to save it as a variable..  Would this be difficult to edit?
0
Roy CoxGroup Finance ManagerCommented:
So you don't mean to use the form directly in OutLook.

I've added an example of how you can use the calendar to put the date into the Variable and then you can use it in your OutLook message

You will need to drag the frmCalendar , the Class module  and also mGlobals to your workbook
frmCalendarv1-02.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
I think I have it working...  in the Excel file it pops up a MsgBox with the date.... I am not finding that...  what is the variable name used?
0
Roy CoxGroup Finance ManagerCommented:
I used your variable. I'll be home soon and I'll post the code
0
RWayneHAuthor Commented:
I have the calendar showing, and I am able to select a date...  however in OutLook, does not produce a MsgBox but and Excel does.  I am reviewing the Excel code now.

.Body = .Body & "ActualDevCompleteDate##" & strDate & "##" & Chr(10)

is resulting in: TargetDevCompleteDate####
0
Roy CoxGroup Finance ManagerCommented:
Can you attach an example workbook. The code will not work from Outlook
0
RWayneHAuthor Commented:
Ok... not in OL is another problem...  I am pretty close.  I show the userform, select a date and it appear to be working...  thinking I just to map the variable strDate correctly and I am good...  one another difference is that after I select the date, the calendar does not disappear, so I was clicking the Close....  and duh, probably does not set strDate.   So whatever closes the calendar, when a date is selected, maybe where it is hanging up...   not sure this will help or not?
0
RWayneHAuthor Commented:
Remember it is not in an Excel workbook, it is in OutLook  I will send code being used.

After the calendar displays... (and not clicking the Close button, it is stopping at the following giving error:  Compile error Ambiguous name detected: g_bForm   Ln9   So I need it to remember which day on the calendar is clicked on..


Sub CmdBtnGroup_Click()

    If Month(CDate(CmdBtnGroup.Tag)) <> frmCalendar.CB_Mth.ListIndex + 1 Then
        Select Case _
               MsgBox("The selected date is not in the currently selected month." _
                      & vbNewLine & "Continue?", _
                      vbYesNo Or vbExclamation Or vbDefaultButton1, "Date check")
            Case vbYes
                If g_bForm Then
                    GoTo on_Form
                Else: GoTo addDate
                End If
            Case vbNo
                Exit Sub
        End Select
    Else:
    If g_bForm Then
        GoTo on_Form
    Else: GoTo addDate
    End If
addDate:
    With ActiveCell
        .Value = CDate(CmdBtnGroup.Tag)
        .EntireColumn.AutoFit
    End With
    GoTo chg_month
on_Form:
    g_sDate = CmdBtnGroup.Tag
chg_month:
    With frmCalendar.CB_Mth
        .ListIndex = Month(CmdBtnGroup.Tag) - 1
    End With
     End If
    Unload frmCalendar
End Sub

Open in new window

0
RWayneHAuthor Commented:
I had to comment out Ln22 thru Ln25, those are excel lines.
0
RWayneHAuthor Commented:
and it looks like Tag is holding the selected day when then the calendar is unloaded.
0
Roy CoxGroup Finance ManagerCommented:
The button tag stores the day, the selected date will not save when the form is closed.
0
RWayneHAuthor Commented:
yes, while stepping through the code,  Ln34  Unload frmCalendar     after that line, Tag (which has the selected day) disappears.  Does Tag need to be a Public variable?
0
RWayneHAuthor Commented:
So is there a way to get that value out it use as a variable?  After this it bounces back out to the main module that I executed the frmCalendar.Show_Cal  from.  If I can get that value, I am home free.  Any ideas?
0
RWayneHAuthor Commented:
NAILED IT!!!!! and it is working awesome!!!!  I am not sure how to export the OutLook Project1(VbaProject.OTM   (that is probably a separate question to ask...
0
RWayneHAuthor Commented:
Thank you both for your help with this...  I now have a OutLook sub that, from InputBox(s) a ListBox, and now a pop up Calendar selector builds a OutLook email.  From this email, when sent it auto-generates a ticket in our RemedyForce ticket system...    THANKS!!
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help. It would be interesting to see the result with the userform. This article might help with the export.
0
RWayneHAuthor Commented:
I exported all the items I created and put them in a folder and zipped it. (attached)  Open MS-OL, Alt+F11 to get to the VBA Editor,  Unzip the attached file and Import them all into the Project1(VbaProject.OTM).  The sub I use to execute code is: Sub RemedyAUTOGEN_EMailOutlook()

It is working for me, hope it does for you.
GenerateEmail.zip
0
Roy CoxGroup Finance ManagerCommented:
I'll test it out thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.