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.

 

[Damian37]

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

Hi Dave,
When I look at the properties for the UserForm, it shows the name as UserForm1, but the caption is AddRecord. Currently my code is this:

Private Sub UserForm1_Initialize()

Dim WS As Worksheet
Set WS = Worksheets("StoreType")

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

Should I change UserForm1_Initialize() to AddRecord_Initialize()?

 

[Anthony47]

(click to expand)

Here it is my vba editor
(1) is the combobox
(2) is the "Property window"; if you don't see it, press F4, or Menu /View /Property window
(3) here it is the RowSource property, that I set to =StoreType (the named range)

If you still wish to use the code, then you should modify your .List = WS.Range("StoreType").Value to

.List = Application.WorksheetFunction.Transpose(WS.Range("StoreType").Value)

Bye

 

[dmt32]

I did ask earlier if you had made ANY changes to the code and you told be you had not - yet clearly from above, you have RENAMED the event code I published to UserForm1_Initialize which you must not do.

Please Use the code as I published it without ANY changes

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