Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA (Excel XP)

    Attached is the code that allows me to enter a telephone number through a control button and when the number is found in the Excel spreadsheet, the line with the number is highlighted. This is a modification of a code that I believe I found on your site.

    What I would like to do is have the control button on a separate sheet withing the workbook in order that the end user would only be working with a 'blank' sheet. That is, he/she would not see all the inforamtion entered, only the information pertaining to the telephone that was entered. I thought that I could modify the line Range("D")... to something like Range ("Sheet2! D")....., but everytime I enter the phone number I get the message about not being able to find the phone number.

    What would the correct syntax be in order to have the results show up on the sheet where I placed the button?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel VBA (Excel XP)

    Similar result

    Sub FindNum()
    Dim MyTel
    Dim Rng As Range
    MyTel = InputBox("Enter the Tel # you are looking for.")

    If MyTel = "" Or MyTel = vbCancel Then GoTo CloseOut

    On Error Resume Next

    Set Rng = Worksheets("Sheet2").Range("D").Find(What:=MyTel).Select
    Worksheets("sheet2").Activate

    CloseOut:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox "Unable to find the Telephone number.", vbOKOnly, ""
    GoTo CloseOut
    End Sub
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel XP)

    I entered your code and still get the same result.

    I have attached a sample of what I am attempting to accomplish. If you look at Sheet 1 and click on the button to enter one of the sample numbers, you should see the row with the number highlighted. If you go to sheet 2, click on the button and enter a number you get the message about not being able to find the number.

    HAven't yet tried Han's suggestion. Will get to it later.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel XP)

    If I understand you correctly, you have a command button on another worksheet (say Sheet2) that executes the FindNum macro. If the user enters a telephone number that is found, you want to switch to Sheet1 and select the telephone number. This could be accomplished by changing FindNum as follows:

    Sub FindNum()
    Dim MyTel As String
    Dim rng As Range

    On Error GoTo ErrHandler

    MyTel = InputBox("Enter the Tel # you are looking for.")
    If MyTel = "" Then Exit Sub
    Set rng = Worksheets("Sheet1").Range("D").Find(What:=MyTel)
    If rng Is Nothing Then Err.Raise 1
    Worksheets("Sheet1").Activate
    rng.Select

    CloseOut:
    Set rng = Nothing
    Exit Sub

    ErrHandler:
    MsgBox "Unable to find the Telephone number."
    Resume CloseOut
    End Sub

    If this is not what you intend, please provide more details.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel XP)

    Thanks Hans. I tried your suggestion and still get the same result. I have attached a simplified example to my reply to Jezza earlier. You might want to take a look at it to see what I trying to accomplish.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel VBA (Excel XP)

    Try adding this line:
    Worksheets("Sheet1").Activate
    Before this line:
    Range("Sheet1!D").Find(What:=MyPart).Select

    so the sheet is selected.
    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel XP)

    You changed the wrong macro. You had two copies of FindNum in your workbook - one in the Sheet1 worksheet module and one in the general module Module1. The latter one is the one you call from Sheet2.

    See attached workbook.

    Note: you must enter (for example) 5559876, not 555-9876, since your phone numbers are real numbers, formatted as a phone number. The stored value does not contain a hyphen.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel XP)

    Thanks, Hans. One other thing though. Is it possible to have the result show on sheet 2? In other words, you enter the telephone number, it gets the information from sheet 1 and puts it on sheet 2. It wouldn't need the highlighting of the row, just the information.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel XP)

    1) Do you want to copy the entire row from Sheet1 to Sheet2?
    2) Where do you want to copy the information to? Always the same location (overwriting previous search results), or in a new row each time?

  10. #10
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel XP)

    The entire row would be copied and always to the same location overwriting the existing information.

    Thanks.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel XP)

    Try this variation on the previous code:

    Sub FindNum()
    Dim MyTel As String
    Dim rng As Range

    On Error GoTo ErrHandler

    MyTel = InputBox("Enter the Tel # you are looking for.")
    If MyTel = "" Then Exit Sub
    Set rng = Worksheets("Sheet1").Range("D").Find(What:=MyTel, _
    LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then Err.Raise 1
    Worksheets("Sheet1").Rows(rng.Row).Copy _
    Destination:=Worksheets("Sheet2").Rows(5)

    CloseOut:
    Set rng = Nothing
    Exit Sub

    ErrHandler:
    MsgBox "Unable to find the Telephone number."
    Resume CloseOut
    End Sub

Posting Permissions

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