Select Case test String is failing.

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:

Dim uiValue As String
    uiValue = uF_Helper.TextBox1.Text

uiValue = Replace(uiValue, ", ", """, """)

Dim wS As Worksheet
    
    For Each wS In Worksheets
        Select Case wS.Name
            Case "Sheet1", "Sheet2", "Sheet3"
                     'Do Nothing
            Case uiValue
            Stop
        Case Else
                     'Do Something

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.

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:

Dim TestString      As String
    For Each wS In Worksheets
     TestString = wS.Name
        Select Case TestString

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

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

Try

    uiValue = uF_Helper.TextBox1.Txt
    For Each wS In Worksheets
        Select Case True
            Case wS.Name = "Sheet1", wS.Name = "Sheet2", wS.Name = "Sheet3"
            Case InStr("," & uiValue & ",", "," & wS.Name & ",")
                MsgBox wS.Name
            Case Else
                ' do somwthing else
        End Select
    Next

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.

Option Explicit

Sub SimplifiedDemonstrationOfFaultyCaseConstruction()
  'This fails because the 'Instr() Case' is doing an evaluation
  'It looks like it should work, but apparently Excel VBA compiles the construction to a constant
  '
  'Make 'SheetX' the Active Sheet

  Dim uiValue As String
  
  uiValue = "SheetX"
  
  Sheets("SheetX").Activate
  
  Select Case ActiveSheet.Name
  
    Case "Sheet1", "Sheet2", "Sheet3"
      'Do Nothing
      MsgBox "Sheet1, Sheet2, Sheet3"
      
    Case InStr("," & uiValue & ",", "," & ActiveSheet.Name & ",")
      MsgBox "SheetX success: uivalue=" & uiValue
      
    Case Else
      MsgBox "Case Failure - 'SheetX' branch should have been selected"

  End Select

End Sub

Sub ReplaceCaseStatementWithCompoundIf()

  Dim uiValue As String
  
  uiValue = "SheetX"
  
  Sheets("SheetX").Activate
  
  If ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then
    'Do Nothing
    MsgBox "Sheet1, Sheet2, Sheet3"
      
  ElseIf InStr("," & uiValue & ",", "," & ActiveSheet.Name & ",") Then
    MsgBox "SheetX success: uivalue=" & uiValue
      
  Else
    MsgBox "if Failure - 'SheetX' branch should have been selected"

  End If

End Sub

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:

Option Explicit

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

Lewis

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:

Sub AddCaseSta() 'http://www.cpearson.com/excel/vbe.aspx

Dim VBProj      As VBIDE.VBProject
Dim VBComp      As VBIDE.VBComponent
Dim CodeMod     As VBIDE.CodeModule
Dim LineNum     As Long

Dim uiValue    As String

    uiValue = uF_Helper.TextBox1.Text
uiValue = Replace(uiValue, ", ", """, """)
If Right(uiValue, 1) <> """" Then uiValue = uiValue & """"
If Left(uiValue, 1) <> """" Then uiValue = """" & uiValue

 Set VBProj = Workbooks("Test.XLSB").VBProject 
 Set VBComp = VBProj.VBComponents("uF_Helper") 
Set CodeMod = VBComp.CodeModule

   With CodeMod
            LineNum = 48
            .InsertLines LineNum, "            Case " & uiValue
        End With
    End Sub

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

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.

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

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.