Solved: Len function problem

[tqm1]

Dear Sir,

I have following values

Me.TextBox17.Value=0
Me.ComboBox1.Value=1
Me.ComboBox2.Value=1
Me.ComboBox3.Value=1

If length of above textbox and comboboxes will be greater than 1 then following message should appaer

MsgBox ("All Codes are greater than 0")

In other case if the length of any box will be 0 then following message should appaer

MsgBox ("Enter correct Pary + Product + Vehicle Codes")

I use following codes


Private Sub cmdWeight_Click()
If Len(Me.TextBox17.Value > 0) And Len(Me.ComboBox1.Value > 0) _
And Len(Me.ComboBox2.Value > 0) And Len(Me.ComboBox4.Value > 0) Then
MsgBox ("All Codes are greater than 0")
Else
MsgBox ("Enter correct Party + Product + Vehicle Codes")
End If
End Sub

But it always displays
MsgBox ("All Codes are greater than 0")

What is wrong please help

[Bob Phillips]

Private Sub cmdWeight_Click()
If Len(Me.TextBox17.Value) > 0 And Len(Me.ComboBox1.Value) > 0 _
And Len(Me.ComboBox2.Value) > 0 And Len(Me.ComboBox4.Value) > 0 Then
MsgBox ("All Codes are greater than 0")
Else
MsgBox ("Enter correct Party + Product + Vehicle Codes")
End If
End Sub

[mdmackillop]

You're mixing up text and values. Textbox17 = 0 has a text value of "0" which has a length of 1.

[Simon Lloyd]

Malcom, it may be because he has asked a question here at this thread http://www.excelforum.com/showthread.php?p=1797357#post1797357 and followed the link provided that may have led to his confusion!

[mdmackillop]

Thanks Simon

[mikerickson]

This will test if a positive numeral is entered in all four of the controls
With Me
If Val(.TextBox17.Value) > 0 And Val(.ComboBox2.Value) > 0 _
And Val(.ComboBox2.Value) > 0 And Val(.ComboBox2.Value) > 0 Then
MsgBox ("All Codes are greater than 0")
Else
MsgBox ("Enter correct Party + Product + Vehicle Codes")
End If
End With

[tqm1]

Dear Sir,

I have done as you said but the situation is still same.

All comboboxes and a textbox is filled with data as

Texbox17.value="Brazil"
combobox1.value=1
combobox1.value=2
combobox1.value=4

and when I apply following codes

With Me
If Val(.TextBox17.Value) > 0 And Val(.ComboBox2.Value) > 0 _
And Val(.ComboBox2.Value) > 0 And Val(.ComboBox2.Value) > 0 Then
MsgBox ("All Codes are greater than 0")
Else
MsgBox ("Enter correct Party + Product + Vehicle Codes")
End If
End With

then this messagebox appears

MsgBox ("Enter correct Party + Product + Vehicle Codes")

Although all boxes are filled with data then what is wrong?
Please see attached sheet

[tqm1]

Very Sorry,
I forget to attach sheet.
Sorry again

[mikerickson]

Misplaced parenthesis, this should do it.

Private Sub CommandButton1_Click()
If Val(Me.TextBox17.Value) > 0 And Val(Me.ComboBox1.Value) > 0 _
And Val(Me.ComboBox2.Value) > 0 And Val(Me.ComboBox4.Value) > 0 Then
MsgBox ("Greater Than 0")
Else
MsgBox ("Not Greater Than 0")
End If
End Sub

[tqm1]

Dear Sir,

Texbox contains String value and all Comboboxes holds numeric
So your codes still not working.

Please modify in Attached sheet.

Thanks

[Aussiebear]

Are you still cross posting tqm1?

[Bob Phillips]

I think I answered the question in #2 of the thread.

[mdmackillop]

I think I answered the question in #2 of the thread.
You're doing better than I. I don't understand the question any longer! Is Brazil > "0"? Maybe when they are playing against England, but I can't factor it in to VBA.

[Bob Phillips]

In the words, he keep saying length.

[johnske]

At a guess, I don't think he needs to use Len at all i.e. something along these lines...


Private Sub cmdWeight_Click()
If Me.TextBox17.Value > 0 And Me.ComboBox1.Value > 0 _
And Me.ComboBox2.Value > 0 And Me.ComboBox4.Value > 0 Then
MsgBox ("All Codes are greater than 0")
Else
MsgBox ("Enter correct Party + Product + Vehicle Codes")
End If
End Sub

[tqm1]

My dear all GREAT PROFESSORS

Thanks for participating to solvout my problem.
But unfortunately that is unsolved.

If you do not mind, could I ask .... why do you not apply your codes on my attached sheet?

Thanks

[johnske]

I'm puzzled as to what the intended outcome is. Do you want to test if entries have been made in all 4 fields, or, only that an entry was made in at least one field? (In that case you only need to see if the sum of all 4 fields is > 0).

[Aussiebear]

My guess is that you need to remove the "val" words and try this instead.

Private Sub CommandButton1_Click()
If (Me.TextBox17.Value > 0) And (Me.ComboBox1.Value > 0) _
And (Me.ComboBox2.Value > 0) And (Me.ComboBox4.Value > 0) Then
MsgBox ("Greater Than 0")
Else
MsgBox ("Not Greater Than 0")
End If
End Sub

I find that this works.

[Aussiebear]

If there's a problem here it is that until you enter the default values it continues to offer the msg "Not greater than 0". You can enter a value of 1 in all but one of the textboxes and it will still return the msg "Not greater than 0".

Brings the question to mind, does tqm1 require this message to be shown if any of the values is greater than 1 or if the sum of the values is greater than 1, and if so what relevance has a text value got to do with a numerical test?

[tqm1]

My guess is that you need to remove the "val" words and try this instead.

Private Sub CommandButton1_Click()
If (Me.TextBox17.Value > 0) And (Me.ComboBox1.Value > 0) _
And (Me.ComboBox2.Value > 0) And (Me.ComboBox4.Value > 0) Then
MsgBox ("Greater Than 0")
Else
MsgBox ("Not Greater Than 0")
End If
End Sub

I find that this works.


While using above codes, if textox17 remains empty then it says

MsgBox ("Greater Than 0")


I explain more what I need

Case 1:
If textbox17, combobox1,combox2 and combo4 are filled then it must say
MsgBox ("Greater Than 0")

Case 2:
If one or more of them is empty then it should display
MsgBox ("Not Greater Than 0")

Thanks

[johnske]

... I explain more what I need

Case 1:
If textbox17, combobox1,combox2 and combo4 are filled then it must say
MsgBox ("Greater Than 0")

Case 2:
If one or more of them is empty then it should display
MsgBox ("Not Greater Than 0")

ThanksSeems to me that XLD has answered this in post #2. The UserForm_Initialize is making entries in all four of your fields, so you will never get the other message ("Not Greater Than 0"). If you scrub the initialize procedure you'll find it should do what you want - here's an alternative solution using Or logic instead of And (and different messages)
Private Sub CommandButton1_Click()
With Me
If Not (.TextBox17 = "" Or .ComboBox1 = "" _
Or .ComboBox2 = "" Or .ComboBox4 = "") Then
MsgBox ("All fields have entries")
Else
MsgBox ("Some empty fields")
End If
End With
End Sub
or if you prefer...
Private Sub CommandButton1_Click()
With Me
If .TextBox17 <> Empty And .ComboBox1 <> Empty _
And .ComboBox2 <> Empty And .ComboBox4 <> Empty Then
MsgBox ("All fields have entries")
Else
MsgBox ("Some empty fields")
End If
End With
End Sub

[Aussiebear]

Fair enough.... that should help confuse us mere mortals

[mdmackillop]

You've not shown us any of your attempts since the first, but I would suggest that you just turn what you've written into a line of code, such as If Me.TextBox17.Value = "0" Or Me.TextBox17.Value = "" Then Test = False
and apply it to the other controls as well.

[Aussiebear]

[/vba]
I explain more what I need

Case 1:
If textbox17, combobox1,combox2 and combo4 are filled then it must say
MsgBox ("Greater Than 0")

Case 2:
If one or more of them is empty then it should display
MsgBox ("Not Greater Than 0")

Thanks

So its not "length" that you require as you first asked, and it seems that its not "Greater than 0" nor is it "Not Greater than 0" since it now appears you simply need a message when ever one of the boxes are not filled.

Is this correct?

No wonder the "Great Professors" are stumped....:chat:


Righto listen up all you F Troop members.. we are moving to a new game plan.

Johnske.... I want you to play wide reciever( and no offside play either). Bob, you and I will wait here on the front line, stiff upper lipp and all that British stuff. MD, you circle round the back of them thar hills, and when you see the smoke, come a'running. I want to see them barrells glowing from 50 mtrs away.

And the rest of you, well you just fill in time cause I'm sure we are going to need question breakers, code sorters and the like... :beerchug:

[mdmackillop]

Great plan. 25 posts and 2 pages, and we don't have a "Solved" yet.
If we can win this battle, we might end the Hundred Years War, but watch out for a flanking move!

[Aussiebear]

No worries, I've got Johnskie out there riding flank.