ComboBox methods in Excel VBA
 

I am having a combobox on a worksheet. I like to fill it in the
worksheet_open() function. In VBA how do i get these methods? - additem,
dataitem, list etc for combobox.

I declared a worksheet wb code in thisworkbook
--------------------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
Set ws = Me.Worksheets("Expenses")
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
DeptLocRange.Select
' Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
' While Not IsNull(DeptLocRange(r, 1))
' .cmbLocation.AddItem = DeptLocRange(r, 1)
' Wend
' .cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
End Sub

--------------------
commented lines are not working. wb.cmbLocation.listindex won't work. but
the last two lines works. Any help is greatly appreciated.

Thanks
MVMurthy

ComboBox methods in Excel VBA
 

You have some follow up questions at your other post.

MVM wrote:

I am having a combobox on a worksheet. I like to fill it in the
worksheet_open() function. In VBA how do i get these methods? - additem,
dataitem, list etc for combobox.

I declared a worksheet wb code in thisworkbook
--------------------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
Set ws = Me.Worksheets("Expenses")
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
DeptLocRange.Select
' Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
' While Not IsNull(DeptLocRange(r, 1))
' .cmbLocation.AddItem = DeptLocRange(r, 1)
' Wend
' .cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
End Sub

--------------------
commented lines are not working. wb.cmbLocation.listindex won't work. but
the last two lines works. Any help is greatly appreciated.

Thanks
MVMurthy

--

Dave Peterson

ComboBox methods in Excel VBA
 

For Line #2: If I have the .listfillrange pointed at a range (probably done
manually), I could get this error.

You could either clear it out manually (under properties or in code)

Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Private Sub Workbook_Open()
Dim r As Integer
With Expenses
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
With .cmbDepartment
.ListFillRange = ""
.AddItem "hi there"
.ListIndex = 0
.PrintObject = False
End With
With .cmbLocation
.ListFillRange = ""
.AddItem "<select Loc"
.AddItem DeptLocRange(r, 1)
.ListIndex = 0
.PrintObject = False
End With
End With
End Sub

For line #3: Note that I got rid of the "set ws = expenses" line.

When I had "with ws", I got a compile error. Not every generic worksheet has an
object called .cmbdepartment. But when I used "with expenses" directly, that
single worksheet did have that object.

I'm not sure why you didn't see intellisense kick in.

When I typed the first dot in this line:
..ListFillRange = ""
I saw a bunch of options to choose from.


=====
And it looks like you may want to loop through all those cells in those ranges.

If that's true, one way:

Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Private Sub Workbook_Open()
Dim myCell As Range
With Expenses
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
With .cmbDepartment
.ListFillRange = ""
.Clear 'if you run it twice??
.AddItem "<Select Department"
For Each myCell In DeptLocRange.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
.PrintObject = False
End With
With .cmbLocation
.ListFillRange = ""
.Clear
.AddItem "<select Loc"
For Each myCell In AcctRange.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
.PrintObject = False
End With
End With
End Sub

MVM wrote:

Hi Dave:
Thank you for your time.
now the program is like this..
-------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
' Set ws = Me.Worksheets("Expenses")
Set ws = Expenses
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
'1 Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
2 Expenses.cmbLocation.AddItem ("<select Loc")
3 .cmbLocation.AddItem (DeptLocRange(r, 1))
' Worksheets("Expenses").cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
'following lines working
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
Expenses.cmbDepartment.PrintObject = False
Expenses.cmbLocation.PrintObject = False
End Sub

-------
Line 2 gives run-time error 70 - permission denied
Line 3 is another problem
if i use .cmblocation.... it says "Method or data member not found"
when i use "ws." the list won't contain cmblocation or cmbdepartment
if i use "Expenses." this list has these.
ws is set to Expenses sheet earlier.

Also "Expenses.cmblocation." won't list any methods.

Is there any references i am missing?
thank again.
MVM

"Dave Peterson" wrote:

You have some follow up questions at your other post.

MVM wrote:

I am having a combobox on a worksheet. I like to fill it in the
worksheet_open() function. In VBA how do i get these methods? - additem,
dataitem, list etc for combobox.

I declared a worksheet wb code in thisworkbook
--------------------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
Set ws = Me.Worksheets("Expenses")
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
DeptLocRange.Select
' Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
' While Not IsNull(DeptLocRange(r, 1))
' .cmbLocation.AddItem = DeptLocRange(r, 1)
' Wend
' .cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
End Sub

--------------------
commented lines are not working. wb.cmbLocation.listindex won't work. but
the last two lines works. Any help is greatly appreciated.

Thanks
MVMurthy

--

Dave Peterson


--

Dave Peterson

ComboBox methods in Excel VBA
 

Thank you Dave.
your code works. So the problem is with manual filling.
i still don't get the list of options with intellisense

"Expenses.cmbLocation." no list. what am i doing wrong?
Thank your for your help.
MVMurthy

"Dave Peterson" wrote:

For Line #2: If I have the .listfillrange pointed at a range (probably done
manually), I could get this error.

You could either clear it out manually (under properties or in code)

Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Private Sub Workbook_Open()
Dim r As Integer
With Expenses
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
With .cmbDepartment
.ListFillRange = ""
.AddItem "hi there"
.ListIndex = 0
.PrintObject = False
End With
With .cmbLocation
.ListFillRange = ""
.AddItem "<select Loc"
.AddItem DeptLocRange(r, 1)
.ListIndex = 0
.PrintObject = False
End With
End With
End Sub

For line #3: Note that I got rid of the "set ws = expenses" line.

When I had "with ws", I got a compile error. Not every generic worksheet has an
object called .cmbdepartment. But when I used "with expenses" directly, that
single worksheet did have that object.

I'm not sure why you didn't see intellisense kick in.

When I typed the first dot in this line:
..ListFillRange = ""
I saw a bunch of options to choose from.


=====
And it looks like you may want to loop through all those cells in those ranges.

If that's true, one way:

Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Private Sub Workbook_Open()
Dim myCell As Range
With Expenses
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
With .cmbDepartment
.ListFillRange = ""
.Clear 'if you run it twice??
.AddItem "<Select Department"
For Each myCell In DeptLocRange.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
.PrintObject = False
End With
With .cmbLocation
.ListFillRange = ""
.Clear
.AddItem "<select Loc"
For Each myCell In AcctRange.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
.PrintObject = False
End With
End With
End Sub

MVM wrote:

Hi Dave:
Thank you for your time.
now the program is like this..
-------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
' Set ws = Me.Worksheets("Expenses")
Set ws = Expenses
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
'1 Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
2 Expenses.cmbLocation.AddItem ("<select Loc")
3 .cmbLocation.AddItem (DeptLocRange(r, 1))
' Worksheets("Expenses").cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
'following lines working
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
Expenses.cmbDepartment.PrintObject = False
Expenses.cmbLocation.PrintObject = False
End Sub

-------
Line 2 gives run-time error 70 - permission denied
Line 3 is another problem
if i use .cmblocation.... it says "Method or data member not found"
when i use "ws." the list won't contain cmblocation or cmbdepartment
if i use "Expenses." this list has these.
ws is set to Expenses sheet earlier.

Also "Expenses.cmblocation." won't list any methods.

Is there any references i am missing?
thank again.
MVM

"Dave Peterson" wrote:

You have some follow up questions at your other post.

MVM wrote:

I am having a combobox on a worksheet. I like to fill it in the
worksheet_open() function. In VBA how do i get these methods? - additem,
dataitem, list etc for combobox.

I declared a worksheet wb code in thisworkbook
--------------------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
Set ws = Me.Worksheets("Expenses")
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
DeptLocRange.Select
' Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
' While Not IsNull(DeptLocRange(r, 1))
' .cmbLocation.AddItem = DeptLocRange(r, 1)
' Wend
' .cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
End Sub

--------------------
commented lines are not working. wb.cmbLocation.listindex won't work. but
the last two lines works. Any help is greatly appreciated.

Thanks
MVMurthy

--

Dave Peterson


--

Dave Peterson

ComboBox methods in Excel VBA
 

I'm not sure.

Sometimes when intellisense dies, I can close excel and reopen and it's back.



MVM wrote:

Thank you Dave.
your code works. So the problem is with manual filling.
i still don't get the list of options with intellisense

"Expenses.cmbLocation." no list. what am i doing wrong?
Thank your for your help.
MVMurthy

"Dave Peterson" wrote:

For Line #2: If I have the .listfillrange pointed at a range (probably done
manually), I could get this error.

You could either clear it out manually (under properties or in code)

Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Private Sub Workbook_Open()
Dim r As Integer
With Expenses
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
With .cmbDepartment
.ListFillRange = ""
.AddItem "hi there"
.ListIndex = 0
.PrintObject = False
End With
With .cmbLocation
.ListFillRange = ""
.AddItem "<select Loc"
.AddItem DeptLocRange(r, 1)
.ListIndex = 0
.PrintObject = False
End With
End With
End Sub

For line #3: Note that I got rid of the "set ws = expenses" line.

When I had "with ws", I got a compile error. Not every generic worksheet has an
object called .cmbdepartment. But when I used "with expenses" directly, that
single worksheet did have that object.

I'm not sure why you didn't see intellisense kick in.

When I typed the first dot in this line:
..ListFillRange = ""
I saw a bunch of options to choose from.


=====
And it looks like you may want to loop through all those cells in those ranges.

If that's true, one way:

Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Private Sub Workbook_Open()
Dim myCell As Range
With Expenses
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
With .cmbDepartment
.ListFillRange = ""
.Clear 'if you run it twice??
.AddItem "<Select Department"
For Each myCell In DeptLocRange.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
.PrintObject = False
End With
With .cmbLocation
.ListFillRange = ""
.Clear
.AddItem "<select Loc"
For Each myCell In AcctRange.Cells
.AddItem myCell.Value
Next myCell
.ListIndex = 0
.PrintObject = False
End With
End With
End Sub

MVM wrote:

Hi Dave:
Thank you for your time.
now the program is like this..
-------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
' Set ws = Me.Worksheets("Expenses")
Set ws = Expenses
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
'1 Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
2 Expenses.cmbLocation.AddItem ("<select Loc")
3 .cmbLocation.AddItem (DeptLocRange(r, 1))
' Worksheets("Expenses").cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
'following lines working
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
Expenses.cmbDepartment.PrintObject = False
Expenses.cmbLocation.PrintObject = False
End Sub

-------
Line 2 gives run-time error 70 - permission denied
Line 3 is another problem
if i use .cmblocation.... it says "Method or data member not found"
when i use "ws." the list won't contain cmblocation or cmbdepartment
if i use "Expenses." this list has these.
ws is set to Expenses sheet earlier.

Also "Expenses.cmblocation." won't list any methods.

Is there any references i am missing?
thank again.
MVM

"Dave Peterson" wrote:

You have some follow up questions at your other post.

MVM wrote:

I am having a combobox on a worksheet. I like to fill it in the
worksheet_open() function. In VBA how do i get these methods? - additem,
dataitem, list etc for combobox.

I declared a worksheet wb code in thisworkbook
--------------------
Option Explicit
Public DeptLocRange As Range, AcctRange As Range
Public ws As Worksheet
Private Sub Workbook_Open()
Dim r As Integer
Set ws = Me.Worksheets("Expenses")
With ws
Set DeptLocRange = .Range(.Cells(79, 3), .Cells(86, 6))
Set AcctRange = .Range(.Cells(91, 3), .Cells(92, 16))
r = 2
DeptLocRange.Select
' Worksheets("Expenses").cmbLocation.AddItem ("<select Loc")
' While Not IsNull(DeptLocRange(r, 1))
' .cmbLocation.AddItem = DeptLocRange(r, 1)
' Wend
' .cmbLocation.ListIndex = 0
' .cmbDepartment.ListIndex = 0
End With
Worksheets("Expenses").cmbLocation.ListIndex = 0
Worksheets("Expenses").cmbDepartment.ListIndex = 0
End Sub

--------------------
commented lines are not working. wb.cmbLocation.listindex won't work. but
the last two lines works. Any help is greatly appreciated.

Thanks
MVMurthy

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson