VBA button click need help. Need LEN help.

VBA button click need help. Need LEN help.

Post by Nigel Cumm » Sat, 12 Jul 2003 17:53:07

hanks 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" < XXXX@XXXXX.COM > wrote in message
news:# XXXX@XXXXX.COM ...
message
click
popular
by
active
not



 
 
 

VBA button click need help. Need LEN help.

Post by Nigel Cumm » Sat, 12 Jul 2003 17:54:11

hanks 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" < XXXX@XXXXX.COM > wrote in message
news:# XXXX@XXXXX.COM ...
message
click
popular
by
active
not





 
 
 

VBA button click need help. Need LEN help.

Post by Patrick Mo » Sat, 12 Jul 2003 20:52:44

) 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

start.
the codes in column
sheet with WMI input
(.Rows.Count, "A").End(xlUp).Row)
& .Range("c1").Value
WMILookupTable, 2, False)
not defined"
scroll:=True
only three characters
example:
and if it does not
include that in the sub?
N1 to N50
("B1").Value) & _
False)
bit.bigpond.com> wrote in
Vehicle Identification
has the World
worksheet specific to each
their VINs. eg.. Ford
named '6FP'.
character WMI and
worksheet (named from the
(manufacturer name) for
6T1 means Toyota
return in the active
decoder".
WMI is not recognised
create worksheet name
worksheet '6FP'.activate
to display in
display in active sheet
 
 
 

VBA button click need help. Need LEN help.

Post by Tom Ogilv » Sun, 13 Jul 2003 04:52:02

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" < XXXX@XXXXX.COM > wrote in message
news:O8BpQC% XXXX@XXXXX.COM ...
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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...