Combo Box not working

[Damian37]

Hello all,
I'm trying to set up a combo box to use a list of values entered in to another worksheet. I have named the worksheet StoreType, and I'm using the following code:

Private Sub StoreType_Change()
Dim sType As Range
Dim WS As Worksheet
Set WS = Worksheets("StoreType")

For Each sType In WS.Range("StoreType")
    With Me.StoreType
        .AddItem sType.Value
        .List(.ListCount - 1, 1) = sType.Offset(0, 1).Value
    End With
Next sType
End Sub

When I open the UserForm and click on the drop down arrow, there are no values. I've also gone in to Define Name section within Excel, and have added the following information:

Name: StoreType
Scope: StoreType

Refers to: =Offset(StoreType!$A$2,0,0,CountA(StoreType!$A$A)-1,1)

Any assistance with this is greatly appreciated. Thank you.

D.

 

[Anthony47]

From what I understand, you can remove the Sub StoreType_Change and only set the property RowSource of the Combobox to =storetype

Next time you should use different names for different items, so it will be immediately clear that I am referring to the "StoreType the Named Range"

PS: I don't know if the Scope of your StoreType is correct or not, but be sure to set it to Workbook (I doubt you named it ...Storetype.xlsm)

Bye

 

[dmt32]

Hi,
Try replacing your code with following & see if helps

Private Sub UserForm_Initialize()
    Dim WS As Worksheet
    
    Set WS = Worksheets("StoreType")

    With Me.StoreType
        .RowSource = ""
        .ColumnCount = 2
        .List = WS.Range("StoreType").Value
    End With
End Sub

Dave

 

[Damian37]

From what I understand, you can remove the Sub StoreType_Change and only set the property RowSource of the Combobox to =storetype

Next time you should use different names for different items, so it will be immediately clear that I am referring to the "StoreType the Named Range"

PS: I don't know if the Scope of your StoreType is correct or not, but be sure to set it to Workbook (I doubt you named it ...Storetype.xlsm)

Bye

Hi Anthony,
I'm a little confused. Are you saying I don't need the following code:

Private Sub StoreType_Change()
Dim sType As Range
Dim WS As Worksheet
Set WS = Worksheets("StoreType")

For Each sType In WS.Range("StoreType")
    With Me.StoreType
        .AddItem sType.Value
        .List(.ListCount - 1, 1) = sType.Offset(0, 1).Value
    End With
Next sType
End Sub

I'm very visual, and I'm unsure how I would use RowSource to reference the combo box to the list I created within the worksheet "StoreType", because right now when I click on the drop down arrow the only thing I see is a blank drop down.

I created a combo box before, however I hard coded the values rather than referencing a list within a worksheet. This is how I wrote it in that instance.

Private Sub cboDirectIndirect_Change()
If cboDirectIndirect.Value = "Indirect" Then
    txtAmt.Enabled = False
Else
    txtAmt.Enabled = True
End If
If cboDirectIndirect.Value = "Direct" Then
MsgBox ("Over/(Short) Amount Box cannot be left Blank")
End If

However, I've never referenced a list within a different worksheet.

 

[Damian37]

Hi,
Try replacing your code with following & see if helps

Private Sub UserForm_Initialize()
    Dim WS As Worksheet
   
    Set WS = Worksheets("StoreType")

    With Me.StoreType
        .RowSource = ""
        .ColumnCount = 2
        .List = WS.Range("StoreType").Value
    End With
End Sub

Dave

Hi Dave,
I tried replacing my code with the code you provided, however, I'm still getting a blank drop down box when I click on the arrow in the userform.

 

[dmt32]

Hi Dave,
I tried replacing my code with the code you provided, however, I'm still getting a blank drop down box when I click on the arrow in the userform.

Did you delete the old code & have you use my code as published or have you made any changes?

Dave

 

[Damian37]

Did you delete the old code & have you use my code as published or have you made any changes?

Dave

Yes, I removed my old code, and replaced it with yours. I didn't make any changes to your code. Should I have?

 

[Anthony47]

Combobox have the property RowSource, and you can use it to specify which area have to be displayed; if the named range StoreType correctly points to the list you wish to use then you can forget about the code for doing that. To set that property, go to the userform vba page, select the combobox, search the Property in the "Property window" and type =StoreType as the property value. If you don't see the Property window, then pressing F4 (within the vba environment) should do the job.
Make sure that the StoreType (named range) scope be the workbook, not a worksheet.

Bye

 

[dmt32]

Yes, I removed my old code, and replaced it with yours. I didn't make any changes to your code. Should I have?

In that case, then code should work - just made a test form & works ok for me

Just to be certain, have you renamed the UserForm_Initialize event?

Dave

 

[Damian37]

Combobox have the property RowSource, and you can use it to specify which area have to be displayed; if the named range StoreType correctly points to the list you wish to use then you can forget about the code for doing that. To set that property, go to the userform vba page, select the combobox, search the Property in the "Property window" and type =StoreType as the property value. If you don't see the Property window, then pressing F4 (within the vba environment) should do the job.
Make sure that the StoreType (named range) scope be the workbook, not a worksheet.

Bye

Within the property "Name" section does reference "StoreType" , I don't see anything that is labeled as Property Window, unless that is the Name section. And I updated the scope to reference Workbook. Really not sure why this isn't working. Everything looks good, but it when I open the userform and click on the drop down arrow nothing shows up. I'm stumped.