Having problems getting a "Select Case" function to work properly

[jameslytle]

I am having a problem getting the "Select Case" part of my VBA to work. The Case "ERAText.Value" is provided from another UserForm and is a value of 1, 2 or 3. In the case of 1 and 2 I want it to run the section listed under it which is to show a text box and list the value of "Human" in it. This seems to work just fine.

However, If I wanted to the case 3 to work where it would show a Combo Box showing a list to select from with the default value of "Human" showing up first. When "3" is selected it acts as if the first Case Option "< 3" was selected and only shows the text box with "Human" in it.

What am I doing wrong?

Thanks in advance for any assistance.

**************************************
Select Case ERAText.Value
Case Is < "3"
Me.PhenoText.Visible = True
Me.PhenoCombo.Visible = False
With PhenoText
.Value = "Human"
End With
Case Is = "3"
Me.PhenoCombo.Visible = True
Me.PhenoText.Visible = False
With PhenoCombo
.List = Range("PhenoType").Value
.Value = "Human"
End With
End Select
*******************************


Jim

 

[Fluff]

What happened after you clicked Ok on the message box?
That code does not use either = or <

 

[Joe4]

It returned a false which I am assuming means it is text. Hence the fact that it will work when I put the quotes on and not without them. It still does not explain why it does not seem to recognize the Case = and only recognize the case <.

That does not seem to make sense.
If it is Text, and "1","2", and "3" are the only possible values, then the solution that Fluff posted in post 3 should work.
You can put a MsgBox in each Case option, to confirm that it is really hitting the correct ones (or not), i.e.

Dim ERA
ERA = ERAText.Value

Select Case ERA
   Case "1", "2"
      MsgBox "Value is " & ERA & " and entered 1st Case clause"
      Me.PhenoText.Visible = True
      Me.PhenoCombo.Visible = False
      With PhenoText
         .Value = "Human"
      End With
   Case "3"
      MsgBox "Value is " & ERA & " and entered 2nd Case clause"
      Me.PhenoCombo.Visible = True
      Me.PhenoText.Visible = False
      With PhenoCombo
         .List = Range("PhenoType").Value
         .Value = "Human"
      End With
End Select

(of course, you could also just add breakpoints and step through your code to see exactly what it is doing)

 

[jameslytle]

Fluff,

What msg box?

You said to change the "Select Case ERAText.Value" to read "Select Case Val(ERAText.Value)

 

[Fluff]

I was referring to the code Joe posted in post#5 and you replied to in post#9

 

[jameslytle]

OK Lets try this again.

I went back and reintroduced the code from post # 5 and put in the code with the MSG boxes in each case.

When I run it and try to select Case "1" the MSG box shows "Value Is" but does not list a value and then it shows "Is it Numeric? False"

When I click OK it brings up the next UserForm but the "textBox"/"ComboBox" show up overlapped with nothing within (As if it does not recognize the case entry at all.

After trying it that way I changed the Case back putting in the "<" and the "=" again and got the same information listed above but then I also got the text message listed in the Case option "< 3" even if I selected that which should have went to the "= 3". Which means it always only goes to the "< 3" option no matter what the ERAText.value is.

 

[Fluff]

OK, I missed this

The Case "ERAText.Value" is provided from another UserForm

in your op.
Is the code with the Select case in the userform module containing the ERAText box?
If not how are you passing that value to the other userform?

 

[jameslytle]

In the previous UserForm (named BioMetricsA) I had this code within "NextButton_Click" sub-routine.

Select Case Me.ERAList.ListIndex
Case -1
MsgBox "Please Select an ERA from the list"
BioMetricsA.Show
Case 0
Sheets("CharData_" & Me.CharLastText.Text).Range("B2") = 1
BioMetricsB.ERAText = 1
Case 1
Sheets("CharData_" & Me.CharLastText.Text).Range("B2") = 2
BioMetricsB.ERAText = 2
Case 2
Sheets("CharData_" & Me.CharLastText.Text).Range("B2") = 3
BioMetricsB.ERAText = 3

This enters a value in the ERAText textbox on BioMetricsB depending on what selection is made in the Select Case function.

 

[Fluff]

Ok, when the BioMetricsB userform opens can you see the value in the ERAText box?

 

[jameslytle]

Yes. I shows the number correlating to the selection made in BioMetricsA.

 

[Fluff]

What happens if you try this?

Select Case Val(ERAtext.Value)
MsgBox (ERAtext.Value)
Case Is < 3
Me.PhenoText.Visible = True
Me.PhenoCombo.Visible = False
With PhenoText
.Value = "Human"
End With
Case Is = 3
Me.PhenoCombo.Visible = True
Me.PhenoText.Visible = False
With PhenoCombo
.List = Range("PhenoType").Value
.Value = "Human"
End With
End Select