ComboBox methods in Excel VBA

[MVM]

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]

You have some follow up questions at your other post.

 

[MVM]

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]

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]

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]

I'm not sure.

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

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