[Nigel Cummins]

Thanks for your code Tom. I did not have a clue where to start.

I went with another reply by Dave Peterson:
----------------
First, some housekeeping:

I used cells A1, B1, C1 to get the WMI code.
I used D1 for the message cell

I added a worksheet named "WMI Table" that consisted of the codes in column
A
and the description in column B.

Then I dropped a button from the Forms toolbar on the sheet with WMI input
cells.

I assigned it this macro:
Option Explicit
Sub testme()

Dim myWMI As String
Dim testWks As Worksheet
Dim WMILookupTable As Range
Dim res As Variant
Dim msgCell As Range

With Worksheets("wmi table")
Set WMILookupTable = .Range("a1:b" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With ActiveSheet
Set msgCell = .Range("D1")
myWMI = .Range("a1").Value & .Range("b1").Value & .Range("c1").Value

Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(myWMI)
On Error GoTo 0

If testWks Is Nothing Then
res = Application.VLookup(myWMI, WMILookupTable, 2, False)
If IsError(res) Then
msgCell.Value = myWMI & " is not defined"
Else
msgCell.Value = myWMI & "-" & res & " is not defined"
End If
Else
msgCell.ClearContents
Application.Goto testWks.Range("a1"), scroll:=True
End If
End With

End Sub
--------------

I liked your idea of including a validation to ensure only three characters
were entered using the Len function.

I want to include data validation into the sub. For example:

If Len(myWMI) = (does not equal three) Then
display
MsgBox "Please enter only three characters"
stop procedure (eg. exit sub)

how do I do that. I want it to check the data (myWMI) and if it does not
equal three then stop the procedure and where do I include that in the sub?



"Tom Ogilvy" wrote in message
...
Assume list of WMI is in M1 to M50 and decoded name is N1 to N50
on same sheet as entry is being made.

Assume entries are made in A1:C1

Private Sub CommandButton1_Click()
Dim sWMI As String
Dim wksh As Worksheet
Dim rng As Range
Dim res As Variant
sWMI = Trim(Range("A1").Value) & Trim(Range("B1").Value) & _
Trim(Range("C1").Value)
If Len(sWMI) < 3 Then
MsgBox "Please reenter choice in A1:C1"
Exit Sub
End If
On Error Resume Next
Set wksh = Worksheets(sWMI)
On Error GoTo 0
If Not rng Is Nothing Then
Application.Goto wksh.Range("A1"), True
Else
res = Application.VLookup(sWMI, Range("M1:N50"), 2, False)
If Not IsError(res) Then
MsgBox sWMI & " " & res & " is not on decoder"
Else
MsgBox sWMI & " is not recognized by decoder"
End If
End If
End Sub

--
Regards,
Tom Ogilvy





Nigel Cummins wrote in
message
...
I am trying to write a workbook that will decode Vehicle Identification
Numbers VIN. The first three characters of every VIN has the World
Manufacturer Identifier WMI. I am creating a worksheet specific to each
manufacturer named by their WMI that will decode their VINs. eg.. Ford
Australia has a WMI of 6FP I created a worksheet named '6FP'.

I need a procedure that when users enter the three character WMI and
click
on search (command button) will activate the worksheet (named from the
WMI).

I would like to define what the WMI means (manufacturer name) for
popular
makes that I haven't done a worksheet for yet eg.. 6T1 means Toyota
Australia. If user inputs 6T1 I would like it to return in the active
sheet
in a cell eg.. "6T1 Toyota Australia is not on decoder".

Else for any other WMI not defined (null). Return WMI is not recognised
by
decoder.

In Summary : click on command button in active sheet

1. needs to add three cells together (6 , F, P) to create worksheet name
(6FP)
2. goto to that worksheet eg.. worksheet '6FP'.activate
3. for defined WMI with no worksheet (eg. 6T1) needs to display in
active
sheet not on decoder
4. for any other undefined WMI (null) needs to display in active sheet
not
recognised by decoder


Can anyone help me?