Tuesday, July 17, 2018

Userforms, TexBoxes & Condition Matching in Excel VBA

Suppose,

TextBox1.Value = 10
and
TextBox2.Value = 10

also, if you pass this following coding it gives the wrong result.

If TextBox1.value = TextBox2.value then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

You will defiantly get the second message and that is, "No, They are not Equal!"

So why does it happens? In Excel VBA, when you are dealing with Userforms, sometimes work demands an use of conditional matching criteria. Just like, IF the value of textbox1 is equal to textbox2 then do something. just like that.

So, Textboxes have always stored values entered in it as String Format. yes even if it's number or not. So, when do we matching the two textboxes based on numbers then we will surely not get the correct result.

So there are two ways to get rid of this.

1. Use Val function
2. Store the Value of TextBoxes into the Worksheet Range.

1. Use of Val Function

If Val(TextBox1.value) = Val(TextBox2.Value) then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

This time you will receive the First Message.

2. Let's look at this.

'Store the Value of Textboxes into the cell. 
Sheets("Sheet1").Range("A1").Value = TextBox1.value
Sheets("Sheet1").Range("A2").Value = TextBox2.value

If Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A2").Value then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

The result will be the same, the first one. It's because of Excel automatically recognize the DataType of the Data entered in the cell. So It will become more accurate and also solves your purpose.

These are the small things. many are knowing and many don't. Who doesn't it's for them. 

Thank you!

Kamal Bharakhda







No comments:

Post a Comment

IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not? Rem : List of Characters Group - ASCII Rem ...