displaying cells on a form

[masanovic]

Can anyone help. Im trying to search a sheet and display results on a form using labels in excel 2000. Ive nearly done it!!! but am gettin an error. Can anyone please see if they can get it workin. The code is:

Private Sub CommandButton1_Click()

Dim fnd As Range ' this will be the samew as your entry in TextBox1
Dim tbl As Range ' this will be the table to search

Set tbl = Sheet1.Range("A2"). CurrentRegion

'run the search
Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
'if the item is not found the user is informed and the textbox cleared
If fnd Is Nothing Then
MsgBox "No match found!"
TextBox1.Value = ""

Exit Sub
'if the item is found the details will be copied to the labels
Else: fnd.Activate
End If

'now put the information stored adjacent to the found item into label1,etc
Label1.Caption = fnd.Offset(0, 1).Value
Label2.Caption = fnd.Offset(0, 2).Value 'and so on
End Sub

 

[PHV]

am gettin an error
Which error on which line ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

"...but am gettin an error"

What error and what statment?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[masanovic]

My apologies. Getting the following error:

Compile error:
Named argument not found

It is highlighting the following code - SearchFormat:=False)

 

[PHV]

What happens if you use this instead ?
Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masanovic]

Another error im afraid.

Unable to get the Find property of the Range class.

I will send you the file if that makes life easier?

 

[PHV]

You may try to replace this:
Set tbl = Sheet1.Range("A2"). CurrentRegion
with this:
Set tbl = ActiveWorkbook.Worksheets("Sheet1").Range("A2"). CurrentRegion

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masanovic]

same error as previous reply. Its beginning to annoy me now! lol

 

[PHV]

Your code is really in an UserForm loaded in an Excel VBAProject ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masanovic]

This is all my code

Private Sub CommandButton1_Click()

Dim fnd As Range ' this will be the samew as your entry in TextBox1
Dim tbl As Range ' this will be the table to search

Set tbl = Sheet1.Range("A2").CurrentRegion

'run the search
Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
'if the item is not found the user is informed and the textbox cleared
If fnd Is Nothing Then
MsgBox "No match found!"
TextBox1.Value = ""

Exit Sub
'if the item is found the details will be copied to the labels
Else: fnd.Activate
End If

'now put the information stored adjacent to the found item into label1,etc
Label1.Caption = fnd.Offset(0, 1).Value
Label2.Caption = fnd.Offset(0, 2).Value 'and so on
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub

 

[SkipVought]

I don't recommend using the Select Method and ActiveCell, but try this...

'run the search
[A1].Select
Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[masanovic]

Ive done it now. But if there is more than one match it only displays the first match. Do you know how I can have a next button that will display any further matches. My code now is:

Private Sub CommandButton1_Click()

Dim fnd As Variant ' this will be the samew as your entry in TextBox1
Dim tbl As Variant ' this will be the table to search

Set tbl = Sheet1.Range("A2").CurrentRegion

'run the search
Set fnd = tbl.Find(What:=Me.TextBox1.Value, LookIn:=xlValues)
'if the item is not found the user is informed and the textbox cleared
If fnd Is Nothing Then
MsgBox "No match found!"
TextBox1.Value = ""

Exit Sub
'if the item is found the details will be copied to the labels
Else: fnd.Activate
End If

'now put the information stored adjacent to the found item into label1,etc
Label1.Caption = fnd.Offset(0, 1).Value
Label2.Caption = fnd.Offset(0, 2).Value 'and so on
End Sub

 

[PHV]

Place the cursor inside the word Find in your code and the press the F1 key: you'll see how to use the FindNext method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masanovic]

Yeh ive looked in the help files, struggling though. Any help would be greatly appreciated

 

[PHV]

So, what is YOUR actual code using the FindNext method ?

 

[masanovic]

I havent got any for findnext, got no idea how to incoporate it into my code. at the moment it just displays first match