CODEWITHSUNDEEP
excel
Mike D
Mike D

Reputation: 2841

VBA Multiple controls container

I put a bunch of controls into a cell made up of several merged cells in a single row. That worked well until I re-sized one of the columns. Then several of the controls overlapped.

Is there some way to freeze the size of this merged cell? Or am I using the wrong kind of container for the controls?

Upvotes: 2

Views: 386

Answers (2)

thanos.a
thanos.a

Reputation: 2694

You can also add an event that returns the column to the correct size. Here is an example that when excel calculates, the column A is enforced to be 20.71, my desired width.

Private Sub Worksheet_Calculate() Columns("A:A").ColumnWidth = 20.71 End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

The problem is not preventing the resizing of the merged cell but resizing of the controls.

To prevent resizing/movement of the controls with Excel cells, right click on the controls and click on Format Control. Under the Properties Tab, select Don't move or size with cells. Now when you re-size the cells or the columns, the controls will not overlap. This applies top both ActiveX and Form Controls.

Still if you want to prevent the resizing of the merged cells then you can do that by protecting the sheet.

Upvotes: 1

Related Questions