Userform lookup date value in database
 

Hi,

Trying to resolve a problem when trying to populate TextBox field
after data has been entered into TextBox1 field in a Userform.

The data entered in TextBox1 is a date dd/mm/yyyy and is stored i
sheet Dbase as "Text" ( have tried many different options Date
General, Text ) but continue to either not match it or get erro
messages "Type mismatch Error 13 at code Loc = TextBox1.Value )

My code is as follows : ( any suggestions )

Private Sub TextBox1_AfterUpdate()
Dim Loc As Long
Sheets("Menu").Select
res = Application.Match(TextBox1.Value
Worksheets("Dbase").Range("A:A"), 0)
If IsError(res) Then
Exit Sub
Else
End If
Loc = TextBox1.Value
With Worksheets("Dbase")
Set C = .Range("A:A").Find(Loc, LookIn:=xlValues)
TextBox2 = .Cells(C.Row, 2)
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)
TextBox6 = .Cells(C.Row, 5)
TextBox7 = .Cells(C.Row, 6)
TextBox8 = .Cells(C.Row, 7)
TextBox9 = .Cells(C.Row, 8)
TextBox10 = .Cells(C.Row, 9)
TextBox11 = .Cells(C.Row, 10)
TextBox13 = .Cells(C.Row, 12)

If .Cells(C.Row, 11) = "F" Then
OptionButton2.Value = True
OptionButton3.Value = False
Else
OptionButton3.Value = True
OptionButton2.Value = False
End If

End With

End Sub

Cheers,
Bern

--
Bernz
-----------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...fo&userid=2294
View this thread: http://www.excelforum.com/showthread.php?threadid=37694

Userform lookup date value in database
 

Maybe just declaring Loc as String would be enough?

Are you sure your dates in column A of dBase are really stored as Text?

if
=isnumber(a1)
returns true, then a1 isn't text (no matter what the format shows).




BernzG wrote:

Hi,

Trying to resolve a problem when trying to populate TextBox fields
after data has been entered into TextBox1 field in a Userform.

The data entered in TextBox1 is a date dd/mm/yyyy and is stored in
sheet Dbase as "Text" ( have tried many different options Date.
General, Text ) but continue to either not match it or get error
messages "Type mismatch Error 13 at code Loc = TextBox1.Value )

My code is as follows : ( any suggestions )

Private Sub TextBox1_AfterUpdate()
Dim Loc As Long
Sheets("Menu").Select
res = Application.Match(TextBox1.Value,
Worksheets("Dbase").Range("A:A"), 0)
If IsError(res) Then
Exit Sub
Else
End If
Loc = TextBox1.Value
With Worksheets("Dbase")
Set C = .Range("A:A").Find(Loc, LookIn:=xlValues)
TextBox2 = .Cells(C.Row, 2)
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)
TextBox6 = .Cells(C.Row, 5)
TextBox7 = .Cells(C.Row, 6)
TextBox8 = .Cells(C.Row, 7)
TextBox9 = .Cells(C.Row, 8)
TextBox10 = .Cells(C.Row, 9)
TextBox11 = .Cells(C.Row, 10)
TextBox13 = .Cells(C.Row, 12)

If .Cells(C.Row, 11) = "F" Then
OptionButton2.Value = True
OptionButton3.Value = False
Else
OptionButton3.Value = True
OptionButton2.Value = False
End If

End With

End Sub

Cheers,
Bernz

--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=376941

--

Dave Peterson

Userform lookup date value in database
 

Hi Dave,

Thanks for this - I thought I had tried this before and it didn't work
however, it now does.

Yes the test on "text' on the field with IsNumber turned out to b
False.

I have just realised that to do any report calcultions within th
spreadsheet itself the date column must be stored as a date and no
text.

I have some ideas that I will try out first and if unsuccessful wil
get back to you.

Thanks for your help.

Cheers
Bernz

PS sorry about the delay in responding but I could not get back int
the forum for a while

:) :) :

--
Bernz
-----------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...fo&userid=2294
View this thread: http://www.excelforum.com/showthread.php?threadid=37694