Solved

Need a MsgBox to input a date

Posted on 2011-10-01
33
298 Views
Last Modified: 2012-05-12
I need a macro to have the user input a date with a pop up msgbox in cell C2 on Sheet2.

Jimi
0
Comment
Question by:eureka15
  • 14
  • 12
  • 7
33 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36897594
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
 

Author Comment

by:eureka15
ID: 36897617
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36897665
Yes - give it a try.
0
 

Author Comment

by:eureka15
ID: 36897705
Nope it does not work.
msgbox pops up to enter date then it stops at
 ActiveWorkbook.Worksheets("Sheet2").Range("C2").Value = GetDate()

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36898655
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
 

Author Comment

by:eureka15
ID: 36899865
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36899874
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
 

Author Comment

by:eureka15
ID: 36899886
2010
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36899898
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900042
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
 

Author Comment

by:eureka15
ID: 36900065
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900071
Do a search for a file in your pc. the name of the file is MSCAL.OCX.

Sid
0
 

Author Comment

by:eureka15
ID: 36900079
Nope no match.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36900087
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900096
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900101
@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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:eureka15
ID: 36900115
OK Sid
I put in your last code....a input box poped up to enter date...I entered a date...but nothing happened??
0
 

Author Comment

by:eureka15
ID: 36900122
My last post was for andrewssd3:

Nothing happened??
0
 

Author Comment

by:eureka15
ID: 36900136
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900147
>>>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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900161
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900219
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36900225
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36900234
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900240
>>>>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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900260
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
 

Author Comment

by:eureka15
ID: 36900283
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
 

Author Comment

by:eureka15
ID: 36900296
Hey Sid just saw your last post...I will not have admin rights to users pc. :(
Will go with Andrew's.

Thanks,
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36900309
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900310
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36900324
Crossover...

Sorry for calling you "Andrew" again and again Stuart :)
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36900524
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
 

Author Closing Comment

by:eureka15
ID: 36900806
Stuart Thanks that works great!
Sid Thanks also for your help.

Guys I learned a lot :)

Jimi
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now