Resize Worksheet and ActiveX buttons based on a user's PC resolution 2

[tbtcust]

Hello All,

how can I re-size a worksheet and it's ActiveX buttons based on a user's PC resolution? Is there a way to reliably detect the screen resolution and adjust the worksheet with the ActiveX controls?

I have developed a worksheet with ActiveX controls in resolution 1600 x 900. When a user opens this workbook the worksheet is large and some of the ActiveX controls are off the screen.

In the Public Sub Workbook_Open() I have
Range("A1").Select
Range("Z34").Select
ActiveWindow.FreezePanes = True
Columns("Y:Y").ColumnWidth = 11.29
Rows("33:33").RowHeight = 39

Sheets("Main_Sheet").Select
Range("A1").Select
ActiveWindow.Zoom = 105

I tried several solutions from various forums. None have been very effective and most unreliable. Controls shift around overlap, etc.

Thanks in advance for any help.

 

[SkipVought]

Hi,

Why are controls 'off the screen?'

What about ZOOM?

 

[strongm]

Presumably because the user is opening the worksheet on a lower resolution screen ...

 

[combo]

Zoom can be undefined:
Sheets("Main_Sheet").Select
Range("A1:L20").Select 'assuming this is the range you would like to have visible
ActiveWindow.Zoom = True
Range("A1").Select


combo

 

[tbtcust]

Thank you combo. This code puts me much closer.

Is there away to keep the controls from changing size and position?

When the code run (on a lower resolution PC) the range I want is visible and the controls look fine. When I click a control (button) it gets very large and so does the font.

 

[combo]

Do you have any code behind the button that changes zoom, position or size of controls?

combo

 

[tbtcust]

Hello combo,

No, there is no code behind any buttons that changes zoom, position or size of controls.

 

[combo]

Maybe this link will be helpful, looks like this is a bug, one can be found more discussions on this topic in the net.

combo

 

[tbtcust]

Hello combo.

This is resolved. I'm now resetting the Height, Width, Left, and Top properties after the "Zoom can be undefined" code you posted above.

The link did not help, but you did get me thinking along the lines of just resetting all the control properties.

Thanks