Combobox List from Column, remove blanks

[SimondsJM]

Hello,

I am trying to populate my combobox1 from Column B starting at B7 and going down but there are blanks in between that I do not want to show up in the list. How can I remove the blanks?

 

[SimondsJM]

No errors, just blank combobox.

If it helps this is Excel 2002/XP

There is more than one Userform, but only 1 combobox on that particular form. I have renamed Combobox1 on that form to CBox1, but modified code to match.

Is there a possibility that maybe i have clicked something in the properties that is messing things up?

 

[AlphaFrog]

You say you have two userforms. Is the UserForm_Initialize code in the correct userform module? It should be in the same userform module that has the userform with combobox?

It always helps to know the version, but in this case it shouldn't matter.

 

[SimondsJM]

Im confused when you say UserForm Module?
I have done nothing with any Modules.
Do you mean the code for the UserForm in question?

OK so the Sheet is: Sheet2

Form is: NewCourse

ComboBox is: CBox1

The Data that I am trying to Populate in the ComboBox (CBox1) is on Sheet2 Column B from B7 down, minus the blank spaces in between.

So Far the code you gave me doesn't seem to be populating the ComboBox (CBox1)

 

[AlphaFrog]

Im confused when you say UserForm Module?
I have done nothing with any Modules.
Do you mean the code for the UserForm in question?

• When you're in the VBA Editor, in the upper left of the screen you should have the VBAProject window.
• In that window's tree view list, double-click on your NewCourse userform.
• That should open its' code module in the edit window. That's where you should put the UserForm_Initialize code.

Private Sub UserForm_Initialize()

    Dim c As Range
    
    With Sheets("[COLOR="Red"]Sheet2[/COLOR]")
        For Each c In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then [COLOR="Red"]CBox1[/COLOR].AddItem c.Value
        Next c
    End With
End Sub

 

[SimondsJM]

Ok added that in where you suggested, now I am getting an Error

Run-time Error '9': Subscript out of range when I click on the Command button on Sheet2 that I am using to open the Form in question.

Debug points to NewCourse.Show

Here is the Code on Sheet2

Private Sub CmdANC_Click()
    NewCourse.Show
End Sub
Private Sub CmdAV_Click()
    NewVendor.Show
End Sub
Private Sub CmdRC_Click()
    RemoveCourse.Show
End Sub
Private Sub CmdRV_Click()
    RemoveVendor.Show
End Sub

On Sheet 2 there are 4 Command Buttons as you can see in the Code.

Here is Code on Form (NewCourse)

Private Sub UserForm_Initialize()
    Dim c As Range
        With Sheets("Sheet2")
        For Each c In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then CBox1.AddItem c.Value
        Next c
    End With
End Sub

Private Sub AddVendor_Click()
    Unload Me
    NewVendor.Show
End Sub

Private Sub Finished_Click()
    Unload Me
End Sub

 

[AlphaFrog]

Have the NewCourse UserForm_Initialize code visible in the VBA editor and the cursor is somewhere in the code. Then click on the "Run Sub\UserForm" icon. Does the userform run and is its combobox populated?

For the other buttons on the worksheet, does the code for other button clicks work?

 

[SimondsJM]

I tried to run the code as you suggested but still got same error

All other Command Buttons on Sheet2 are working properly

 

[AlphaFrog]

If you did as I suggested, I would think you couldn't have the exact same error as before because you are not running the code for the CmdANC_Click. If it did error, it should highlight some line in the UserForm_Initialize code I would guess.

 

[SimondsJM]

No it is still showing error but not pointing to anything this time.

 

[AlphaFrog]

Try this...

Private Sub UserForm_Initialize()

    Dim c As Range
    [COLOR="Red"]CBox1.RowSource = ""[/COLOR]
    With Sheets("Sheet2")
        For Each c In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then CBox1.AddItem c.Value
        Next c
    End With
End Sub