Limiting Input Range using VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nathan Guill - 27 May 2008 22:26 GMT

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.

Reply to this Message

Rick Rothstein (MVP - VB) - 27 May 2008 22:36 GMT

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

>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
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message

Nathan Guill - 27 May 2008 23:18 GMT

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
[quoted text clipped - 29 lines]
>>
>> Thanks.

Reply to this Message

Tom Hutchins - 27 May 2008 22:39 GMT

Try

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

Hope this helps,

Hutch

> 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
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

Dave Peterson - 27 May 2008 23:00 GMT

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
[quoted text clipped - 10 lines]
>
> Thanks.

Signature

Dave Peterson

Reply to this Message