Message box or Inputbox or what?????

[rollo]

I have a macro which when activated goes through a sequence of formatting and auto filtering procedures in a spreadsheet.
One of the filters is on a column containing dates. Currently to filter for a different range of dates I have to go into the macro and edit the date concerned. Ideally I would like a box (Input box or Message box?) to appear at some stage while the macro is running asking for the date or range of dates required. (In the same way that a parameter query operates in Access.)
Has anyone any ideas, please?

[Juan Pablo González]

A MessageBox is designed to inform the user about something or ask like a Yes/No question.

An InputBox is designed to ask the user specific info, which can't be obtained using a MessageBox.

So, for your question, I would believe it would be an Input Box

[Dinictus]

Right,

Create a userform, which you popup where you want in your macro. Use the input in the userform textbox to indicate which range you want to format or whatever you want to do with it.

goodluck.

[Dave Hawley]

Hi Rollo


I would say the best thing would be if it were possible to show the "Custom Filter" xldialog. Unfortunately I don't think it can be shown.

Input boxes will also do it.


Sub GetDates()
Dim stDateSrt As String
Dim stDateEnd As String

On Error Resume Next
stDateSrt = Application.InputBox _
(Prompt:="Select or type the Start date", Title:="DATE", Default:=Date, Type:=2)
If stDateSrt = "False" Or stDateSrt = "" Then Exit Sub

If Not IsDate(CDate(stDateSrt)) Then
MsgBox "Invalid date", vbCritical
Run "GetDates"
End If

SecondDate:
stDateEnd = Application.InputBox _
(Prompt:="Select or type the End date", Title:="DATE", Default:=CDate(stDateSrt) + 21, Type:=2)
If stDateEnd = "False" Or stDateEnd = "" Then Exit Sub

If Not IsDate(CDate(stDateEnd)) Then
MsgBox "Invalid date", vbCritical
GoTo SecondDate
End If

MsgBox stDateSrt & " " & stDateEnd
On Error GoTo 0
End Sub

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-25 08:10 ]

[rollo]

Thanks to all for the posts ...... and to Dave for the time spent on your response. All much appreciated. May well have to call back for further help! (Don't go too far away!!!!!)
Thanks, again
rollo