VBA Autofit Columns

[ionelz]

I have an Button for this : to autofit columns, from 8 to....how many they are (found on net)

Dim x As Integer
For x = 8 To ActiveSheet.UsedRange.Columns.Count
Columns(x).EntireColumn.AutoFit
Next x

All Ok, it autofit columns but it change the SIZE too of my Active X Control buttons placed in these columns even they are set don't move or size with cells
How to fix this ?

 

[MyForcey]

Columns or rows?

I use the below for my table, maybe you can adjust to suit yours?

"FAC_RISKS" being the defined range.

Sub Autofit()
  Dim rng As Range
  Application.ScreenUpdating = False
  ActiveSheet.Rows("1").RowHeight = 120
  ActiveSheet.Rows("2").RowHeight = 50
  ActiveSheet.Range("FAC_RISKS").EntireColumn.Autofit
  ActiveSheet.Range("FAC_RISKS").EntireRow.Autofit
  Application.ScreenUpdating = True
End Sub

 

[BSALV]

look in the attributes of that shape

Prevent Images and Shapes from Resizing or Moving in Excel - TeachExcel.com

How to stop Images and Shapes from resizing in Excel when you change the size of rows and columns This will allow you to keep the dimensions of your images and shapes while allowing them to move with ...

www.teachexcel.com

 

[ionelz]

look in the attributes of that shape

Prevent Images and Shapes from Resizing or Moving in Excel - TeachExcel.com

How to stop Images and Shapes from resizing in Excel when you change the size of rows and columns This will allow you to keep the dimensions of your images and shapes while allowing them to move with ...

www.teachexcel.com

Did this but still buttons change in size

 

[Joe4]

This doesn't solve your issue, but just a little note that will help you with your programming and performance.
When auto fitting many consecutive columns, you do NOT need to loop through each one. You can apply autofit to all those columns at once with a single line of code, i.e.

    Range(Cells(1, 8), Cells(1, ActiveSheet.UsedRange.Columns.Count)).EntireColumn.AutoFit

 

[BSALV]

Did this but still buttons change in size

Size.xlsb
Did you modify the property of each shape to "don't move or size with cells" ?
Because that's the solution to your problem.
See attachment, you can change columnwidth or rowheight, the shapes all remain at their position and size.

 

[Fluff]

@BSALV maybe you missed this part of the op.

my Active X Control buttons

@ionelz I would recommend replacing the ActiveX controls with Shapes and/or Form Controls.
ActiveX controls are notoriously flaky.

 

[ionelz]

Size.xlsb
Did you modify the property of each shape to "don't move or size with cells" ?
Because that's the solution to your problem.
See attachment, you can change columnwidth or rowheight, the shapes all remain at their position and size.

I can not download your file, and for some reason, "don't move or size with cells" do not work for me, buttons keep size ..ing

 

[BSALV]

example file
new link

 

[Fluff]

What is the point of uploading another file that is totally irrelevant to the OP's problem?