Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Hi there,
I am really green with Excel. I am trying to create an input box that is formated to accept 'Date' data only. The system will take the date entered, search the 'Master Log' sheet, and display the row of data for that date. Can anyone shed some light on how to accomplish this task? Many Kind regards, -- Carlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Sub carlee()
Dim d As Date d = DateValue(Application.InputBox("enter date: ", 2)) End Sub This allows a lot of options for the user, but still gets a date. -- Gary''s Student - gsnu200713 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Hi Carlee,
to check, whether a string could be a date, and a reasonable date in addition (!), is an endless story. Better see, e.g. by Martin Green, http://www.fontstuff.com/word/wordtut03a.htm or depending on you Office version, there is a Date and Time Picker control. There is a IsDate-function as well, but you won't get far with it. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Hi Gary
Sub carlee() Dim d As Date d = DateValue(Application.InputBox("enter date: ", 2)) End Sub This allows a lot of options for the user, but still gets a date. yes, but that is as well the drawback of it if possible anyhow, it converts the input into a date For input "1 2 3" it returns "2001-02-03" For input "1 20 30" it returns "1930-01-20" For input "9999 1 1" it returns "9999-01-01" For input "1 1 9999" it returns "9999-01-01" as well -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
This help with the error's
Sub carlee1() Dim d As Date On Error GoTo InValidDateEntry d = DateValue(Application.InputBox("enter date: ", "Date Data Only")) InValidDateEntry: If Err = 13 Then MsgBox "Not a valid date " & " : " & "Please retry ", , "Date Data Only" End If End Sub "Carlee" wrote: Hi there, I am really green with Excel. I am trying to create an input box that is formated to accept 'Date' data only. The system will take the date entered, search the 'Master Log' sheet, and display the row of data for that date. Can anyone shed some light on how to accomplish this task? Many Kind regards, -- Carlee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
And I think that depends on what your windows date settings are.
I got January 2, 2003 with "1 2 3" and my USA settings. I like this a little better: Option Explicit Sub carlee2() Dim d As long d = Application.InputBox("enter date: ", Type:=1) If Year(d) < 2000 _ Or Year(d) 2010 Then MsgBox "quitting" Exit Sub End If Debug.Print d & vblf & format(d,"mmmm d, yyyy") End Sub Checking for the date for a valid year can eliminate some of the problems. Helmut Weber wrote: Hi Gary Sub carlee() Dim d As Date d = DateValue(Application.InputBox("enter date: ", 2)) End Sub This allows a lot of options for the user, but still gets a date. yes, but that is as well the drawback of it if possible anyhow, it converts the input into a date For input "1 2 3" it returns "2001-02-03" For input "1 20 30" it returns "1930-01-20" For input "9999 1 1" it returns "9999-01-01" For input "1 1 9999" it returns "9999-01-01" as well -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Hi Dave,
And I think that depends on what your windows date settings are. yes, which doesn't make it any easier. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Hi there,
THanks for all the responses. I think i might not have phrased my question right. What i need to do is the following: 1. Provide user with parameter form to enter a valid date 2. System refers to 'Master Log' sheet and finds the row matching the date entered (there will only be one row for each date). I only need four or five values in the identified row, for example Reading Date, Value1, Value2, Value3 3. Display the specified rows from 'Master Log' sheet, in a specific location on 'Customer1 Daily' sheet. Can anyone help me? -- Carlee "Mike" wrote: This help with the error's Sub carlee1() Dim d As Date On Error GoTo InValidDateEntry d = DateValue(Application.InputBox("enter date: ", "Date Data Only")) InValidDateEntry: If Err = 13 Then MsgBox "Not a valid date " & " : " & "Please retry ", , "Date Data Only" End If End Sub "Carlee" wrote: Hi there, I am really green with Excel. I am trying to create an input box that is formated to accept 'Date' data only. The system will take the date entered, search the 'Master Log' sheet, and display the row of data for that date. Can anyone shed some light on how to accomplish this task? Many Kind regards, -- Carlee |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box as Parameter for report
Hi Carlee,
I think you better use a userform, not an inputbox. Put a listbox on the userform. Populate the listbox with all the dates from 'Master Log'. Check which item of the listbox was selected. But, of course, for someone really green with Excel that is a lot to learn. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parameter Input for Goal Seek | Excel Discussion (Misc queries) | |||
Importing external data with cell value as input parameter | New Users to Excel | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Discussion (Misc queries) | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Worksheet Functions | |||
Input to populate report worksheet | Excel Worksheet Functions |