Resizing a listbox in VBA

[jpl458]

I have a form that has on listbox, and I have several queries that display results in one listbox. It works well. Now I have created a query that will not fit into the list box and I want to resize it; Here is code I have used:

Me.List115.Visible = True
Me.List115.Height = 2.5826
Me.List115.Width = 10
Me.List115.RowSourceType = "Table/Query"
Me.List115.RowSource = "QryGroupbyCompany"
Me.List115.ColumnCount = 8

after the dimensions in the height and width rows I've tried " and in and neither worked. I created a listbox and stretched it to the size I wanted, copied the dimensions and put them in the code for the original listbox with no result. I am wondering if it's even possible to resize a listbox in VBA.

 

[MajP]

Height and width are in twips. Multiply 2.5826 by 1440

 

[Gasman]

Still not giving decent names to your controls I see?

 

[jpl458]

Still not giving decent names to your controls I see?

I will, when they work.

 

[MajP]

With...End With Statement - Visual Basic

Learn more about: With...End With Statement (Visual Basic)

learn.microsoft.com

 

[jpl458]

Height and width are in twips. Multiply 2.5826 by 1440

One would think that if inches work for column widths, then why doesn't inches work for box sizes. Twips work works and thanks for the help. But with everything else they do for you in ACCESS, why might you need a calculator, unless you ae wizard in base 1440 math.

 

[cheekybuddha]

Or just get VBA to do the maths:

Const TWIPS_PER_INCH As Integer = 1440

With Me.List115
  .Visible = True
  .Height = Int(2.5826 * TWIPS_PER_INCH)
  .Width = 10 * TWIPS_PER_INCH
  .RowSourceType = "Table/Query"
  .RowSource = "QryGroupbyCompany"
  .ColumnCount = 8
End With

 

[jpl458]

Or just get VBA to do the maths:

Const TWIPS_PER_INCH As Integer = 1440

With Me.List115
  .Visible = True
  .Height = Int(2.5826 * TWIPS_PER_INCH)
  .Width = 10 * TWIPS_PER_INCH
  .RowSourceType = "Table/Query"
  .RowSource = "QryGroupbyCompany"
  .ColumnCount = 8
End With

Very cool, and reusable. Thanks so much.

 

[MajP]

One would think that if inches work for column widths, then why doesn't inches work for box sizes. Twips work works and thanks for the help. But with everything else they do for you in ACCESS, why might you need a calculator, unless you ae wizard in base 1440 math

Not really. Column widths like everything else are also saved in Twips. The user interface allows you to specify the property in the regional settings (cm, inches) for width, height, top, left, etc. However all these properties are converted and saved in twips.

Width Property [Access 2003 VBA Language Reference]

learn.microsoft.com

Use regional settings using the Properties in the User Interface and use Twips in VBA.

 

[Gasman]

One would think that if inches work for column widths, then why doesn't inches work for box sizes. Twips work works and thanks for the help. But with everything else they do for you in ACCESS, why might you need a calculator, unless you ae wizard in base 1440 math.

You can also use cm?, so you need a base value to work with?

 

[GPGeorge]

One would think that if inches work for column widths, then why doesn't inches work for box sizes. Twips work works and thanks for the help. But with everything else they do for you in ACCESS, why might you need a calculator, unless you ae wizard in base 1440 math.

Sometimes, youth and enthusiasm are very helpful, but other times, a bit of experience and education are more helpful.

Twip is the measurement for pixels on a screen. While this discussion quickly dives into the details beyond what we really need to know, it's worth the effort to read and understand it.

Here's the relevant statement.

Twips are screen-independent units to ensure that the proportion of screen elements are the same on all display systems. A twip is defined as being 1⁄1440 of an inch (approximately 0.01764 mm).

In order to make the placement of controls on forms and reports as precise as possible, base measurements are expressed in Twips.

However, humans are trained to look for inches or centimeters as the unit of measurement, so we often use this conversion to facilitate that.

Long-time, experienced Access developers have internalized the equivalence between Twips and "human" units of measurement.

On the other hand, to make it easier to code, many of us define a global constant to use instead of that number when applying dimensions.

Public Const mTwip = 1440

Then they can refer to mTwip without having to remember the exact number.

 

[MajP]

Const TWIPS_PER_INCH As Integer = 1440
Const TWIPS_PER_CM As Integer =567

For those on the metric system

 

[jpl458]

Sometimes, youth and enthusiasm are very helpful, but other times, a bit of experience and education are more helpful.

Twip is the measurement for pixels on a screen. While this discussion quickly dives into the details beyond what we really need to know, it's worth the effort to read and understand it.

Here's the relevant statement.


In order to make the placement of controls on forms and reports as precise as possible, base measurements are expressed in Twips.

However, humans are trained to look for inches or centimeters as the unit of measurement, so we often use this conversion to facilitate that.

Long-time, experienced Access developers have internalized the equivalence between Twips and "human" units of measurement.

On the other hand, to make it easier to code, many of us define a global constant to use instead of that number when applying dimensions.

Public Const mTwip = 1440

Then they can refer to mTwip without having to remember the exact number.

Sometimes, youth and enthusiasm are very helpful, but other times, a bit of experience and education are more helpful.

Twip is the measurement for pixels on a screen. While this discussion quickly dives into the details beyond what we really need to know, it's worth the effort to read and understand it.

Here's the relevant statement.


In order to make the placement of controls on forms and reports as precise as possible, base measurements are expressed in Twips.

However, humans are trained to look for inches or centimeters as the unit of measurement, so we often use this conversion to facilitate that.

Long-time, experienced Access developers have internalized the equivalence between Twips and "human" units of measurement.

On the other hand, to make it easier to code, many of us define a global constant to use instead of that number when applying dimensions.

Public Const mTwip = 1440

Then they can refer to mTwip without having to remember the exact number.

Sometimes, youth and enthusiasm are very helpful, but other times, a bit of experience and education are more helpful.

Twip is the measurement for pixels on a screen. While this discussion quickly dives into the details beyond what we really need to know, it's worth the effort to read and understand it.

Here's the relevant statement.


In order to make the placement of controls on forms and reports as precise as possible, base measurements are expressed in Twips.

However, humans are trained to look for inches or centimeters as the unit of measurement, so we often use this conversion to facilitate that.

Long-time, experienced Access developers have internalized the equivalence between Twips and "human" units of measurement.

On the other hand, to make it easier to code, many of us define a global constant to use instead of that number when applying dimensions.

Public Const mTwip = 1440

Then they can refer to mTwip without having to remember the exact number.

I understand now, but my question still remains. 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. I accept that it is what is. BTW, in reference to your opening, I learned Autocoder (the Sanskrit of IBM computer languages) in 1965, but, still enthusiastic.

Thanks for the information

 

[MajP]

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.

I explained that already. Regional Settings. If I do it in inches and you do it in metric then the code is not going to work in other locations. It has to store in a common format. They could have picked and stored in inches and then those in metrics would have to really get out the calculator.

 

[jpl458]

I explained that already. Regional Settings. If I do it in inches and you do it in metric then the code is not going to work in other locations. It has to store in a common format. They could have picked and stored in inches and then those in metrics would have to really get out the calculator.

My reginal setting are set to the US. I don't want to belabor this but my questions remains regarding listboxes. If I can set the column widths in inches, which are converted to twips by ACCESS, shouldn't it do the conversion for sizing the listbox itself? I kow we can't change it, but it seems inconsistent, (and trivial).

 

[GPGeorge]

Design changes will need to be made while the form is in design view.

My reginal setting are set to the US. I don't want to belabor this but my questions remains regarding listboxes. If I can set the column widths in inches, which are converted to twips by ACCESS, shouldn't it do the conversion for sizing the listbox itself? I kow we can't change it, but it seems inconsistent, (and trivial).

Which event is being used?

 

[MajP]

shouldn't it do the conversion for sizing the listbox itself?

I guess they could have wrote the class properties to handle regional settings. I think working in twips is actually the standard for most applications, so it is probably on purpose not to use regional settings. It may seem foreign to you, but for us who code a lot in different applications it is the norm. They could not write a single property that would know to use regional settings or Twips, but they could have had two seperate properties. But that seems a lot of work.

Width and WidthTwips
me.lstOne.width = 2
me.lstOne.WidthTwips = 2880

To make it even easier than the constant you can make a function

Public Function TwipsToInches(iTwips As Long) As Double
  TwipsToInches = iTwips / 1440
End Function
Public Function InchesToTwips(Inches As Double) As Long
  InchesToTwips = 1440 * Inches
End Function

me.lstOne.width = TwipsToInches(8.67)

 

[cheekybuddha]

I suspect it has more to do with simplicity of implementation.

From the Access.Listbox docs:

ListBox.Width property (Access)​

• Article
• 09/13/2021
• 2 minutes to read
• 6 contributors

Gets or sets the width of the specified object in twips. Read/write Integer.

The property is an Integer datatype (as are most dimensional properties like Width, Height, Left, Top of most Access objects).

This makes sense since they are single value properties.

Now when they implemented ColumnWidths in a ListBox, my guess is that they were lazy and couldn't be bothered/decided it wasn't worth coding a dynamically added/deleted column object for each column every time you changed the amount of columns, so you couldn't specify:
MyListbox.Column(0).Width = 1234 ' Read/Write Integer

Instead it was easier to just use a single text property that the ListBox object parsed.
From the same docs (different page):

ListBox.ColumnWidths property (Access)​

• Article
• 01/22/2022
• 2 minutes to read
• 7 contributors

Use the ColumnWidths property to specify the width of each column in a multiple-column list box. Read/write String.

This time the property is a string which the object parses (using the list separator defined in Regional Settings!).

Since they are doing some parsing, it's not much extra bother to code in parsing where sensible units are specified (ie inches/cm) and make the conversion to twips internally for rendering on screen.

Had they decided to dynamically add/ remove columns as the developer changed the ColumnCount property, then I bet the property would have been a single-value Integer in twips!

 

[jpl458]

Design changes will need to be made while the form is in design view.

Which event is being used?

I have several buttons that launch queries and use the same listbox as the display area for the queries. Here is the cod in th Onclick event of a button.

Me.QryResulttb.Visible = True
Me.QryResulttb.Height = 3000
Me.QryResulttb.Width = 3880
Me.QryResulttb.RowSourceType = "Table/Query"
Me.QryResulttb.RowSource = "QryCountNames"
Me.QryResulttb.ColumnCount = 2

 

[Pat Hartman]

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

I will, when they work.

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