Limiting Input Range using VBA

[Nathan Guill]

I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").ControlFormat.ListFillRange = s


For example, if i = 2, then s turns out to be "I13:I 15", I need this to be
"I13:I15". Can anyone help me here?

Thanks.

 

[Rick Rothstein \(MVP - VB\)]

The Str function always output a leading space for positive numbers (it is
an ancient function from the early days of BASIC and the space is sort of a
place holder in case the number were negative). You could apply the Trim
function to it...

s = "I13:I" & Trim(Str(i))

or use the Mid function to skip over it...

s = "I13:I" & Mid(Str(i), 2)

but the easiest thing to do is not use that function at all; use the CStr
function instead...

s = "I13:I" & CStr(i)

Rick

 

[Tom Hutchins]

Try

s = "I13:I" & Trim(Str(i))

Hope this helps,

Hutch

 

[Dave Peterson]

What happens if you just use:

s = "I13:I" & i

I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").ControlFormat.ListFillRange = s

For example, if i = 2, then s turns out to be "I13:I 15", I need this to be
"I13:I15". Can anyone help me here?

Thanks.

 

[Nathan Guill]

Thanks Rick, the CStr worked perfectly.

The Str function always output a leading space for positive numbers (it is
an ancient function from the early days of BASIC and the space is sort of
a place holder in case the number were negative). You could apply the Trim
function to it...

s = "I13:I" & Trim(Str(i))

or use the Mid function to skip over it...

s = "I13:I" & Mid(Str(i), 2)

but the easiest thing to do is not use that function at all; use the CStr
function instead...

s = "I13:I" & CStr(i)

Rick