ActiveX Controls Misbehaving on Different Screen Scales

Hi folks!

I created a 'tool', so to speak, in excel using various dropdown comboboxes and command buttons that generate specified data into a textbox, depending on what value is selected. The goal is to generate 'statements' based on the users selections.

The tool works well, except for one big problem.

I created this tool on my own work setup, with monitors that are set to 100% screen scale. It didnt occur to me that it may not function correctly on other users setups.

When users in my team attempt to use the program on a setup that is different, e.g., laptop screen set to 150% + monitor set to 100%, the contols just start acting wild and pretty much dont work. For example, If a user on a laptop (150% screen scale) clicks a drop down, the dropdown arrow will either bounce around, get really large, or shrink etc, text will be blurry or gradually shrink to nothing.

Is there a way to get vba to resize the controls depending on the users screen scale when opening the file, while maintaining the controls location on the page and usability?

Ive attempted a few different codes, but so far nothing seems to be working for me.

Any help would be massively appreciated, as I'm completely self taught when it comes to this sort of thing!

Thank you

This has been a known problem since VBA was introduced into Office. ActiveX controls are not native controls in Excel. They were, as I say, imported “by force” from the VB5 language. As a result, for many years you have noticed strange behavior of these controls when they are embedded in a worksheet. There is no problem with them when they are embedded in a UserForm.
If you can, use Form Controls, although they are much worse to program. The only way I've found for ActiveX controls is to constantly force a certain position and size of the controls in multiple events.

Artik

Hi StringJazzer!

Thank you so much for replying!

I found the form controls too limiting, which is why i opted for ActiveX.

sure thing, I'll attach a sample - what sort of things do you need included in the sample to review? I'm mindful that my document contains sensitive data so I will need to strip it back a bit first, but keeping the functions the same.

I have approx 13 Dropdown Comboboxes that contain values located in cells on another worksheet. When the user selects values from each drop down and click on a 'generate' command button, text will concatenate assigned text (in vba) to a specified textbox to form a 'statetment' based on the value selections.

Heres a screen grab: below the large image is an example of how when clicked on, the dropdown button shifts/misbehaves

excel grab.png

Thanks Artik!

Yes, when I initially started this project, I was using form controls, however I found they were way too restrictive (likely due the the fact that I also had no idea what I was doing )

There is no problem with them when they are embedded in a UserForm.

Ok, very curious, I know absolutely nothing about UserForms. If I was to recreate my tool using a userform, how would that appear? would It open onto a worksheet? Id contemplate redesigning my tool into a userform if it means it will decrease the risk of function issues for users. I have a large team of approx 30 people who work all across different sites and different workstation setups, depending on where they are working for the day. So this issue is super frustrating

Looking at the screenshot shown, it seems that all the controls can be moved to UserForm.

Artik

You design UserForm in a VBA editor. From the editor menu, choose Insert/UserForm, into which you can insert the controls you need. They are programmed similarly to controls embedded in a worksheet.
You should remember that the UserForm should be sized to fit on the computer screen with the smallest resolution used by your colleagues. At the time of designing the form (especially its appearance), you can change the screen resolution on your computer to the smallest one used by your team members, so that you have an idea of how the user will see your form. It is also possible to dynamically resize the form and its controls, but this process is already more complex.
Calling the form can be implemented by a button placed on the sheet, to which a simple macro will be attached:

Please Login or Register  to view this content.

Artik

Hi Artik!

Thank you so much for your help! I really appreciate it. Im now in the process of reverting my design to a userform. I was just hoping to clarify one thing:

Please Login or Register  to view this content.

- when you say smallest, do you mean, for example, 100% would be the smallest, and 150% would be larger? or is it the other way around?

Thank you!

Correct, I've not used any cells at all! This makes me happy, hopefully moving it to a userform will be simple and the best solution. Thank you so much for your help!!