Excel shape starting in the correct position with VBA

[Peter Davison]

I am creating some rectangles in excel with the code below.
I have set it to create 5 rectangles using i = 0 to 4
What I am not sure is how I get the first block to start at 405 position and not 0 which is what "i" is starting at (I think).
Any help would be great.

Private Sub CommandButton1_Click()

Dim s As Shape
Dim i As Integer
Dim sleft As Integer
Const sWidth As Integer = 100
Const sHeight As Integer = 200

Dim ws As Worksheet
Set ws = Sheets("sheet1")
For i = 0 To 4

sleft = i * sWidth

'add a shape
'Positions (type of shape, Start Position from left, Start Position from Top, Width, Height)
Set s = ws.Shapes.AddShape(1, sleft, 130, sWidth, sHeight)

Next i

'make the fixture Black
s.Fill.ForeColor.RGB = RGB(0, 0, 0)

s.Fill.Visible = False
End Sub

 

[Fluff]

Not quite sure what you mean by 405 position, but how about

 sleft = (i+1) * sWidth

 

[Wookiee]

Would making this change do what you're trying to accomplish?

sleft = (i * sWidth) + 405

 

[Peter Davison]

Hi
405 is the position to start the shape from the left of the spreadsheet.
Would it still be -
sleft = (i+1) * sWidth

Would making this change do what you're trying to accomplish?

sleft = (i * sWidth) + 405

That has worked great. Thank you
If I wanted the user to enter the number of shapes (for e.g. 5) how would I go about achieving that?
Would it be something like -
'Dim w As Variant, h As Variant, b As Variant, nb As Variant, bw As Variant


nb = InputBox("Please enter the number of bays")
If nb = "" Then Exit Sub

What would I then make the following -
For i = 0 To 4

Thanks for your help.

 

[Fluff]

How about

 For i = 1 To nb

 

[Peter Davison]

Perfect and thank you again, I may have a further question later if that is okay.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter Davison]

Hi Its me again.
I've moved further along now and I want to set a variable to read what has been entered in a cell on the spreadsheet.
Instead of having a const set at 100 (Const sWidth As Integer = 100)
I would like it to get the figure from cell D2 in sheet1 which would be to two decimal points.

I tried to use (Set sWidth = ws.Range("d2")
but it doesn't seem to like it.
I'm wondering if I have it in the wrong place.
Any help would be great.

This is the code so far -

Dim s As Shape
Dim i As Integer
Dim sleft As Integer
Dim sWidth As Double
Dim ws As Worksheet
Set ws = Sheets("sheet1")

Const sHeight As Integer = 200

Set sWidth = ws.Range("d2")
nb = InputBox("Please enter the number of bays")
If nb = "" Then Exit Sub
For i = 0 To nb - 1

        sleft = (i * sWidth) + 405


        'add a shape
        'Positions (type of shape, Start Position from left, Start Position from Top, Width, Height)
        Set s = ws.Shapes.AddShape(1, sleft, 130, sWidth, sHeight)

        'make the fixture Black
        s.Fill.ForeColor.RGB = RGB(0, 0, 0)

        s.Fill.Visible = False

    Next i
End Sub

 

[Fluff]

Please edit your post to put the code in code tags, thanks.

 

[Peter Davison]

I've just cracked it I needed to use the Variant variable.
I'm sure we will speak soon.
Take Care