Need a MsgBox to input a date

I need a macro to have the user input a date with a pop up msgbox in cell C2 on Sheet2.

Jimi
eureka15Asked:
Who is Participating?
 
andrewssd3Commented:
This works for me:

Sub Start_New_Report()
'
    Sheet2.Unprotect Password:=""
    Application.ScreenUpdating = False
    Dim FilenameString As String
    FilenameString = "@Master_TEC_" & Format(Date, "yyyy") & "_Week-" & Application.WorksheetFunction.WeekNum(Date)
    ActiveWorkbook.SaveAs Filename:=FilenameString
    With Sheet1
    Range("B2:B5000,D2:AY5000").Select
    Selection.ClearContents
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Sheet2
       .Range("C2").ClearContents
    End With
    Range("D2").Select
    'Sheet5.Select
    Range("A2:E20").Select
    Selection.Copy
    Sheet1.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
'maybe your code here???
    Dim vDate As Variant

    vDate = True
    Do While Not ((vDate = False) Or (IsDate(vDate)))
        vDate = Application.InputBox(Prompt:="Please enter a date", Type:=2)
    Loop
    If IsDate(vDate) Then
        Sheet2.Range("C2").Value = vDate
    End If

Application.ScreenUpdating = True
    Sheet2.Protect Password:=""
    MsgBox "New Master TEC Report has been created", vbInformation, "Master TEC Report"
End Sub

Open in new window

0
 
andrewssd3Commented:
Not sure how you want to trigger your macro, but this will do what you ask:
Public Sub FillCell()

    ActiveWorkbook.Worksheets("Sheet2").Range("C2").Value = GetDate()

End Sub

Public Function GetDate()

    Dim vDate As Variant

    vDate = True
    Do While Not ((vDate = False) Or (IsDate(vDate)))
        vDate = Application.InputBox(Prompt:="Please enter a date", Type:=2)
    Loop
    
    GetDate = vDate
    
End Function

Open in new window

Are you sure you need a pop up box to prompt?  If you just want to validate that a date has been entered, you could use data validation instead
0
 
eureka15Author Commented:
I will trigger this inside of a macro here is the code;
Sub Start_New_Report()
'
    Sheet2.Unprotect Password:=""
    Application.ScreenUpdating = False
    Dim FilenameString As String
    FilenameString = "@Master_TEC_" & Format(Date, "yyyy") & "_Week-" & Application.WorksheetFunction.WeekNum(Date)
    ActiveWorkbook.SaveAs Filename:=FilenameString
    With Sheet1
    Range("B2:B5000,D2:AY5000").Select
    Selection.ClearContents
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Sheet2
       .Range("C2").ClearContents
    End With
    Range("D2").Select
    Sheet5.Select
    Range("A2:E20").Select
    Selection.Copy
    Sheet1.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
'maybe your code here???
    
    Application.ScreenUpdating = True
    Sheet2.Protect Password:=""
    MsgBox "New Master TEC Report has been created", vbInformation, "Master TEC Report"
End Sub

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
andrewssd3Commented:
Yes - give it a try.
0
 
eureka15Author Commented:
Nope it does not work.
msgbox pops up to enter date then it stops at
 ActiveWorkbook.Worksheets("Sheet2").Range("C2").Value = GetDate()

0
 
SiddharthRoutCommented:
Jimi

I would suggest you to avoid the input box for getting dates. input box returns string values and you have no control on what the user might put in the input box. Which means you will have to write hell lot of a code to do the necessary validations.

Instead, create a userform with a date and time control in it and use that to get the date :) That ways you will also have to write less code :)

Sid
0
 
eureka15Author Commented:
Sid, this is for my company and only one person will be doing this would you still use a user form?
If so how would I go about doing that?
Which is the easiest input box or user form?

Jimi
0
 
SiddharthRoutCommented:
Userform with datetime control will be easier as you will then not have to write validation code to check if the user inputs invalid data in the input box :)

>>>If so how would I go about doing that?
I can give you a sample. Which Excel version do you have?

Sid
0
 
eureka15Author Commented:
2010
0
 
SiddharthRoutCommented:
Ok :)

The code that I will be using is this :) Now tell me which is simple? Shortly will upload a sample :)

In a module
Option Explicit

Sub Sample()
    UserForm1.Show
End Sub

Open in new window


In the click event of the date and time picker.
Private Sub DTPicker1_Click()
    Sheets("Sheet1").Range("C2").Value = DTPicker1.Value
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
Sorry, Jimi

Had stepped out for a moment. Could you please confirm if you have MSCAL.OCX file on the pc where you are planning to run this excel document? Also please confirm whether you have a 32bit or a 64bit system?

Sid
0
 
eureka15Author Commented:
NP, Sid.

My pc is 64 bit excel 2010
User pc is 32 bit excel 2007

I do not know about a MSCAL.OCX?
0
 
SiddharthRoutCommented:
Do a search for a file in your pc. the name of the file is MSCAL.OCX.

Sid
0
 
eureka15Author Commented:
Nope no match.
0
 
andrewssd3Commented:
That's why I don't usually try to use the date/time picker in Excel VBA - you can't gurantee it will be present unless the user is a developer.  My original suggestion was pretty simple, doesn't need tons of validation code, and should work if you put in the code

    Dim vDate As Variant

    vDate = True
    Do While Not ((vDate = False) Or (IsDate(vDate)))
        vDate = Application.InputBox(Prompt:="Please enter a date", Type:=2)
    Loop

Open in new window


in the place you indicated in your post. You can move the Dim to the top of your module if you prefer.
0
 
SiddharthRoutCommented:
Jim

Ok first download the file from here.

http://www.gmayor.com/Zips/MSCAL.ZIP

Since only one user will be using and you are not planning on distributing the app then you should not have a problem :)

Also please refer to this link for the deployment of that ocx :)

Topic: Date Picker For Excel 2010 Form
Link: http://social.msdn.microsoft.com/Forums/kk-KZ/exceldev/thread/b2ce1cdf-9f21-4511-9cc4-e9e8295f7c7e

Sid
0
 
SiddharthRoutCommented:
@Andrews: What is the user enters "$##$#$@#$" in the inputbox? How will you handle that? What if the date in cell C2 acts as an input for some other cell? :)

Sid
0
 
eureka15Author Commented:
OK Sid
I put in your last code....a input box poped up to enter date...I entered a date...but nothing happened??
0
 
eureka15Author Commented:
My last post was for andrewssd3:

Nothing happened??
0
 
eureka15Author Commented:
BTW- C2 is a input cell for many formulas on my worksheet!

Sid does the MSCAL need to be installed on the user pc as well?

0
 
SiddharthRoutCommented:
>>>BTW- C2 is a input cell for many formulas on my worksheet!

So I guessed it right ;-)

>>>Sid does the MSCAL need to be installed on the user pc as well?

Yes, It needs to be installed on the pc wherever you plan to open the file. It is a one time thing and it is very simple :) I have it installed on my pc as well.

Sid
0
 
SiddharthRoutCommented:
Wait. I made a mistake. When i said date and time picker, I meant a calendar control. I am uploading a video so that you can see how it works. Please give me few minutes.

Sid
0
 
SiddharthRoutCommented:
See this video. it just took me 2 minutes to achieve what you wanted and that too with 6 lines of code :)

This way the user will not be able to enter incorrect data in that cell... Not even in this format (02.08.2010). Few more things. The calendar control gives you flexibility that an inputbox will not give you

1) User will not be able to input anything else other than a date. .....
2) The calendar control will input the date in the format that your regional settings supports.
3) It lets you easily navigate to a particular month in a particular year.

All you need to do is first install the OCX as mentioned int he link that I gave above :)

Hope this helps :)

Sid
SiddharthRout-508657.flv
0
 
andrewssd3Commented:
Sorry I didn't include enough of my original code -  add lines after the others:

    
If IsDate(vDate) Then
    ActiveWorkbook.Worksheets("Sheet2").Range("C2").Value = VDate
End If

Open in new window

0
 
andrewssd3Commented:
Sid - I agree the calendar control is great - it's just the pain of having to have it available. Even if you switch machines you'll have to download it again, and if you send your file to anyone else it's likely not to work unless you create an MSI package to install it all
0
 
SiddharthRoutCommented:
>>>>it's likely not to work unless you create an MSI package to install it all

No that is not true :) You can easily register the OCX using Start~~>Run if you have admin rights. :)

Sid
0
 
SiddharthRoutCommented:
However, I agree on one thing with you :)

>>>it's just the pain of having to have it available.

:)

Jim, if you don't have admin rights then do what Andrew suggested :) it is much easier and the workbook will be easier to distribute as well ;)

Sid
0
 
eureka15Author Commented:
Sid I cannot get MSCAL installed??

andrewssd3:
Yours code keeps stopping for dugging on line
ActiveWorkbook.Worksheets("Sheet2").Range("C2").Value = vDate

can you repost the whole code to make sure I have it right inside of my code?
0
 
eureka15Author Commented:
Hey Sid just saw your last post...I will not have admin rights to users pc. :(
Will go with Andrew's.

Thanks,
0
 
andrewssd3Commented:
Sid

You're right, you don't have to have an msi - and Jim said he's the only user anyway so it will probably be fine.  I was just making a more general point because I once had an Excel spreadsheet to support that has several of these ocx controls and other dlls that were not standard, and they really had created an msi to distribute the spreadsheet plus all the extras to lots of users.

Stuart
0
 
SiddharthRoutCommented:
I will wait before Andrew posts the complete code. Try that first :)

After that we will go through a step by step procedure on installing the mscal.ocx :)

Sid
0
 
SiddharthRoutCommented:
Crossover...

Sorry for calling you "Andrew" again and again Stuart :)
0
 
eureka15Author Commented:
Stuart Thanks that works great!
Sid Thanks also for your help.

Guys I learned a lot :)

Jimi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.