Resizing a listbox in VBA

[The_Doc_Man]

@jpl458 - I'm going to address the issue of using twips internally yet using inches or cm when designing the form.

In row widths we can use inches with several decimal places, so why since we are using computers, that are very good at performing mundane tasks, why do we have 2 ways of defining distance on a screen.

You find the formatting info of some sizable object on the object's properties sheet and there, you can enter inches or cm. But why did you ever think you were actually entering inches or cm to the property sheet? It, like anything else you see in the design interface, converts your inputs to whatever you need them to be. The developer GUI is holding your hand there. You are talking to a FORM internal to Access and it does all of those computations on the fly. Just like your own forms would do if that were required of them, just like you would limit or convert your user's inputs and options to keep it from becoming overwhelming. (I HOPE your forms would be that nice to your users.)

When you set a beveled edge on a control, did you actually think it said "beveled" internally? No, it is an encoded setting and when you click into the property sheet for it, you get a list-box that shows the names of the possible edge types. The GUI is guiding your hand.

When you try to select a font, you start entering the characters but it doesn't take long for you to get a "find as you type" result that narrows the search and lets you quickly pick the font name. That is the developer GUI holding your hand again.

But NOW we run into the OTHER side of that coin... VBA. When you want to control one of those properties from your program code, you can't use the GUI. You are behind the scenes now. You have to do at run-time what the GUI was doing for you in design mode - i.e. YOU have to look up or convert what the GUI was looking up or converting for you. When you want to do that, you have to recognize that the GUI was like training wheels but when you are to the point that you need to change things using VBA, the training wheels have to come off.

For the "encoded" representations, you can use the Object Browser from the VBA screen to find various types of control settings and the correct names (and values) for them. For the other items that require a computation, you have to know the conversions. For example, twips = 1440/inch. Points (in typeface terms) = 72/inch. (Which means one point in typesetting terms is 20 twips.) For colors you can play with the interactive color tool to find the correct hex or integer value for a given color. The point is, Access never changed. What you are seeing as two ways to do things is merely the result of Access having been nice to you and hiding the sordid truth from you.

 

[jpl458]

A more important question would be - why are you resizing the control on the fly?


So, instead of changing ONE thing - the Name property of the control - at the time you create it, you prefer to change all the instances where you have used the name later???? OK. You don't have time to do it right the first time but you have time to change it later

Because the answer sets of the queries take up different amounts of space and I wanted to use only one list box for the answer sets; or I could have created a list box for each answer set and made visible when needed. I liked the first choice, seemed more elegant.

 

[jpl458]

@jpl458 - I'm going to address the issue of using twips internally yet using inches or cm when designing the form.



You find the formatting info of some sizable object on the object's properties sheet and there, you can enter inches or cm. But why did you ever think you were actually entering inches or cm to the property sheet? It, like anything else you see in the design interface, converts your inputs to whatever you need them to be. The developer GUI is holding your hand there. You are talking to a FORM internal to Access and it does all of those computations on the fly. Just like your own forms would do if that were required of them, just like you would limit or convert your user's inputs and options to keep it from becoming overwhelming. (I HOPE your forms would be that nice to your users.)

When you set a beveled edge on a control, did you actually think it said "beveled" internally? No, it is an encoded setting and when you click into the property sheet for it, you get a list-box that shows the names of the possible edge types. The GUI is guiding your hand.

When you try to select a font, you start entering the characters but it doesn't take long for you to get a "find as you type" result that narrows the search and lets you quickly pick the font name. That is the developer GUI holding your hand again.

But NOW we run into the OTHER side of that coin... VBA. When you want to control one of those properties from your program code, you can't use the GUI. You are behind the scenes now. You have to do at run-time what the GUI was doing for you in design mode - i.e. YOU have to look up or convert what the GUI was looking up or converting for you. When you want to do that, you have to recognize that the GUI was like training wheels but when you are to the point that you need to change things using VBA, the training wheels have to come off.

For the "encoded" representations, you can use the Object Browser from the VBA screen to find various types of control settings and the correct names (and values) for them. For the other items that require a computation, you have to know the conversions. For example, twips = 1440/inch. Points (in typeface terms) = 72/inch. (Which means one point in typesetting terms is 20 twips.) For colors you can play with the interactive color tool to find the correct hex or integer value for a given color. The point is, Access never changed. What you are seeing as two ways to do things is merely the result of Access having been nice to you and hiding the sordid truth from you.

I was just observing that in a listbox you could enter inches, with decimal places, for row sizes (And access would do the conversion), but you had to enter twips when resizing in VBA. I now see that one is in the property sheet and the second is in VBA. It was not a burning question, just curious thought.

 

[Pat Hartman]

This code resizes a subform control when the main form is resized. I'm not sure where you would put your code. It depends on when you know how big you want the listbox. Notice the constants. the fractions are in inches and then they are multiplied by twips. To use this, you will have to futz with the left-right padding numbers to get them to work for the specific size you need at any time.

Private Sub Form_Resize()
Const LeftRightPadding = (0.125 + 0.25) * 1440
Const TopBottomPadding = (0.25 + 0.375) * 1440

Me.sfrmMemberList.Height = Me.InsideHeight - TopBottomPadding
Me.sfrmMemberList.Width = Me.InsideWidth - LeftRightPadding
End Sub

 

[jpl458]

This code resizes a subform control when the main form is resized. I'm not sure where you would put your code. It depends on when you know how big you want the listbox. Notice the constants. the fractions are in inches and then they are multiplied by twips. To use this, you will have to futz with the left-right padding numbers to get them to work for the specific size you need at any time.View attachment 103645

Private Sub Form_Resize()
Const LeftRightPadding = (0.125 + 0.25) * 1440
Const TopBottomPadding = (0.25 + 0.375) * 1440

Me.sfrmMemberList.Height = Me.InsideHeight - TopBottomPadding
Me.sfrmMemberList.Width = Me.InsideWidth - LeftRightPadding
End Sub

Thanks