vba variable possible in .listfillrange? (excel 2011 ie. mac version)

[jarmose]

Hi,

Target: a macro should set the input range for a combobox. (Excel for Mac)

Problem:
The range can change so I cannot use a standard .listfillrange range such as “$B$1:$B$10” (which would work otherwise).
I’ve tried the following:

Sub combo()
Dim list_finalrow As Integer
list_finalrow = Sheets("sheet1").Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.Shapes.Range(Array("Drop Down 20")).Select
With Selection
.ListFillRange = "$B$1:$B" & finalrow
.LinkedCell = "$G$5"
.DropDownLines = 8
.Display3DShading = True
End With
End Sub

The input field of the combobox stays empty.. I wonder if a variable can be used here at all?
Yours, Jarmo

 

[Andrew Poulsom]

Welcome to MrExcel.

It should be:

.ListFillRange = "$B$1:$B" & list_finalrow

You should use Option Explicit (Require variable declaration in Tools|Options|Editor tab). With that your code would have created an error.

 

[jarmose]

Oh dear.. how careless of me.. I'll start using option explicit,
Thank you Andrew!

yours, Jarmo