Some questions about TextBoxes and vbTab

[JimmyBob]

Hi folks, just a few quick Excel VBA questions relating to a multiline textbox. Apologies if these have been covered previously.

1. I’m trying to send 6 visually separated columns of data to a textbox. Each of the 6 columns will be 1-8 characters in length and the data will extend downwards from 1-2000 lines.

My problem is that the columns don’t always line up when using vbTab i.e.
TextBox = TextBox & A & vbtab & B & vbTab & C & vbTab & D & vbTab & E & vbTab & F & vbNewLine
Can vbTab or any alternative be made to function similar to the tab key in Word (irrespective of fixed/non-fixed font size)…is there any textbox-solution to guarantee column line-up?

1. I could use 6 listboxes (or 6 textboxes?) – would it be feasible to suppress a vertical scrollbar to all but the right-most list/textbox and then allow the user to scroll down, while at the same time, linking the current scroll position with the remaining 5 boxes? (each box will have identical Listcounts).

1. If there is a data limit on the maximum size of each textbox or listbox (we may extend to around 5000 lines in the future), apart from using Access, is there any other way we can display this data to the user. VBA will be used to search and filter this data so a significantly less amount would be displayed to the user.

1. Are there any issues using VBA from a business perspective, in the context of standalone (VB6) support being withdrawn by Microsoft….I take it MS will support VBA?

Thanks, Jim.

 

[AlphaFrog]

You could use one multicolumn listbox. It can have several thousand rows. I don't know the max row limit.

Example code to populate a 6 column Listbox.

    [COLOR=darkblue]With[/COLOR] ListBox1
        .ColumnCount = 6
        .ColumnWidths = "40,40,60,40,80,40"
        .List = Sheets("Sheet1").Range("A1:F1000").Value
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[JimmyBob]

Thanks, AlphaFrog. This sounds just what I need. I think there used to be a 65K limit on the listbox size, something to do with an integer being used as the .listcount and .listindex properties (though I see folk talking about API). I'm not sure how this impacts a multicolumn listbox, but I'll give this a try with a test fill loop.