New to Excel programming - ?s on find, select and inputbox

[LauriS]

Running 2000 - here's what I'm trying to do. I want an input box to appear
when the file is opened, prompting user for a date. Once they enter a date I
to search column A for the matching date then set the active cell to that
cell. I also want it to make sure they enter a valid date and if they don't
- go back to the input box and let them try again.

Here's what I have so far after reading through many of the great posts
already out here:

***************************************
Private Sub Workbook_Open()
Dim EnterDate As Date
Dim GoHere As Range

On Error GoTo addError

EnterDate = InputBox("Enter the Month and Year for the data being
entered (mm/yy)", "Enter MM/YY format")

Set GoHere = Columns("A").Find(what:=EnterDate, LookIn:=xlValues)

GoHere.Select

Exit Sub

addError:

MsgBox "This is not a date."

End Sub
*****************************

Two problems.

1. The code, as it is, goes to the error section even when I enter a valid
date. When I try to use the immediate window to check the value of GoHere I
get the message: Runtime error 91: Object variable or With block variable
not set. I don't understand why I'm getting that message.

2. If they don't enter a valid date it displays the error message and
stops. How do I make it go back to the input box and allow them to try again
(and again and again if necessary)?

As always, thanks for any help you can offer!!

Lauri S.

 

[Tom Ogilvy]

Find is pretty flaky with Dates. I don't generally use it for that:

Private Sub Workbook_Open()
Dim EnterDate As Date
Dim GoHere As Range

On Error GoTo addError

EnterDate = InputBox( _
"Enter the Month and Year for the data" & _
" being entered (mm/yy)", _
"Enter MM/YY format")

Set rng = Columns(1).Cells
res = Application.Match(CLng(EnterDate), rng, 0)
If Not IsError(res) Then
Set GoHere = rng(res)
GoHere.Select
Else
MsgBox EnterDate & " not found"
End If

Exit Sub

addError:

MsgBox "This is not a date."
Resume
End Sub

Also, if you are not in a region that uses the US date format, you might
have troubles as well.

If that is the case, try changing to

EnterDate = cDate(InputBox( _
"Enter the Month and Year for the data" & _
" being entered (mm/yy)", _
"Enter MM/YY format"))

 

[Jim Thomlinson]

Finding Dates can be a bit tricky in XL but assuming your dates are actual
dates and you are not using systems with different date formats then this
should work... Note I changed the input to mm/yyyy as this is a little more
foolproof...

Private Sub Workbook_Open()
Dim varEnterDate As Variant
Dim rngGoHere As Range

varEnterDate = InputBox("Enter the Month and Year for the data " & _
"being entered (mm/yyyy)", "Enter MM/YYYY format")
Do While Not IsDate(varEnterDate) And varEnterDate <> ""
MsgBox "The value entered is not a date. Please try again."
varEnterDate = InputBox("Enter the Month and Year for the data " & _
"being entered (mm/yyyy)", "Enter MM/YYYY format")
Loop

If varEnterDate = "" Then
MsgBox "User Cancelled"
Else
varEnterDate = CDate(varEnterDate)
On Error Resume Next
Set rngGoHere = Columns("A").Find(what:=varEnterDate,
LookIn:=xlValues)
On Error GoTo 0

If rngGoHere Is Nothing Then
MsgBox "Sorry that date was not found"
Else
rngGoHere.Select
End If
End If
End Sub

 

[Tom Ogilvy]

Just a heads up to the OP,

In Jim's suggestion,

On Error Resume Next
Set rngGoHere = Columns("A").Find(what:=varEnterDate,
LookIn:=xlValues)
On Error GoTo 0


the error handling isn't needed. Find returns nothing if nothing is found,
so it doesn't produce an error.

 

[Jim Thomlinson]

Thanks Tom... Brain Fart at my end... It won't hurt but it sure won't help.

 

[LauriS]

Thanks to both of you!!

I'll check out both suggestions and I'm sure I'll get it done (and learn
something in the process).

Lauri S.

 

[Tom Ogilvy]

Probably took me years to remember that - I am a slow learner.