If/Then statements with three outcomes

Garrek

Board Regular
Joined
Aug 22, 2019
Messages
53
Hey all, I have a Userform with textboxes in it. A user can input up to two characters in each. How the values are formatted is dependent on how many characters there are i.e. Empty Textboxes are handled one way, single characters ("A") are handled another and 2 letters ("AB") are handled a separate way. Is there any easy way to sort these?

Right now I have the following code
Code:
If TB.Value = "" Then
Set xxxxxxxxxx
Else
Set xxxxxxx
End If

This takes care of empty text boxes and non empty ones, but I'm hoping to find a way to incorporate single vs 2 initial.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sure, use a Case statement, i.e.
Code:
Select Case Len(TB.Value)
    Case 0
        Set XXX
    Case 1
        Set YYY
    Case 2
        Set ZZZ
    Case Else
        MsgBox "Error!"
End Select
See here for more information: https://www.techonthenet.com/excel/formulas/case.php


Is there a good way to quickly identify how many characters are in the textbox? I used TB.Value = "" to identify empty textboxes, but obviously that won't work for trying to differentiate between 1 and 2 character inputs.
 
Upvote 0
Is there a good way to quickly identify how many characters are in the textbox?
Yes, and I already did it in my previous reply:
Code:
Select Case [COLOR=#0000ff]Len(TB.Value)[/COLOR]
The LEN function checks the length (number of characters) of the value entry in your "TB" textbox.

Try it out for yourself and see!
 
Last edited:
Upvote 0
Yes, and I already did it in my previous reply:
Code:
Select Case [COLOR=#0000ff]Len(TB.Value)[/COLOR]
The LEN function checks the length (number of characters) of the value entry in your "TB" textbox.

Try it out for yourself and see!


Missed that. Thank you! Working great so far.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top