"Type Mismatch" picking Select Case from ComboBox

matonanjin
NewLounger
Posts: 4
Joined: 01 Aug 2023, 21:54

"Type Mismatch" picking Select Case from ComboBox

Post by matonanjin »

I hope my post title was descriptive, prolly not.

I am picking from a combobox which takes the list index to use in a Selct Case. That selection determines what is stuck in a cell. There are 11 choices in the combobox. That choice goes to this Select Case:

Code: Select all

  Select Case ComboBox2.ListIndex         'condition of vinyl   DOWN THE ROAD IF FORMAT IS NOT VINYL I SHOULD NOT HAVE THESE CHOICES???
        Case 0
            Range("f" & intRowNo) = "M"
         Case 1
            Range("f" & intRowNo) = "NM"
        Case 2
            Range("f" & intRowNo) = "VG+"
        Case 3
            Range("f" & intRowNo) = "E"
        Case 4
            Range("f" & intRowNo) = "VG"
        Case 5
            Range("f" & intRowNo) = "G"
        Case 6
            Range("f" & intRowNo) = "G+"
        Case 7
            Range("f" & intRowNo) = "G-"
        Case 8
            Range("f" & intRowNo) = "P"
        Case 9
            Range("f" + intRowNo) = "F"
        Case 10
            Range("f" + intRowNo) = "S"
  End Select

As you can see, the Case determines what is stuck in the Row intRowNo Column F.

Everything works just spiffy if I choose Case 0 through 8. If I choose 9 or 10 I get the "Type Mismatch". Any idea why the last two choices are disliked by VBA?

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: "Type Mismatch" picking Select Case from ComboBox

Post by HansV »

Why did you use + instead of & for those two? I suspect it will work if you change

Code: Select all

        Case 9
            Range("f" + intRowNo) = "F"
        Case 10
            Range("f" + intRowNo) = "S"
to

Code: Select all

        Case 9
            Range("f" & intRowNo) = "F"
        Case 10
            Range("f" & intRowNo) = "S"
By the way, I'd store the values in a (hidden) column of the list box, for example in the second column. You can then use

Code: Select all

    If ComboBox2.ListIndex <> -1 Then
        Range("F" & intRowNo).Value = ComboBox2.Column(1)
(Columns start counting at 0, so Column(1) is the 2nd column)
Best wishes,
Hans

matonanjin
NewLounger
Posts: 4
Joined: 01 Aug 2023, 21:54

Re: "Type Mismatch" picking Select Case from ComboBox

Post by matonanjin »

Why did I use + instead of &?!? Isn't it obvious? I don't have an effing clue what I am doing. That is why. 😉

Store the value in a hidden column of the list box? To make sure I understand. So if I click on the choice of the combobox that will pull the (hidden) choice and actually eliminate the need for the entire Select Case, wouldn't it? Sheez. Embarrassingly obvious.

Thank you for the help and suggestion.

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: "Type Mismatch" picking Select Case from ComboBox

Post by HansV »

matonanjin wrote:
05 Aug 2023, 21:34
Store the value in a hidden column of the list box? To make sure I understand. So if I click on the choice of the combobox that will pull the (hidden) choice and actually eliminate the need for the entire Select Case, wouldn't it?
Yep!
Best wishes,
Hans

matonanjin
NewLounger
Posts: 4
Joined: 01 Aug 2023, 21:54

Re: "Type Mismatch" picking Select Case from ComboBox

Post by matonanjin »

HansV wrote:
05 Aug 2023, 21:46

Yep!
Hans,
Thank you again for taking the time to look at my code and respond with your helpful suggestion/correction. Know that it is appreciated. As you suspected, making the change did immediately resolve my problem. And I am going to go back and change the combobox to hold the hidden column which will be much cleaner.

snb
5StarLounger
Posts: 606
Joined: 14 Nov 2012, 16:06

Re: "Type Mismatch" picking Select Case from ComboBox

Post by snb »

This is all you need:

Code: Select all

cells(intRowNo,6) =choose(ComboBox2.ListIndex+1,"M","NM","VG+","E","VG","G","G+","G-","P","F","S")
or

Code: Select all

cells(intRowNo,6) =Arraty("M","NM","VG+","E","VG","G","G+","G-","P","F","S")(ComboBox2.ListIndex)
or

Code: Select all

cells(intRowNo,6) =Split(,"M NM VG+ E VG G G+ G- P F S")(ComboBox2.ListIndex)