Combobox in Vba

June 8, 2009 at 02:10:24
Specs: Windows XP
I was wondering if anyone knows how to collect data from excel, and code it in VBA so it transfers it into a combo box in VBA, because i'm doing a project and i need the names of 4 teams that are on a worksheet in excel and i need them in a combo box in VBA, by using a for next loop, preferably.

I have written following code but its giving me error Object Required runtime err 424

Private Sub Workbook_Open()

Dim I As Integer
Dim Lastrow As Long

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
Lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox Lastrow
End If

For I = 1 To Lastrow
rng = "A1:A" & I
Combobox1.AddItem Range(rng).Value
Next I

End Sub

Pls help


See More: Combobox in Vba

Report •

June 8, 2009 at 05:25:37
Just a quick troubleshooting tip:

If you don't know which line is generating the error, change the first line of the macro to something like:

Sub TestCombo() to turn the code into a "regular" macro.

Then use F8 to single step though it until you can determine what's causing the error.

It you must leave it as a Workbook_Open macro, then force an error by commenting out a required line, such as End If, and saving the workbook.

When you open the workbook, the code should fail and throw you into Debug mode. Uncomment the error you forced and use F8 to start single stepping.

Find the error, fix it, and you're on your way.

Report •

June 12, 2009 at 06:53:07
the error is at

Combobox1.AddItem Range(rng).Value

but i already have a combo box with name combobox1


Report •

June 12, 2009 at 08:06:00
A few items...

First, since this code resides in the ThisWorkbook module, you have to tell the module where the ComboBox is:

Sheets(1).ComboBox1.AddItem "Item1"

Second, how did you create the CombBox? If it was created with the Forms tools bar, you might still have problems, but if it was created with the Control Toolbox, you should be OK...sort of. Read on...

Third, if you are trying to loop through the range A1:A & Lastrow, you are doing it wrong and may run into further problems.

Let's look at this snippet:

For I = 1 To Lastrow
      rng = "A1:A" & I
          Combobox1.AddItem Range(rng).Value
Next I

When I = 1, rng will be set to A1:A1 and Range("A1:A1").Value will be whatever is in A1. This is OK and should work.

However, when I = 2, rng will be set to A1:A2 and Range("A1:A2") doesn't have a "value". Range("A1") has a value and Range("A2") has a value, but Range("A1:A2") does not. This will probably throw up a Type Mismatch error.

A couple of ways to loop through the range are as follows:

For I = 1 To Lastrow
 Sheets(1).ComboBox1.AddItem Cells(I, "A").Value
Next I


For I = 1 To Lastrow
 Sheets(1).ComboBox1.AddItem Range("A" & I).Value
Next I

Each of these methods determine the value in a single cell each time through the loop.

Finally, if all you really want is the data from Column A, there is a more efficient way to find the last row in Column A. Your method finds the last row in the entire sheet, which might cause your loop to be longer than it needs to be.

e.g. If your list is in A1:A10 but you have a value in G20, Lastrow will be set to 20 and your code will loop through A1:A20, probably resulting in blank entries in your ComboBox.

The "proper" way to find the last row in a given column is as follows:

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Report •

Related Solutions

June 16, 2009 at 03:05:31
DerbyDad Thanks a lot for your help

Report •

Ask Question