By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,729 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to Dynamically set the Combo Drop Down List Width

ADezii
Expert 5K+
P: 8,627
This week’s Tip of the Week will clearly demonstrate how you can dynamically set the Drop Down List Width of a Combo Box to the length of the longest item in its Row Source. The inspiration for this Tip came from one of our own resident Experts, mshmyob. In response to a Thread relating to this very Topic, mshmyob came up with a rather ingenious method to accomplish this task. He computed the Average Character Width of a String consisting of Upper and Lower case characters as well as the numbers 0 to 9, then applied this logic to determine the List Width of the Combo Box. This he did by calculating the length of the longest entry in the Combo Box, determined the current Font Name and Size, utilizing the TextWidth Method of a Report opened in Hidden Mode, computing the Average Character Width, then transferring this value back to the Form (to a Text Box), where the ListWidth of the Combo Box can now be calculated to accommodate the longest entry. What makes this approach unique is that:
  • This is all accomplished internally within Access with no external References or API Calls.
  • Regardless of the Font Size and Name, the code will adjust and always produce the desired results.
  • Since Forms do not have a TextWidth Method, an alternative method had to be realized in order to solve this dilemma. mshmyob’s use of the TextWidth Method within a Hidden Report, was not at all intuitive, and in fact was very clever.
I’ll post the code below for your preview. It is amply commented, even bordering on overkill, but it should give you a good indication of exactly what is happening. These code modifications have been made with mshmyob’s approval, but keep in mind that the original concept was his, not mind. Even with the varied comments, and what I feel is a sound and logical code based approach, I have made this Tip available as an Attachment. I strongly suggest that you download it and get a visual indication of how this functionality is actually implemented. Should you have any questions whatsoever, please feel free to ask them. I’ll be monitoring this Thread as I’m sure mshmyob will be also.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim lngNumberOfEntries As Long
  3. Dim lngLongestRowlength As Long
  4. Dim dblLargestCboEntryWidth As Double
  5.  
  6. Call IntroDialog
  7.  
  8. 'gcboCtl is a Global Object Variable of Type Combo Box
  9. 'Initialize it now
  10. Set gcboCtl = Me![cboAdjust]
  11.  
  12. lngNumberOfEntries = gcboCtl.ListCount
  13.  
  14. If lngNumberOfEntries = 0 Then Exit Sub     'No entries, Bye-Bye!
  15.  
  16. 'Retrieve the longest Row of data from the Combo Box via the Function, pass
  17. 'to the Function the number of entries in the Combo Box
  18. lngLongestRowlength = fGetLongestRowLength(lngNumberOfEntries)
  19.  
  20. 'Determine Font Size and Font Name in use and Save these values in Global
  21. 'Variables instead of passing Arguments to the Report, then retrieving them
  22. gintFontSize = gcboCtl.FontSize
  23. gstrFontName = gcboCtl.FontName
  24.  
  25. 'Open the Report, but in Hidden Mode so you can apply Parameters to it such as
  26. 'FontSize and FontName. We need to use a Report because Access's TextWidth
  27. 'Method only works in a Report, and not a Form
  28. DoCmd.OpenReport "rptAvgString", acViewPreview, , , acHidden
  29.  
  30. 'txtAvgValue holds the value passed back from the Report and is the Average
  31. 'Character Width based on the pre-determined String (strMessage) as defined
  32. 'in the Print() Event of the Report. You can hide the txtAvgValue Text Box,
  33. 'I just used this to show you how it works. Multiply the Average Character
  34. 'Width by the longest value in the Combo Box to arrive at the Length of the
  35. 'longest entry in Twips.
  36. dblLargestCboEntryWidth = Me.txtAvgValue * lngLongestRowlength
  37.  
  38. 'Adjust the Drop Down List Width of the Combo Box
  39. gcboCtl.ListWidth = dblLargestCboEntryWidth
  40.  
  41. 'Close the 'Hidden' Report
  42. DoCmd.Close acReport, "rptAvgString", acSaveNo
  43.  
  44. Call FillDescription    'Program Description in Form Label
  45. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  2. Dim rpt As Report
  3. Dim strMessage As String
  4. Dim dblAvgSize As Double
  5. Const conTWIPS As Byte = 1
  6.  
  7. Set rpt = Me
  8.  
  9. 'Create a string with all upper and lowercase characters, This process will allow
  10. 'the creation of an'Average Characer Width in Twips given the Font's Name and Size.
  11. strMessage = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
  12.  
  13. With rpt
  14.   'Set ScaleMode to Twips, and set FontName and FontSize properties.
  15.   'Character size is based on Font Size and Font Name - set as Twips not Pixels
  16.   .ScaleMode = conTWIPS
  17.   .FontName = gstrFontName
  18.   .FontSize = gintFontSize
  19. End With
  20.  
  21. 'Use the TextWidth method to determine the Horizontal Width - Vertical is unimportant.
  22. 'The value returned by the TextWidth method is expressed in terms of the coordinate system
  23. 'in effect for the Report, as defined by the Scale Method
  24. dblAvgSize = rpt.TextWidth(strMessage) / 62
  25.  
  26. 'Pass this number back to the calling form to calculate length needed
  27. Forms!frmListWidth.txtAvgValue = dblAvgSize
  28. End Sub
Aug 17 '08 #1
Share this Article
Share on Google+
30 Comments


FishVal
Expert 2.5K+
P: 2,653
Nice work.

Though I'm very unsure it is an optimal approach at all.
IMHO Win API functions (particularly those from GDI32 and User32) having an exclusive responsibility on rendering graphics might have a "last word" in determining of expecting results.

Here is what simple googling give.

Kind regards,
Fish

P.S. Cheers to the developers anyway.
Aug 18 '08 #2

ADezii
Expert 5K+
P: 8,627
Nice work.

Though I'm very unsure it is an optimal approach at all.
IMHO Win API functions (particularly those from GDI32 and User32) having an exclusive responsibility on rendering graphics might have a "last word" in determining of expecting results.

Here is what simple googling give.

Kind regards,
Fish

P.S. Cheers to the developers anyway.
Great point FishVal, but I think the beauty of mshmyob's approach is that everything is handled internally within Access, no API Declarations and implementations, no Type...End Type Declarations, no Multiple Constant Declarations, etc. I also agree that the API has the 'last word' in rendering Graphics, but I am a KISS Principal Advocate, and although the API offers limitless functionality, it can just as easily cause Fatal Errors. I'm sure I will cause a stir with these Comments, let's just wait and see what other, varied, opinions may be.
Aug 18 '08 #3

FishVal
Expert 2.5K+
P: 2,653
And you are right ADezii. ;)

Though I still stay on my own in the following:
  • The best way to prevent Fatal Errors is to keep computer switched off.
  • The most frequent reason for Fatal Errors is users noobness.
  • Access native objects could easily crash too.
  • Lack of Combobox.TextWidth or even Combobox.AutoFit properties is a shame of M$ developers.
  • Report.TextWidth property is sure to use API, otherwise it will not work at all or will work in a way making happy nobody.
  • Using API certainly requires more code. More code - more bugs. Some DLL errors could not be trapped with VBA code - really it is still considered to be a macrolaunguage.
  • But as soon as the code has been debugged properly it is expected too be more efficient that may be crucial when processing multiple strings in batch.

And the last but not the least - mshmyob's algorithm has a flaw. I've added string consisting of dots and the code failes to fit list width. Assuming a string with maximal character count to be the longest string when rendered is not a proof way as well as calculating symbol average width on alphabet - it should be calculated at least on more representative collection reflecting character frequency.

Regards,
Fish

P.S. BTW what is being calculated when dividing twips count by 62?
Aug 18 '08 #4

mshmyob
Expert 100+
P: 903
Hello Fish,

Your point about the algorithm failing when a field is populated with say all periods - I agree will fail. I thought of that but under normal conditions nobody will fill the field with all non alphanumeric data. If that is the case then a slight change to the algorithim would be needed.

The divide by 62 is to divide the string that consists of the upper and lower case letters and numbers 0-9. There are 62 characters if you count them (26 upper, 26 lower, 10 numbers) Just getting a rough average character width based on those characters going into the table. You could add non alpahnumeric characters if you wish.

I think this method works pretty close for almost all situations and just shows how with some slight changes you could accomodate someone entering all periods.

Switching off computer now to avoid fatal errors :).

cheers,

And you are right ADezii. ;)

Though I still stay on my own in the following:
  • The best way to prevent Fatal Errors is to keep computer switched off.
  • The most frequent reason for Fatal Errors is users noobness.
  • Access native objects could easily crash too.
  • Lack of Combobox.TextWidth or even Combobox.AutoFit properties is a shame of M$ developers.
  • Report.TextWidth property is sure to use API, otherwise it will not work at all or will work in a way making happy nobody.
  • Using API certainly requires more code. More code - more bugs. Some DLL errors could not be trapped with VBA code - really it is still considered to be a macrolaunguage.
  • But as soon as the code has been debugged properly it is expected too be more efficient that may be crucial when processing multiple strings in batch.

And the last but not the least - mshmyob's algorithm has a flaw. I've added string consisting of dots and the code failes to fit list width. Assuming a string with maximal character count to be the longest string when rendered is not a proof way as well as calculating symbol average width on alphabet - it should be calculated at least on more representative collection reflecting character frequency.

Regards,
Fish

P.S. BTW what is being calculated when dividing twips count by 62?
Aug 18 '08 #5

ADezii
Expert 5K+
P: 8,627
And you are right ADezii. ;)

Though I still stay on my own in the following:
  • The best way to prevent Fatal Errors is to keep computer switched off.
  • The most frequent reason for Fatal Errors is users noobness.
  • Access native objects could easily crash too.
  • Lack of Combobox.TextWidth or even Combobox.AutoFit properties is a shame of M$ developers.
  • Report.TextWidth property is sure to use API, otherwise it will not work at all or will work in a way making happy nobody.
  • Using API certainly requires more code. More code - more bugs. Some DLL errors could not be trapped with VBA code - really it is still considered to be a macrolaunguage.
  • But as soon as the code has been debugged properly it is expected too be more efficient that may be crucial when processing multiple strings in batch.

And the last but not the least - mshmyob's algorithm has a flaw. I've added string consisting of dots and the code failes to fit list width. Assuming a string with maximal character count to be the longest string when rendered is not a proof way as well as calculating symbol average width on alphabet - it should be calculated at least on more representative collection reflecting character frequency.

Regards,
Fish

P.S. BTW what is being calculated when dividing twips count by 62?
Hello again, FishVal. As always, your points are well taken and always respected. It is true that the TextWidth() Method must, of course, access the API in some manner, but the hugh difference is that the functionality is encapsulated within the Method which has thoroughly been debugged and ultra efficient. It is, in my mind, a major step to mimic this functionality and access the API directly. Would you agree?

You are also correct about the flaw but as stated by mshmyob, logic can easily be incorporated into the code that will offset this, namely the occurrences of periods, or other similar characters in the String. A really nice pick up, I would never have thought of this!

P.S. - If I ever get the time, I will run Benchmark Tests comparing msymyob's approach, to that which you have demonstrated, on very long Combo Box RowSources. Care to bet who the winner will be?
Expand|Select|Wrap|Line Numbers
  1. msymyob vs. The API       'seems like an old Godzilla Movie! (LOL).
Aug 18 '08 #6

FishVal
Expert 2.5K+
P: 2,653
Hello.

I hope you don't take my critisism personally. ;)
I intend to help you make the code better.

So I'd like to add a few more points:
  • A mistic 62 divisor is now clear for me. ;)
  • Flat alphabetic list IMHO does not give statistically relevant results as for symbol average width. Letter frequency should be taken into account to enhance result relevancy. Though all statistical tricks work better the larger collection is taken. ;)
  • Though Report.TextWidth takes into account CR/LF in text, your code still calculates full string width.
  • Again, a text with maximal symbol count is not the longest text when rendered.

Single bricks are quite good, but the whole building is not enough strong, IMHO.
I would recommend you to rethink the whole logic and issue the next version ;).

Regards,
Fish
Aug 18 '08 #7

FishVal
Expert 2.5K+
P: 2,653
Hello again, FishVal. As always, your points are well taken and always respected. It is true that the TextWidth() Method must, of course, access the API in some manner, but the hugh difference is that the functionality is encapsulated within the Method which has thoroughly been debugged and ultra efficient. It is, in my mind, a major step to mimic this functionality and access the API directly. Would you agree?
He-he.

4 years ago I've bought Kodak Z780 soapbox and was quite happy.
Now I could hardly agree with all its "friendly automatics" and dream about camera with at least hand-focus available. :D

I was one from that kids with daemonic fire in eyes coding in asm.

But these happy days were years ago when I was student with no money, no home, no wife and no children. Now I prefer to do less and to get more to afford myself at least a half of that fun I had. :D

Regards,
Fish
Aug 18 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Ok, gentlemen.

Some more points:
  • I think these stuff could be nicely implemented as class built over Access.ComboBox class.
  • User may be allower to adjust list width manually with an option to store user changes - some kind of design mode emulation.
  • Too long entries may be displayed separately in some kind of "status line" or popup.

Hope these point are not drastically off topic.

Regards,
Fish
Aug 18 '08 #9

Expert Mod 2.5K+
P: 2,545
Hi All. This has been a very interesting discussion, but perhaps at this stage we should simply recognise that mshmyobs' method is a very good starter for achieving something not done at all using native Access facilities.

As ADezii pointed out, mshmyob's solution is entirely self contained. Fish is right about windows API calls being better/more efficient/more general. But, the wrappers for API code are hardly easy for users to read and understand when trying to work out what the code does - unless time and trouble has been taken to translate low-level numeric values into human-readable constants and so on. And misplaced or missing dlls can break the code anyway. At least in native VBA it is easy enough to work out what is going on...

Thank you to mshmyob for the work that has gone into this, and to ADezii for posting the thread for all to read. Thanks also to Fishval for reviewing the code and suggesting potential improvements.

Ahh, the lure of writing perfect code - I might one day get there, but I haven't been able to achieve this yet despite more than 25 years of trying...

Cheers

Stewart
Aug 18 '08 #10

mshmyob
Expert 100+
P: 903
LOL @ corrected years of perfect code. Combined we have 50 years and M$ must have a billion and they aren't perfect yet either.

cheers,

.... Ahh, the lure of writing perfect code - I might one day get there, but I haven't been able to achieve this yet despite more than 25 years of trying...

.....
Stewart
Aug 18 '08 #11

nico5038
Expert 2.5K+
P: 3,072
Just my $0.02 "contribution", why would I need this?
Problem with this solution is the fact that there can be another control on the right of the combo box. . . . .

I prefer to control my form layout in the design phase, the option to widen the expanded combobox automatically will be sufficient for me.

For solving this problem I would probably would have tried to launch a datasheet sub form with the longest field first and, after selecting the column, apply the resize method to be able to read the field width Access calculated for me...

Nic;o)
Aug 18 '08 #12

ADezii
Expert 5K+
P: 8,627
Just my $0.02 "contribution", why would I need this?
Problem with this solution is the fact that there can be another control on the right of the combo box. . . . .

I prefer to control my form layout in the design phase, the option to widen the expanded combobox automatically will be sufficient for me.

For solving this problem I would probably would have tried to launch a datasheet sub form with the longest field first and, after selecting the column, apply the resize method to be able to read the field width Access calculated for me...

Nic;o)
Problem with this solution is the fact that there can be another control on the right of the combo box. . . . .
That's what I thought was one of the benefits of this code: only the Drop Down List Width becomes dynamically re-sized, not the Text Box Component. No Controls to the right of the Combo would be affected. Controls may be overshadowed by the Drop Down, but to me that is irrelevant. Done rambling, thanks for joining in, Nico.
Aug 18 '08 #13

P: 69
I've followed this thread with huge interest and congratulate those who have produced really elegant code BUT I'm still in the KISS camp when it comes to the usefulness of variable length drop-down lists. I think we have to recognise that the drop-down list isn't a permanent hog of screen real estate and use this feature with caution.

How many of you have (like me) pored over code written months ago and wondered what on earth you originally meant it to do? And I'll bet you've had to do this at 3am! I know I have, and I curse myself for dreaming up cute little twirls which really were never necessary.

Enough said. I've downloaded the zip file and I know I'll use it!!
Aug 19 '08 #14

FishVal
Expert 2.5K+
P: 2,653
Hello, gentlemen.

As soon as nobody comes with this obvious question I will ask it. :)
What for is that average symbol width calculation at all?
Exactly the same with higher precision and for less money may be done by calculation screen width of the longest list item directly.

IMHO it is not perfect too, but at least more reliable.

Regards,
Fish
Aug 22 '08 #15

mshmyob
Expert 100+
P: 903
Good question Fish.

It is to take into account the errors in calculating the actual screen width of the list item like you pointed out in previous posts.


Take a simple example of 2 list items.

The 1st list item consists of 10 alphanumeric characters
The 2nd list item consists of 10 alphanumeric items and 1 period and 1 exclamation mark.

As we all know the period and exclamation mark won't have the same screen "footprint" has the alphanumeric characters.

But using the simple alogorithm I used of just grabbing the list item that has the most characters the 2nd list item will be be assumed to have the larger screen "footprint".

I therefore want to treat the non alphanumeric characters as having the same average screen footprint as alphanumeric characters. This will ensure that a few non alphanumeric characters won't result in a list width that is too narrow.

Hope that makes sense.

cheers,

Hello, gentlemen.

As soon as nobody comes with this obvious question I will ask it. :)
What for is that average symbol width calculation at all?
Exactly the same with higher precision and for less money may be done by calculation screen width of the longest list item directly.

IMHO it is not perfect too, but at least more reliable.

Regards,
Fish
Aug 22 '08 #16

mshmyob
Expert 100+
P: 903
This is interesting??????

My previous post (#16) to Fish explaining why I used the average character width is still correct and that was intention all along and should have always taken into account someone using all periods etc.

But doing some testing I noticed that Access LEN function doesn't give the proper number when you use periods.

If you put in 1 period you get a length of 1
If you put in 5 periods you get a length of 3
If you put in 10 periods you get a lenth of 4

Anyone know what the LEN function is actually doing?? Anyone else notice this strange behaviour??

I am using AC2007.

cheers,

PS - after more testing this happens only with the period character.
Aug 22 '08 #17

Expert Mod 2.5K+
P: 2,545
Hi mshmyob. Len appears to behave correctly in Access 2003 with various period strings. For instance just testing Len() in the immediate window of the VBE reports
Expand|Select|Wrap|Line Numbers
  1. len("..........")
correctly as 10, and every other value I've tested.

Maybe you've discovered another A2007 bug...

-Stewart
Aug 22 '08 #18

mshmyob
Expert 100+
P: 903
I get 10 also when I do it that way but from the combo box bound to a table it doesn't give a valid number.

cheers,

Hi mshmyob. Len appears to behave correctly in Access 2003 with various period strings. For instance just testing Len() in the immediate window of the VBE reports
Expand|Select|Wrap|Line Numbers
  1. len("..........")
correctly as 10, and every other value I've tested.

Maybe you've discovered another A2007 bug...

-Stewart
Aug 22 '08 #19

Expert Mod 2.5K+
P: 2,545
Hi myshmyob. There is a simpler explanation; as you type the period characters in the combo, groups of 3 periods are autocorrected and replaced by the elipsis character - you will see that the ellipsis looks smaller than the period characters within the combo. 3 eillipsis plus one period gives 4 characters when 10 periods are entered - so len is behaving correctly.

-Stewart
Aug 22 '08 #20

mshmyob
Expert 100+
P: 903
Then that explains why when Fish put in all periods the code failed to properly accomodate the drop down width.

Why does M$ change the periods?

cheers,

Hi myshmyob. There is a simpler explanation; as you type the period characters in the combo, groups of 3 periods are autocorrected and replaced by the elipsis character - you will see that the ellipsis looks smaller than the period characters within the combo. 3 eillipsis plus one period gives 4 characters when 10 periods are entered - so len is behaving correctly.

-Stewart
Aug 22 '08 #21

Expert Mod 2.5K+
P: 2,545
The 'autocorrect/autoformat as you type' is common to the Office applications, although it is perhaps least expected in Access. At least in Word you can change all its options from the main menu.

The replacement of three consecutive periods '...' by the single-character ellipsis '…' isn't as well known as the soft replacement of single quotes by curly quotes, for instance. In proportionally-spaced fonts such as this one the ellipsis looks much the same as the period character (as you will see above); It is easier to show the differences in a monospace font as shown below:

Expand|Select|Wrap|Line Numbers
  1. … … … < three ellipsis separated by spaces
  2. ... ... ... < three groups of three periods with spaces in between
The ellipsis is a special character often used to indicate things missed out:

In a quote: "In formal papers ellipsis are used to indicate edits in … quotes where things have been missed out"

In a list: 1, 2, 3, … , n - 1, n

-Stewart
Aug 23 '08 #22

mshmyob
Expert 100+
P: 903
If you disable the autocorrection of the ellipse the code works as should even if you put in all periods.

cheers,
Aug 23 '08 #23

FishVal
Expert 2.5K+
P: 2,653
Hello, gentlemen.

Lets continue our interesting discussion. :)
I was somewhat under impression that Report.TextWidth method is slow and multiple callinngs could reduce performance drastically. That was actually the only obvious reason to implement "symbol average width estimation" algorithm.

First I tried to run this method in loop. Ooops! 100k callings were made in less than 3 sec. So I've designed a simple database implementing a method of screen width determination of all combobox list items.

Access 2003, Win XP SP3, CPU: Celeron 3.33GHz, RAM: 2Gb

Combobox with 3340 list items is being processed in less than 1s.

Though there are still several issues:
  • ListBox.ListWidth property sets an outer border of listbox portion (including text margins and scrollbar width). So far I just set combobox width manually in design mode to width of scrollbar+margins). Sure there are a numerous better ways.
  • The code is just a case study to check feasibility of the approach in terms of performance. So it doesn't support multicolumn comboboxes, though one skill in art ;) could easily add this functionality.
  • Still there are some issues with width determination precision. I observe slight deviations in different conditions. The reason/s is/are not completely clear yet.

..... going to attach the db.

Regards,
Fish
Attached Files
File Type: zip TextWidth.zip (118.8 KB, 271 views)
Aug 25 '08 #24

ADezii
Expert 5K+
P: 8,627
Hello, gentlemen.

Lets continue our interesting discussion. :)
I was somewhat under impression that Report.TextWidth method is slow and multiple callinngs could reduce performance drastically. That was actually the only obvious reason to implement "symbol average width estimation" algorithm.

First I tried to run this method in loop. Ooops! 100k callings were made in less than 3 sec. So I've designed a simple database implementing a method of screen width determination of all combobox list items.

Access 2003, Win XP SP3, CPU: Celeron 3.33GHz, RAM: 2Gb

Combobox with 3340 list items is being processed in less than 1s.

Though there are still several issues:
  • ListBox.ListWidth property sets an outer border of listbox portion (including text margins and scrollbar width). So far I just set combobox width manually in design mode to width of scrollbar+margins). Sure there are a numerous better ways.
  • The code is just a case study to check feasibility of the approach in terms of performance. So it doesn't support multicolumn comboboxes, though one skill in art ;) could easily add this functionality.
  • Still there are some issues with width determination precision. I observe slight deviations in different conditions. The reason/s is/are not completely clear yet.

..... going to attach the db.

Regards,
Fish
Hello FishVal, got a chance to look at your code, impressive as always. It was a little tricky, but I figured out how to calculate the width of the Vertical Scroll Bar in Twips. It was tricky in the sense that the API likes to make returns in Pixels NOT Twips, but here goes. I've also attached the Test DB if anyone is at all interested. I can't see many circumstances in which this code can actually be used, but it is interesting, nonetheless. I've provided what I consider to be ample comments in the code so you can get a clear picture of what is going on:
  1. Required Declarations
    Expand|Select|Wrap|Line Numbers
    1. Public Declare Function GetSystemMetrics Lib "User32" (ByVal nIndex As Long) As Long
    2. Public Declare Function GetDC Lib "User32" (ByVal hWnd As Long) As Long
    3. Public Declare Function GetDeviceCaps Lib "GDI32" (ByVal hde As Long, ByVal nIndex As Long) As Long
  2. Function Code
    Expand|Select|Wrap|Line Numbers
    1. Public Function fVScrollBarWidthInTwips()
    2. On Error GoTo Err_fVScrollBarWidthInTwips
    3. Dim lngDC As Long
    4. Dim lngLogPixelsPerInch As Long
    5. Dim lngTwipsPerPixel As Long
    6. Const conHWND_DESKTOP = 0
    7. Const conLOGPIXELSX = 88
    8. Const conTWIPS_PER_INCH = 1440
    9. Const conSM_CXVSCROLL = 2
    10.  
    11. 'Obtain a Device Context for the Screen Device using the Handle for
    12. 'the Windows Desktop. A Device Context allows Windows to communicate
    13. 'with a specific Device, once we obtain it for the Screen, we can
    14. 'then retrieve information about it.
    15. lngDC = GetDC(conHWND_DESKTOP)
    16.  
    17. If lngDC <> 0 Then
    18.   'Obtain the Logical Pixels per Inch in the Horizontal direction
    19.   'GetDeviceCaps() retrieves specific Device capabilities
    20.   lngLogPixelsPerInch = GetDeviceCaps(lngDC, conLOGPIXELSX)
    21.  
    22.   'Finally, we can derive the Twips per Pixel Value
    23.   lngTwipsPerPixel = conTWIPS_PER_INCH / lngLogPixelsPerInch
    24.  
    25.   'Multiply the Vertical Scroll Bar width in Pixels as returned by
    26.   'GetSystemMetrics() by the number of Twips per Pixel
    27.   fVScrollBarWidthInTwips = GetSystemMetrics(conSM_CXVSCROLL) * lngTwipsPerPixel
    28. Else    'We are really in trouble!
    29.   MsgBox Err.Description, vbExclamation, "Could Not Retrieve Device Context"
    30.     Exit Function
    31. End If
    32.  
    33. Exit_fVScrollBarWidthInTwips:
    34.   Exit Function
    35.  
    36. Err_fVScrollBarWidthInTwips:
    37.   MsgBox Err.Description, vbExclamation, "Error in fVScrollBarWidthInTwips()"
    38.   Resume Exit_fVScrollBarWidthInTwips
    39. End Function
  3. Sample Function Call
    Expand|Select|Wrap|Line Numbers
    1. MsgBox "Scroll Bar Width in [Twips]: " & fVScrollBarWidthInTwips(), vbInformation, _
    2.        "Scroll Bar Demo"
Aug 25 '08 #25

ADezii
Expert 5K+
P: 8,627
Sorry guys, it looks like the olde forget the Attachment trick ! (LOL)!
Aug 26 '08 #26

FishVal
Expert 2.5K+
P: 2,653
...
It was a little tricky, but I figured out how to calculate the width of the Vertical Scroll Bar in Twips. It was tricky in the sense that the API likes to make returns in Pixels NOT Twips, but here goes. I've also attached the Test DB if anyone is at all interested. I can't see many circumstances in which this code can actually be used, but it is interesting, nonetheless
...
Cheers.

Nice solution.
Though people would blame it for using API. ;) LOL
Aug 26 '08 #27

ADezii
Expert 5K+
P: 8,627
Cheers.

Nice solution.
Though people would blame it for using API. ;) LOL
Thanks FishVal, although it does seem like a lot of work for obtaining the width of a 'Little Olde Scroll Bar'. (LOL).
Aug 26 '08 #28

FishVal
Expert 2.5K+
P: 2,653
Hello all.

Exploring Access hidden objects I've stumbled upon a method with a quite selfexplaining name - WizHook.TwipsFromFont method. :)
Brief googling gives quite enough to understand method usage.

Regards,
Fish
Dec 30 '08 #29

P: 1
I wish to employ this method in one of my combo boxes. Two questions arise:

1. Is there a file available that contains the code?

2. Where exactly do you place the code?

Thanks

Hawg11
Jan 24 '12 #30

ADezii
Expert 5K+
P: 8,627
@Hawg11:
Download the Attachment, it says it all.
Attached Files
File Type: zip ListWidth.zip (37.2 KB, 232 views)
Jan 24 '12 #31