ActiveX controls shrinking (listboxes and command buttons)

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
I have an ActiveX ListBox on a worksheet that I populate at the user's request or upon start up. But I'm experiencing a strange problem: the list box changes in size (gets thinner and a bit shorter, which also affects the font inside the list box). I've also seen this before with command buttons (on a sheet that had many command buttons).

Anybody had this problem, and if so, is there a fix? I think I'm going to just change to using a form, but I'd like to know if there is a fix for future reference.

Thank you,

Russell
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Can't you just set the top, left, height properties on initializing? Or does that have no effect...
Just in case you missed the obvious...
Tom
 
Upvote 0
Yes, I did think of that but I think it was actually an issue with ScreenUpdating. I solved it by switching the order of some of my code.

Thanks for responding,

Russell
 
Upvote 0
I believe this is a similar problem to the one I just posted (my check boxes aren't printing as they're displayed) and I was wondering what you did in your coding to help correct the problem. I definately checked left, top, font, and all that, but the boxes come up different from one another. It appears their position on the screen affects the printing somewhat, but I'm not sure...
 
Upvote 0
Well, it's not just checking the top, left, etc., it's re-setting them at run time. So when your code runs or when your workbook opens, set the top, left, width, and height properties and see if that works.

-rh
 
Upvote 0
An absolutely stupid suggestion, but there's no option on this control for Autofit is there? i.e. Autofit the control to house the text. (in my earlier days I remember the fun I had with automatically sized textboxes on userforms)

As I say, stupid, yet simple thing to check.
 
Upvote 0
The properties all match (I haven't closed the workbook yet, so I don't thing anything is resetting), and I've tried both auto-fiting the text and not, the thing is, the controls look normal and sized correctly until I print or print preview. Maybe I need to go brush up on the proper way to insert objects.
Thanks for the help, however!
 
Upvote 0
It will look like they match, but try setting them at run-time and see what happens. I'll bet that it will get rid of your problem (it worked for me!). Don't just trust the settings if things look wrong.
 
Upvote 0
I put this code on the sheet containing the checkboxes:

Private Sub Worksheet_Activate()

With CheckBox8
.AutoSize = False
.Top = 60
.Left = 373.5
.Height = 19.5
.Font = TimesNewRoman
.Width = 108
End With

There are several checkboxes on the page, all of them should be the same width, height etc, as this code, I hoped, would set. But they still look different, ie the fonts, though almost identical, seem spaced different, and the check boxes are slightly different sizes.

With a sore head...
Corticus
 
Upvote 0
Hmm. Yeah, I don't know what the deal is with the controls changing like that. Sorry my suggestion didn't help - as I said, it did work for my list box.

-rh
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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