+ Reply to Thread
Results 1 to 5 of 5

Excel VBA - ListBox scrolling causes text alignment issue

  1. #1
    Registered User
    Join Date
    05-07-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    3

    Excel VBA - ListBox scrolling causes text alignment issue

    Hi all,

    I have a User Form with a ListBox containing a table with multiple rows and columns. The data source is pulling from Excel Worksheet.

    I encountered an issue with text alignment whenever I use the scroll enabled in the ListBox. It also causes smudge to the text for no reason.
    Note that the UserForm window size is set to xlMaximized. Can this be the source of the problem?

    Has anyone encountered this issue too? How can I fix this? Would highly appreciate your comments and acknowledgement.

    Thanks!
    Andrew
    Attached Images Attached Images
    Last edited by andrewjot; 05-11-2020 at 02:02 AM.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Excel VBA - ListBox scrolling causes text alignment issue

    Your problem is most likely caused by the use of column headers.
    The use of column headers indicates you have used the 'rowsource' method to populate your listbox.
    As you scroll the listbox it reads your sheet and re-populates the listbox, having to re-align columns with the header widths.
    Unfortunately 'rowsource' is the only loading method that allows column headers.
    Far better practice to load in from an array, which is only read in from your sheet once and held in memory.
    torachan.

  3. #3
    Registered User
    Join Date
    05-07-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Excel VBA - ListBox scrolling causes text alignment issue

    Hi torachan,

    Thank you so much for replying. Yes you're right, I used 'rowsource' to pull data from my worksheet and populate them in my listbox. I must agree with you about the source of the issue.

    I guess I have no other choice but to try out the array method. Until then, I will let you know how it goes. Really appreciate your help!

    Heap thanks,
    Andrew

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Excel VBA - ListBox scrolling causes text alignment issue

    Also if column widths are set too small to accommodate the full length of data from the cell this sometimes causes some sort of buffering effect while scrolling.
    Try limiting the physical length of data in each cell and then adjust column widths of the list box at least 10pts bigger.

  5. #5
    Registered User
    Join Date
    05-07-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Excel VBA - ListBox scrolling causes text alignment issue

    Hi torachan,

    Thank you for your suggestion again. Unfortunately, I have tried out all and the issue still persists. I suspect it's a bug caused by the Scroll, that the Dev. Team from Microsoft Excel isn't aware of.

    Like you suggested, I have tried using the array method to populate the listbox. I have also limited the length of data for each cell, as well as setting the column width to 200 pts (I'm sure that's big enough). However, the issue still persists and I can't waste too much time on this ridiculous matter. As for now, the best option i could think of is enlarging the ListBox size, so that there is no scroll needed.

    All in all, I am truly grateful for your help and let's hope that the dev. team can see this and the issue will be resolved in the future.

    Best regards,
    Andrew

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] lable text alignment issue in userform
    By shiva_raj in forum Excel General
    Replies: 1
    Last Post: 02-03-2017, 12:38 AM
  2. Excel 2013 Cell Alignment Issue
    By cheiss8078 in forum Excel General
    Replies: 3
    Last Post: 11-27-2015, 10:56 AM
  3. [SOLVED] Listbox then vlookup to set text issue
    By pyromorphous in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2015, 12:13 PM
  4. Fetching data from PDF to excel-Alignment issue
    By laxmanann in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-11-2014, 02:20 PM
  5. Scrolling alignment of cells?
    By AdamLord in forum Excel General
    Replies: 0
    Last Post: 01-05-2011, 06:55 AM
  6. [SOLVED] Text alignment within a listbox
    By ndm berry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2005, 09:05 AM
  7. Listbox Alignment Help
    By justchris in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1