Limiting Input Range using VBA

  • Thread starter Thread starter Nathan Guill
  • Start date Start date
N

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.
 
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
 
What happens if you just use:

s = "I13:I" & i



Nathan said:
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.
Click to expand...
 
Thanks Rick, the CStr worked perfectly.

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
Back
Top