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