inkserious
New Member
Hello,
Is it possible to use select case to evaluate the value in a ComboBox against a range? I have six named ranges. One range has 30 three digit values in it. The other five ranges contain several of the numbers in the first range. I've posted some code below to illustrate what I'm trying to achieve. It doesn't work in it's current state. I hope someone can help out with this one.
Thanks.
-ep
'Private Sub cboTable_Change()
Dim tNum As Range
Dim g As String
Dim aTables As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
Set aTables = ws.Range("allTables")
For Each tNum In aTables
Select Case tNum.Value
Case ws.Range("bjTables"): g = "BJ" 'if tNum.Value = any of the values in the range "bjTables" then "BJ"
Case ws.Range("crTables"): g = "CR"
Case ws.Range("roTables"): g = "RO"
Case ws.Range("ppTables"): g = "PP"
Case ws.Range("msTables"): g = "MS"
Case Else: g = ""
End Select
Next tNum
Me.cboGames.Value = g
End Sub`
Is it possible to use select case to evaluate the value in a ComboBox against a range? I have six named ranges. One range has 30 three digit values in it. The other five ranges contain several of the numbers in the first range. I've posted some code below to illustrate what I'm trying to achieve. It doesn't work in it's current state. I hope someone can help out with this one.
Thanks.
-ep
'Private Sub cboTable_Change()
Dim tNum As Range
Dim g As String
Dim aTables As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
Set aTables = ws.Range("allTables")
For Each tNum In aTables
Select Case tNum.Value
Case ws.Range("bjTables"): g = "BJ" 'if tNum.Value = any of the values in the range "bjTables" then "BJ"
Case ws.Range("crTables"): g = "CR"
Case ws.Range("roTables"): g = "RO"
Case ws.Range("ppTables"): g = "PP"
Case ws.Range("msTables"): g = "MS"
Case Else: g = ""
End Select
Next tNum
Me.cboGames.Value = g
End Sub`