Using a string to reverse fill Textboxes on a userform

Hi all,

I have a two part question.

I've got a VBA userform with 5 text boxes. When a user selects the cell the userform shows and I want the text boxes to fill each text box from a string in the cell which has a "-" as a seperator.

E.g say the string was "Item 1 - Item 2 - Item 3 - Item 4 - Item 5"

Userform1 Textbox1 would equal the string Item 1
Userform1 Textbox2 would equal the string Item 2 etc

The below works but wondered.

1) Is there a better way to do this.
2) How to count the number of "-" with using the Evaluate Method and a sumproduct formula which I'm doing at the moment

Please Login or Register  to view this content.

VBA Noob

Hello VBA Noob,

I am not sure I understand what you want. The part about loading the TextBoxes is clear. The counting of "-" part is a bit unclear. Do you want to count all "-" within a range of cells?

The counting of "-" part is a bit unclear. Do you want to count all "-" within a range of cells?

Hi Leith,

The string should contain 4 "-" in total so was really afer some way to build in validation of the string.

I've managed to use the Evaluate Method and a sumproduct formula to validate the string but was wondering if there was a built in VBA function I've forgetten about\overlooked or a better way

Hopefully it's a bit clearer.

VBA Noob

Thanks karan but wanted to validate the string has 4 "-" before loading the userform Textboxes.

e.g I'm replacing this

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

VBA Noob

Hello VBA Noob,

This will validate that are at least 4 hyphens in the string. If you want to test for exactly 4 then it would require testing with RegExp.

Please Login or Register  to view this content.

Thanks karan.

Should of thought of doing that myself

Please Login or Register  to view this content.

Thanks again

VBA Noob

Originally Posted by Leith Ross

Hello VBA Noob,

This will validate that are at least 4 hyphens in the string. If you want to test for exactly 4 then it would require testing with RegExp.

Please Login or Register  to view this content.

Thanks Leith. Works a treat as always.

Thanks both for the help and input


VBA Noob

Please Login or Register  to view this content.

Thanks shg for the input

Better than my method

Please Login or Register  to view this content.

VBA Noob