PDA

View Full Version : [SOLVED] Set Variable of column number to Multicolumn ListBox



10shlomi10
12-19-2016, 06:17 AM
Hi !

I've got a UserForm with ListBox which load the data into it, gives a unique items , according to column A, and show the result in the ListBox.

As this UserForm is going to be in a couple of files, I want to set a Global Varibale to make it easy to handle, which columns to show on ListBox.
it will always be 2 columns (Only).
The problem is that the columns are located on sheet, on each File, in different order.

I have on the current file 15 column on sheet but I want to show only 2 columns on ListBox.
in this case: Column C and Column E

I've tried something like that:


'Declare Global static variable for the column of : AccountNumber
Public Const ColumnAccountNumber As Integer = 3

'Declare Global static variable for the column of : AccountNume
Public Const ColumnAccountName As Integer = 5

With Me.ListBox2
Me.ListBox2.ColumnCount = 2
Me.ListBox2.ColumnWidths = "0;0;60;0;60;0;0" 'Wondering, Is it possible to connect the position of each ColumnWidths to the Global variable ?
'Me.ListBox2.List(0, ColumnAccountNumber).Visible = True ' I'm getting error
End With


any help will be appreciated !: pray2:

Shlomi

p45cal
12-19-2016, 11:52 AM
you could make up the "0;0;60;0;60;0;0" string in code:
myStr = ""
For i = 1 To 7
If i = ColumnAccountName Or i = ColumnAccountNumber Then myStr = myStr & ";60" Else myStr = myStr & ";0"
Next i
myStr = Mid(myStr, 2)
then use it thus:
.ColumnWidths = myStr

Untested.
Are you sure you want to set the columncount to 2?

10shlomi10
12-19-2016, 01:23 PM
Dear p45cal

Many Thanks for your answer.

It works perfectly !:clap:

Shlomi:bow: