Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default VBA button click need help. Need LEN help.

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?












  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default VBA button click need help. Need LEN help.

1) REading the code I see that the test you asked for IS
there already!


2) why not make this a function?
Function testme(text)

Dim WMILookupTable As Range
Dim res As Variant

Application.Volatile

If Len(text) < 3 Then
testme = text & ": is not 3 letters"
Exit Function
End If

With Worksheets("wmi table")
Set WMILookupTable = _
.Range("a1:b" & .Cells(.Rows.Count, _
"A").End(xlUp).Row)
End With
On Error Resume Next
res = Application.VLookup(text, _
WMILookupTable, 2, False)
If IsError(res) Then
testme = text & " is not defined"
Else
testme = text & "=" & res
End If
On Error GoTo 0
End Function

in D1
=Testme( a1 & b1 & c1 )


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
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 <ncummins-nospam@delete-this-

bit.bigpond.com 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?












.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default VBA button click need help. Need LEN help.

1) REading the code I see that the test you asked for IS
there already!


You must be more astute than me. I don't see any check in Dave's code for
three characters.

Regards,
Tom Ogilvy



"Patrick Molloy" wrote in message
...
1) REading the code I see that the test you asked for IS
there already!


2) why not make this a function?
Function testme(text)

Dim WMILookupTable As Range
Dim res As Variant

Application.Volatile

If Len(text) < 3 Then
testme = text & ": is not 3 letters"
Exit Function
End If

With Worksheets("wmi table")
Set WMILookupTable = _
.Range("a1:b" & .Cells(.Rows.Count, _
"A").End(xlUp).Row)
End With
On Error Resume Next
res = Application.VLookup(text, _
WMILookupTable, 2, False)
If IsError(res) Then
testme = text & " is not defined"
Else
testme = text & "=" & res
End If
On Error GoTo 0
End Function

in D1
=Testme( a1 & b1 & c1 )


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
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 <ncummins-nospam@delete-this-

bit.bigpond.com 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?












.



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 103
Default VBA button click need help. Need LEN help.

Well I didn't say HUS code, but if you scroll down you'll see it in, er YOUR code !
I was wondering why the question was asked when the code had been supplied.

best regards

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Tom Ogilvy" wrote in message ...
1) REading the code I see that the test you asked for IS
there already!


You must be more astute than me. I don't see any check in Dave's code for
three characters.

Regards,
Tom Ogilvy



"Patrick Molloy" wrote in message
...
1) REading the code I see that the test you asked for IS
there already!


2) why not make this a function?
Function testme(text)

Dim WMILookupTable As Range
Dim res As Variant

Application.Volatile

If Len(text) < 3 Then
testme = text & ": is not 3 letters"
Exit Function
End If

With Worksheets("wmi table")
Set WMILookupTable = _
.Range("a1:b" & .Cells(.Rows.Count, _
"A").End(xlUp).Row)
End With
On Error Resume Next
res = Application.VLookup(text, _
WMILookupTable, 2, False)
If IsError(res) Then
testme = text & " is not defined"
Else
testme = text & "=" & res
End If
On Error GoTo 0
End Function

in D1
=Testme( a1 & b1 & c1 )


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
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 <ncummins-nospam@delete-this-

bit.bigpond.com 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?












.



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default VBA button click need help. Need LEN help.

He said he used Dave's code, but wondered how to add the check for 3
characters from my code to Dave's code. So when you responded that it was
already in there, it looked like you were saying it was included already in
Dave's code.

Anyway, to the original poster, replace

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

Using the using the following


With ActiveSheet
Set msgCell = .Range("D1")
msg = ""
' check if values in each cell
if application.counta(Range("A1:C1")) < 3 then
msg = "Cells A1, B1, C1 must be filled"
else
myWMI = Trim(.Range("a1").Value) & _
Trim(.Range("b1").Value) & Trim(.Range("c1").Value)
if len(myWMI) < 3 then
msg = "Cells A1, B1, C1 must contain only one character"
End if
End if
if msg < "" then
msgbox msg
exit sub
end if

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

And below Testme() add the declaration

Dim msg As String


--
Regards,
Tom Ogilvy

"Patrick Molloy" wrote in message
...
Well I didn't say HUS code, but if you scroll down you'll see it in, er YOUR
code !
I was wondering why the question was asked when the code had been supplied.

best regards

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Tom Ogilvy" wrote in message
...
1) REading the code I see that the test you asked for IS
there already!


You must be more astute than me. I don't see any check in Dave's code for
three characters.

Regards,
Tom Ogilvy



"Patrick Molloy" wrote in message
...
1) REading the code I see that the test you asked for IS
there already!


2) why not make this a function?
Function testme(text)

Dim WMILookupTable As Range
Dim res As Variant

Application.Volatile

If Len(text) < 3 Then
testme = text & ": is not 3 letters"
Exit Function
End If

With Worksheets("wmi table")
Set WMILookupTable = _
.Range("a1:b" & .Cells(.Rows.Count, _
"A").End(xlUp).Row)
End With
On Error Resume Next
res = Application.VLookup(text, _
WMILookupTable, 2, False)
If IsError(res) Then
testme = text & " is not defined"
Else
testme = text & "=" & res
End If
On Error GoTo 0
End Function

in D1
=Testme( a1 & b1 & c1 )


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
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 <ncummins-nospam@delete-this-

bit.bigpond.com 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?












.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
click a button create an X Captain Eddie Excel Worksheet Functions 1 July 31st 09 08:01 PM
how can i add $ to formulas with a click of a button to ? hgood Excel Discussion (Misc queries) 2 November 12th 08 10:24 PM
Command Button Click bmolintas Excel Discussion (Misc queries) 4 November 8th 07 11:37 PM
Click button to automatically add row yirawan Excel Worksheet Functions 1 November 24th 05 05:26 PM
VBA button click need help. Need LEN help. Nigel Cummins Excel Programming 0 July 11th 03 09:53 AM


All times are GMT +1. The time now is 02:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"