Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: Displaying cells in a form

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    2 things I see that may be causing a problem. I havn't ran this to check but suspect the way the range is defined and not using the reference to the Userform would cause problems. Although because this is in the Form module it does not require an explicit reference to the form. you can refer to the form as "Me" when used in the Form Module.

    Here's a quick edit to try
    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:A50") ' or whatever size your range is
    'run the search
    Set fnd = tbl.Find(What:=TextBox1.Value, After:=Sheet1.Range("A2"), 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!"  ''' Or skip this and put it in the TextBox
    Me.TextBox1.Value = "No match found!" 
    Exit Sub 
    'if the item is found the details will be copied to the labels
    Else
     fnd.Activate ' use 2 lines vrs the ":" colon after Else
    End If 
    
    'now put the information stored adjacent to the found item into label1,etc
    Me.Label1.Caption = fnd.Offset(0, 1).Value 
    Me.Label2.Caption = fnd.Offset(0, 2).Value 'and so on
    End Sub
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •