Select Case test String is failing.

[dlow]

Hi,

I am trying to test some text that is entered via a textBox in a userForm through a Case Statement.

Its not working.
Hers what Im doing:

Please Login or Register  to view this content.

In the textBox I enter some text:
Dog, Cat, Snake

Using Replace I am inserting the Quotes it should have, but its not hitting the Stop.
What am I doing wrong?

TIA for your help.

[:) Sixthsense :)]

In your code you just matching the Worksheet Names with your case conditions.

So your worksheet name and case condition both should match to run the given task.

[dlow]

Hi,

Thanks.

Maybe Im not following what youre saying. It sounds like what youre telling me is that the test need to be of like variables/items
So here is what I tried:

Please Login or Register  to view this content.

That didnt work, so maybe Im not following what youre saying or I didnt do it right.
Could you throw me another bone on this?
TIA

[dlow]

This is what it looks like in the Locals window
Expression________Value__________Type
uiValue : "CAT", "DOG", "GOAT" : String

[mgs73]

As far as VBA is concerned, uiValue is a single string - not three strings separated by commas. I see what you are trying to do, but I don't think there is a way to make that work.

This might work for you:

Please Login or Register  to view this content.

[jindon]

Try

Please Login or Register  to view this content.

[LJMetzger]

Hi dlow,

What we are dealing with is a feature of Excel 'Case Statements'. Branches of 'Case Statements' should be constants and not evaluations, since Excel VBA apparently compiles the 'Case' branches as constants.

The 'Select Case' construction should probably be replaced with a 'Compound If' construction.

See the following example macros that are included in the attached file.

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

[dlow]

Hi,

Wow thanks for the outpouring of help gang, I appreciate you.

Yes mgs73 I think you are correct & from what our friend LJMetzger shared its confirmed. Thanks Lewis for sharing the info about the Branches needing to be constants.

So, what I ended up doing was just use the VBIDE to write the line of code & at the end remove/clean up that line.
It works great!
Heres what that looks like:

Please Login or Register  to view this content.

Thanks again everybody for your help; & sharing of your time, & knowledge.

[Norie]

Why are you writing to the VBE?

What, in words, are you actually trying to do?

[dlow]

Hi Norie,

I have a textBox that a user inputs some Sheet.Names.
In my Module the caseStatement deletes unprotected sheets.
I was trying to protect the users desired sheet.Names that were entered into text box.
It wasnt working in my CaseStatement & thats what I was asking for help with here.

Lewis AKA [LJMetzger] offered this insight.

Branches of 'Case Statements' should be constants and not evaluations

Thats why I am using the VBIDE to write to the Sub before its runs creating the string as a constant & at the end I call another Sub to remove the line that was added.

Do you have a more elegant solution? Im open to ideas.

[Norie]

You could split the string of sheet names entered by the user to create an array then use Application.Match to check if a sheet name is one of those entered by the user.

Please Login or Register  to view this content.

[StuCram]

Just a note about SELECT & CASE

You can test a number of logical decisions if there isn't any variable at the start, just 'True"
The first case decision that matches "True" will be the one that is acted on.

Here's a simple example….

Please Login or Register  to view this content.

So I suspect something similar would work in your case. (ha ha, no pun intended.)

[dlow]

Hmm, ok

mgs73 also suggested that & I tried it, but it didnt work. Could be I didnt try hard enough to see why it didnt work.
I am gonna try it again thanks Norie & mgs73.

[dlow]

Hi thanks StuCram. I didnt even think to try True as the condition. I will check that out as well.

[Norie]

mgs73 did suggest using Split but the rest of the code was quite different to what I'm suggesting.