0

I have three worksheets:

Contacts

Column A (Supplier Name)    Column B      Column C
Mars                        Name          Email
Marstons
General Mills
General UK

Data

Cell I2

Delivery Tracker

Column E (Supplier Name)

Mars
...
...

Basically i've built a VBA code, that once a supplier name has been typed in column E on delivery tracker sheet, the code searches for a similar matching supplier name from column A on Contacts sheet. It then pulls up the matching contact number, email and fax for that supplier name.

It then places these contact details in cell I2 on Data sheet.

A shape object is then displayed on delivery tracker sheet pulling through these contact details from cell I2.

Here is my code:

'Start Phone Book Directory Code
Dim Contact As String
Dim Email As String
Dim Phone As String
Dim Fax As String

Application.EnableEvents = False 'to prevent endless loop
On Error GoTo Finalize 'to re-enable the events

If Not Intersect(Target, ThisWorkbook.Worksheets(1).Range("F" & ActiveCell.Row)) Is Nothing Then 'Main IF
ThisWorkbook.Worksheets(1).Shapes("Suggest").Visible = False
ThisWorkbook.Worksheets(1).Shapes("Close").Visible = False
Application.EnableEvents = True
'ActiveSheet.Unprotect Password:="SecretPassword"
Else
If ThisWorkbook.Worksheets(1).Range("E" & ActiveCell.Row).Value = "" Or ThisWorkbook.Worksheets(1).Range("F" & ActiveCell.Row).Value <> "" Then ' Secondary iF
ThisWorkbook.Worksheets(1).Shapes("Suggest").Visible = False
ThisWorkbook.Worksheets(1).Shapes("Close").Visible = False
Application.EnableEvents = True
'ActiveSheet.Unprotect Password:="SecretPassword"
Else


    'Start FIND
Dim c As Variant

With Worksheets("Contacts").Range("A1:A10000")
Set c = .Find("*" & ActiveCell.Offset(0, -1).Value & "*", LookIn:=xlValues)
If c Is Nothing Then

'Introduce FailSafe, escape code if no result found
ThisWorkbook.Worksheets(1).Shapes("Suggest").Visible = False
ThisWorkbook.Worksheets(1).Shapes("Close").Visible = False
'ActiveSheet.Unprotect Password:="SecretPassword"


Else

'Check values are not blank
If c.Offset(0, 1).Value <> "" Then
Contact = "Contact: " & c.Offset(0, 1).Value & Chr(10)
Else
Contact = "Contact: No Contact Held" & Chr(10)
End If

If c.Offset(0, 2).Value <> "" Then
Email = "Email: " & c.Offset(0, 2).Value & Chr(10)
Else
Email = "Email: No Email Held" & Chr(10)
End If

If c.Offset(0, 3).Value <> "" Then
Phone = "Phone: " & c.Offset(0, 3).Value & Chr(10)
Else
Phone = "Phone: No Phone Held" & Chr(10)
End If

If c.Offset(0, 4).Value <> "" Then
Fax = "Fax: " & c.Offset(0, 4).Value
Else
Fax = "Fax: No Fax Held"
End If


'Show Contacts
ThisWorkbook.Worksheets("Data").Range("I2").Value = "Hello," & vbNewLine & "Have you tried to contact " & c.Value & " about your issue?" & vbNewLine _
& Contact & Email & Phone & Fax

'ThisWorkbook.Worksheets(1).Shapes("Suggest").TextFrame.AutoSize = True
CenterShape ThisWorkbook.Worksheets(1).Shapes("Suggest")
RightShape ThisWorkbook.Worksheets(1).Shapes("Close")
ThisWorkbook.Worksheets(1).Shapes("Suggest").Visible = True

'Show Close Button
ThisWorkbook.Worksheets(1).Shapes("Close").Visible = True






End If
End With

End If ' End Main If
End If ' End Secondary If



'Error Handling

Finalize:
Application.EnableEvents = True

This works, except If the user types in Mars then contact details for Marstons is shown.

This is wrong. The code needs to use wildcards because sometimes the user may type Mars Ltd or Mars Uk and this should match Mars (but not Marstons)

Please can someone show me a better way of doing this?

user7415328
  • 1,053
  • 5
  • 24
  • 61

0 Answers0