Combo Box List out of range error.
 

For I = 1 To 6
Dim Item As String
Item = "I" & CStr(I)

roomName = Worksheets("Sheet1").Range(Item).Value
Worksheets("Sheets1").cmbList.AddItem (roomName)
MsgBox (roomName)
Next

I dont know why this piece of code is throwsing the error. If the line
Worksheets("Sheets1").cmbList.AddItem (roomName) is commented out, then the
msg box shows all the 6 items there. Which tells me there is some problem
with this line of code(combo box). Is there a property that needs to be set
for combo box's?Please help... Thnx

My bet is that it's a typo.

Worksheets("Sheets1").cmbList.AddItem (roomName)

Is the worksheet really named SheetS1 (with that extra S near the end)?

And I'd be hesitant to use a variable named Item. It's used in VBA, too. And
it may not confuse excel, but it sure can confuse me. (I also don't like single
character variables--but that's a personal preference.) And I like declaring my
variables, too.



Option Explicit
Sub testme01()

Dim myStr As String
Dim iCtr As Long
Dim RoomName As String

For iCtr = 1 To 6
myStr = "I" & iCtr
RoomName = Worksheets("Sheet1").Range(myStr).Value
Worksheets("Sheet1").cmblist.AddItem RoomName
'MsgBox RoomName
Next iCtr

End Sub


Kiran wrote:

For I = 1 To 6
Dim Item As String
Item = "I" & CStr(I)

roomName = Worksheets("Sheet1").Range(Item).Value
Worksheets("Sheets1").cmbList.AddItem (roomName)
MsgBox (roomName)
Next

I dont know why this piece of code is throwsing the error. If the line
Worksheets("Sheets1").cmbList.AddItem (roomName) is commented out, then the
msg box shows all the 6 items there. Which tells me there is some problem
with this line of code(combo box). Is there a property that needs to be set
for combo box's?Please help... Thnx

--

Dave Peterson

Hi Dave,
Thanks for your reply. Appreciate it. But your code did not work. I got the
following error:
"Run time error: 438
Object does not support this method or property. "
When debugged stops at this line:
Worksheets("Sheet1").cmblist.AddItem RoomName

All I have on that excel sheet is a combo box. And On "Column I" on Sheet1 I
have a bunch of room names (bed room, great room, kitchen) and want to load
this list onto the combo-box when my customer opens the excel spreadsheet.

Can u help?
thnx

"Dave Peterson" wrote:

My bet is that it's a typo.

Worksheets("Sheets1").cmbList.AddItem (roomName)

Is the worksheet really named SheetS1 (with that extra S near the end)?

And I'd be hesitant to use a variable named Item. It's used in VBA, too. And
it may not confuse excel, but it sure can confuse me. (I also don't like single
character variables--but that's a personal preference.) And I like declaring my
variables, too.



Option Explicit
Sub testme01()

Dim myStr As String
Dim iCtr As Long
Dim RoomName As String

For iCtr = 1 To 6
myStr = "I" & iCtr
RoomName = Worksheets("Sheet1").Range(myStr).Value
Worksheets("Sheet1").cmblist.AddItem RoomName
'MsgBox RoomName
Next iCtr

End Sub


Kiran wrote:

For I = 1 To 6
Dim Item As String
Item = "I" & CStr(I)

roomName = Worksheets("Sheet1").Range(Item).Value
Worksheets("Sheets1").cmbList.AddItem (roomName)
MsgBox (roomName)
Next

I dont know why this piece of code is throwsing the error. If the line
Worksheets("Sheets1").cmbList.AddItem (roomName) is commented out, then the
msg box shows all the 6 items there. Which tells me there is some problem
with this line of code(combo box). Is there a property that needs to be set
for combo box's?Please help... Thnx

--

Dave Peterson

It worked for me when I added a combobox from the control toolbox toolbar to a
worksheet named Sheet1.

I did rename the combobox from ComboBox1 to cmblist.

So is there a worksheet named Sheet1 that holds a combobox from the control
toolbox toolbar named cmblist in your workbook?



Kiran wrote:

Hi Dave,
Thanks for your reply. Appreciate it. But your code did not work. I got the
following error:
"Run time error: 438
Object does not support this method or property. "
When debugged stops at this line:
Worksheets("Sheet1").cmblist.AddItem RoomName

All I have on that excel sheet is a combo box. And On "Column I" on Sheet1 I
have a bunch of room names (bed room, great room, kitchen) and want to load
this list onto the combo-box when my customer opens the excel spreadsheet.

Can u help?
thnx

"Dave Peterson" wrote:

My bet is that it's a typo.

Worksheets("Sheets1").cmbList.AddItem (roomName)

Is the worksheet really named SheetS1 (with that extra S near the end)?

And I'd be hesitant to use a variable named Item. It's used in VBA, too. And
it may not confuse excel, but it sure can confuse me. (I also don't like single
character variables--but that's a personal preference.) And I like declaring my
variables, too.



Option Explicit
Sub testme01()

Dim myStr As String
Dim iCtr As Long
Dim RoomName As String

For iCtr = 1 To 6
myStr = "I" & iCtr
RoomName = Worksheets("Sheet1").Range(myStr).Value
Worksheets("Sheet1").cmblist.AddItem RoomName
'MsgBox RoomName
Next iCtr

End Sub


Kiran wrote:

For I = 1 To 6
Dim Item As String
Item = "I" & CStr(I)

roomName = Worksheets("Sheet1").Range(Item).Value
Worksheets("Sheets1").cmbList.AddItem (roomName)
MsgBox (roomName)
Next

I dont know why this piece of code is throwsing the error. If the line
Worksheets("Sheets1").cmbList.AddItem (roomName) is commented out, then the
msg box shows all the 6 items there. Which tells me there is some problem
with this line of code(combo box). Is there a property that needs to be set
for combo box's?Please help... Thnx

--

Dave Peterson


--

Dave Peterson