9 Replies - 2215 Views - Last Post: 18 April 2013 - 05:59 PM Rate Topic: -----

#1 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

horizontal scroll bar

Posted 16 April 2013 - 04:03 AM

Hi
Does anyone know I can put a horizontal scroll bar on a Listbox in VB excel. I have tried changing the width of the list box but has no affect. I have tried wrapping the column in the excel sheet same affect.
Thanks
Is This A Good Question/Topic? 0
  • +

Replies To: horizontal scroll bar

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3812
  • View blog
  • Posts: 13,522
  • Joined: 12-December 12

Re: horizontal scroll bar

Posted 16 April 2013 - 04:15 AM

Which ListBox? The Form Control doesn't have this feature and an ActiveX Control (on a worksheet) introduces horizontal and vertical scrollbars if the content is too wide or tall. You may perhaps have to set specific column widths to force the horizontal one to appear.
Was This Post Helpful? 0
  • +
  • -

#3 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: horizontal scroll bar

Posted 16 April 2013 - 04:42 AM

[ ListBox1.Width = 300
ListBox1.AddItem Range("A" & nrow).Value + " " + " Instrument Type: " + Range("C" & nrow).Value
ListBox1.AddItem Range("B" & nrow).Value ][/code]
Hi andrewsw
Above is the part of code I use to fill the listbox. Not sure if it makes sence but it work. The problem is when the string returned form range b&nrow is large there is noway of scrolling across the list litsbox to see the full text.
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: horizontal scroll bar

Posted 16 April 2013 - 09:48 PM

You need to send an LB_SETHORIZONTALEXTENT message to the window, using the SendMessage function from the Windows API. See here for a simple example.

This is a good opportunity for you to learn the basics of using the Windows API, by the way. If you get good at it, you can do all kinds of tricks. Post back with questions if you're interested.

This post has been edited by BobRodes: 16 April 2013 - 09:49 PM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3812
  • View blog
  • Posts: 13,522
  • Joined: 12-December 12

Re: horizontal scroll bar

Posted 16 April 2013 - 11:05 PM

I suppose a simpler approach would be to determine how many characters are in the new item you are about to add to the listbox and, if greater than a certain number, make the Width wider.
Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: horizontal scroll bar

Posted 17 April 2013 - 11:45 PM

If you are using a fixed-width font like this one in your list box, that probably is simpler. If you're using a proportionally-spaced font (like this one), then you can't really use that approach. Strings of characters can vary widely in size then. To take an extreme example, here are 20 i's and 20 w's:

iiiiiiiiiiiiiiiiiiii
wwwwwwwwwwwwwwwwwwww

You have to get the width of a ps font in points, typically, and that can get messy.

Note also

iiiiiiiiiiiiiiiiiiii
wwwwwwwwwwwwwwwwwwww


Where that approach could work. One of the reasons that fixed-width fonts are less popular is they take up a good deal more space than a ps font of comparable point size.

This post has been edited by BobRodes: 18 April 2013 - 12:00 AM

Was This Post Helpful? 0
  • +
  • -

#7 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: horizontal scroll bar

Posted 18 April 2013 - 12:32 AM

View PostBobRodes, on 17 April 2013 - 11:45 PM, said:

If you are using a fixed-width font like this one in your list box, that probably is simpler. If you're using a proportionally-spaced font (like this one), then you can't really use that approach. Strings of characters can vary widely in size then. To take an extreme example, here are 20 i's and 20 w's:

iiiiiiiiiiiiiiiiiiii
wwwwwwwwwwwwwwwwwwww

You have to get the width of a ps font in points, typically, and that can get messy.

Note also

iiiiiiiiiiiiiiiiiiii
wwwwwwwwwwwwwwwwwwww


Where that approach could work. One of the reasons that fixed-width fonts are less popular is they take up a good deal more space than a ps font of comparable point size.


Hi Bob
I tried that example that you gave me and it did not work. Does it matter I am using vb through excel in windows 7. I also tired making the listbox smaller and there is a auto horizontal scroll below a width of 60 anything over this it disappears.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3812
  • View blog
  • Posts: 13,522
  • Joined: 12-December 12

Re: horizontal scroll bar

Posted 18 April 2013 - 03:03 AM

View PostBobRodes, on 18 April 2013 - 06:45 AM, said:

If you are using a fixed-width font like this one in your list box, that probably is simpler. If you're using a proportionally-spaced font (like this one), then you can't really use that approach. Strings of characters can vary widely in size then. To take an extreme example, here are 20 i's and 20 w's:

iiiiiiiiiiiiiiiiiiii
wwwwwwwwwwwwwwwwwwww

You have to get the width of a ps font in points, typically, and that can get messy.

Note also

iiiiiiiiiiiiiiiiiiii
wwwwwwwwwwwwwwwwwwww


Where that approach could work. One of the reasons that fixed-width fonts are less popular is they take up a good deal more space than a ps font of comparable point size.

I was just considering using two widths (not attempting to determine or assume a character width). The main width, and a slightly wider width if the number of characters exceed, say 30, characters. This wouldn't be perfect, but it would be simple to implement.

Regards, Andy.
Was This Post Helpful? 0
  • +
  • -

#9 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: horizontal scroll bar

Posted 18 April 2013 - 05:46 PM

Yes, it would. I've tried something like it before, and it works well with fixed-width fonts. I've never been happy with the results using PS fonts though. I prefer to use the horizontal scrollbar personally.
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: horizontal scroll bar

Posted 18 April 2013 - 05:59 PM

Nocando, I've done a little research on this and it appears that the Excel ListBox doesn't expose an hWnd property directly. While you can get the handle using the FindWindow function, I also found this:

Quote

Scrollbars in a VBA ListBox control are automatic. There are 2 ways you can go about it. First, change the column width to be greater than the width of the ListBox. Or second, if you do have more than 1 column changes the widths of the columns so that they are wider than the ListBox width. There you have it!
This appears to bear out your experience. Perhaps you can experiment with the column width and the width of the ListBox and get the behavior you want.

This post has been edited by BobRodes: 18 April 2013 - 06:00 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1