VBA Select Case with Named Range does not work

Question

I am trying to insert a new row into a named range. The user selects a "category" from a combo box e.g., Cool Drinks, Beer and Cider, Bitters etc... and then the contents of that category populate another combo box.

I have named the ranges of all of the Categories and would like them to populate the second combo box. I have a code which works by itself:

Dim rng As Range
Dim DailySales As Worksheet
Set DailySales = Worksheets("Daily Sales")

Set rng = DailySales.Range("CoolDrinksDailySales")
For Each rng In DailySales.Range("CoolDrinksDailySales")
Me.CmboName.AddItem rng.Value
Next rng

However, whenever I try to use that in a Select Case, it doesn't work.

Dim rng As Range
Dim DailySales As Worksheet
Set DailySales = Worksheets("Daily Sales")
Select Case Me.CmboType.Value
Case "Cool Drinks"
    Set rng = DailySales.Range("CoolDrinksDailySales")
    For Each rng In DailySales.Range("CoolDrinksDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
Case "Beer and Cider"
    Set rng = DailySales.Range("BeerCiderDailySales")
    For Each rng In DailySales.Range("BeerCiderDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
End Select

Does anybody have any ideas?

Here is the complete code:

Option Explicit
Private Sub UserForm_Initialize()
'InitializeTypeCombo
Dim Types() As String
Types = Split("Cool Drinks,Beer and    
Cider,Bitters,Brandy,Whiskey,Rum,Spirits,Sherry,White Wine,Red Wine", 
",")

Dim i As Integer
For i = LBound(Types) To UBound(Types)
Me.CmboType.AddItem Types(i)
Next

'InitializeNameCombo

Dim rng As Range
Dim DailySales As Worksheet
Set DailySales = Worksheets("Daily Sales")
 Select Case Me.CmboType.Value
Case "Cool Drinks"
    Set rng = DailySales.Range("CoolDrinksDailySales")
    For Each rng In DailySales.Range("CoolDrinksDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
Case "Beer and Cider"
    Set rng = DailySales.Range("BeerCiderDailySales")
    For Each rng In DailySales.Range("BeerCiderDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
End Select
End Sub

 Private Sub CmdExit_Click()
 Unload Me
 End Sub
Private Sub CmdEnter_Click()
Dim rng As Range

'Store Date Index
Dim colArray(32) As Integer

'Store Item Index
Dim rowArray(150) As Integer

'Store first value for Find and FindNext
Dim FirstAddress As String

Dim i As Integer
Dim j As Integer
i = 0
j = 0

With Range("B6:AD6")
Set rng = .Find(TxtDate.Value, LookIn:=xlValues)
If Not rng Is Nothing Then
    FirstAddress = rng.Address       
    Do
        Set rng = .FindNext(rng)
        colArray(i) = rng.Column
        i = i + 1            
        Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
End With

With Range("A7:A150")
Set rng = .Find(CmboName.Value, LookIn:=xlValues)
If Not rng Is Nothing Then
    FirstAddress = rng.Address        
    Do
        Set rng = .FindNext(rng)
        rowArray(j) = rng.Row
        j = j + 1            
        Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
End With

Dim c As Integer
Dim r As Integer

For c = 0 To i - 1
For r = 0 To j - 1
    Cells(rowArray(r), colArray(c)).Value = TxtNoSold.Value
Next r
Next c
Unload Me
End Sub

Hello, delete this line Set rng = DailySales.Range("CoolDrinksDailySales") and see what happens — Davesexcel
Yes, if it worked for the 1st one, would you not do the same for the second one? — Davesexcel
Is the code even running? How are you calling it? Put a break point (F9) on the Select Case line and step (F8) through it to see what's happening. — Dick Kusleika
It appears your Select Case is in the Initialize event. At the time it runs, the value of the combobox is nothing - no Case statement will execute. I would think you want that code in the combobox_Change event so that every time the combobox changes, the code executes. — Dick Kusleika

Meringue90 Meringue90 · Accepted Answer · 2015-05-27T15:53:11

The solution was simply moving the Select Case into the Combobox_Change event. As Dick Kusleika said, the value of the combobox was nothing at runtime. Here is the correct code to accomplish what I was trying to do.

Option Explicit
Private Sub Userform_Initialize()
'Populate cmboTypes
Dim Types() As String
Types = Split("Cool Drinks,Beer and _
    Cider,Bitters,Brandy,Whiskey,Rum,Spirits,Sherry,White Wine,_
    Red Wine", ",")

 'Loop through the values populated in the split function above, and add 
 'each item to the combobox
Dim i As Integer
For i = LBound(Types) To UBound(Types)
Me.CmboType.AddItem Types(i)
Next
End Sub


Sub CmboType_Change()

Dim rng As Range
Dim DailySales As Worksheet

'Populate CmboName with named dynamic ranges of "Types"

Set DailySales = Worksheets("Daily Sales")
 Select Case Me.CmboType.Value
   Case "Cool Drinks"
    Set rng = DailySales.Range("CoolDrinksDailySales")
    For Each rng In DailySales.Range("CoolDrinksDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
   Case "Beer and Cider"
    CmboName.Clear
    Set rng = DailySales.Range("BeerCiderDailySales")
    For Each rng In DailySales.Range("BeerCiderDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
   Case "Bitters"
    CmboName.Clear
    Set rng = DailySales.Range("BittersDailySales")
    For Each rng In DailySales.Range("BittersDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
  Case "Brandy"
    CmboName.Clear
    Set rng = DailySales.Range("BrandyDailySales")
    For Each rng In DailySales.Range("BrandyDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
  Case "Whiskey"
    CmboName.Clear
    Set rng = DailySales.Range("WhiskeyDailySales")
    For Each rng In DailySales.Range("WhiskeyDailySales")
    Me.CmboName.AddItem rng.Value
    Next rng
End Select
End Sub

VBA Select Case with Named Range does not work

1 Answers