Problem resizing shapes with VBA

chrisignm

Active Member
Joined
Apr 1, 2014
Messages
273
Hey, I got a code and have problems with resizing shapes.

The first 2 code lines are to resize, the last 2 codes to reposition. Repositioning works.
But resizing does the following: It automatically gives the shapes the size of the cell. I want it to use a predefined size however.

In Object Positioning I chose the last option (so it should be able to move around and resize totally freely)

Code:
Public Sub DropdownResizeIndustries()Dim i As Long
Dim oShape As Shape
Dim oCell As Range
Dim oCell2 As Range


For i = 1 To 15
    Set oShape = ActiveSheet.Shapes("Industry " & i)
    Set oCell = ActiveSheet.Range("G26").Offset(i, 0)
[COLOR=#b22222][B]    oShape.Height = 14.1732283465[/B][/COLOR]
[COLOR=#b22222][B]    oShape.Width = 235.842519685[/B][/COLOR]
    oShape.Top = oCell.Top
    oShape.Left = oCell.Left
Next i
End Sub

Many thanks for help already :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sorry for posting again. I mean oShape gets the height of the cells. So the oShape.Height seems to not work correctly

Because what it really does is: oShape.Height = oCell.Height instead of my predefined height
 
Upvote 0
Your code looks correct to me. Try moving the top and left assignment to before the width and height. It shouldn't matter but you could try it.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top