PDA

View Full Version : help with listbox columns...



lazyuser
02-19-2009, 01:18 PM
i had this code that worked for a list box. i had some text coming out of this list box and getting populated in 4 different
columns in a spreadsheet.

lboReporterType.AddItem xlSheet.Cells(row, col - 1) & " %- " & xlSheet.Cells(row, col) & " %s " & _
xlSheet.Cells(row, col + 1) & " %f " & xlSheet.Cells(row, col + 4) 'Add mulitiple lines into the combobox

however, the requirement now is that i need to display these values in four seperate columns in the listbox. i do not need
to append the delimiters ('%-', '%s' and '%f') here .

i then read the values from the listbox and update an excel spreadsheet as well as a text file (this part is existing
functionality). the code is here:

Dim ff As Long
Dim strBuff As String
Dim strTest As String
Dim strTest1 As String
Dim strTest2 As String
Dim strTest3 As String
Dim strTest4 As String
Dim strTest5 As String
Dim abc As Long
Dim Text As String

ff = FreeFile

If lboReporterType.Text = "" Then
MsgBox "'Reporter Type' cannot be blank. Please enter a value for Sample Cite.", vbOKOnly, appName
End
End If

Open "C:\xxxxx\statrep.db" For Input As #ff

While Not EOF(ff)
Line Input #ff, Text
strBuff = strBuff & Text & " "
Wend

Close #ff
strTest = lboReporterType.Text

abc = InStr(1, strBuff, strTest, vbTextCompare)

If abc <> 0 Then
MsgBox "Code already exists in file.", vbOKOnly, appName
lboReporterType.SetFocus
Exit Sub
Else

Open "C:\xxxxx\statrep.db" For Append As #ff
Print #ff, strTest
Close #ff

MsgBox "The Dictionary file has been appended.", vbOKOnly, appName
End

End If

the question here is:
(1) how would i split the code above to display the text in 4 different columns in the listbox.

(2) how would i update the excel spreadsheet with the four different listbox columns?

thanksss

xld
02-19-2009, 01:20 PM
With lboReporterType
.ColumnCount = 4
.AddItem xlSheet.Cells(Row, col - 1)
.List(.ListCount - 1, 1) = xlSheet.Cells(Row, col)
.List(.ListCount - 1, 2) = xlSheet.Cells(Row, col + 1)
.List(.ListCount - 1, 3) = xlSheet.Cells(Row, col + 4)
End With

lazyuser
02-19-2009, 03:43 PM
With lboReporterType
.ColumnCount = 4
.AddItem xlSheet.Cells(Row, col - 1)
.List(.ListCount - 1, 1) = xlSheet.Cells(Row, col)
.List(.ListCount - 1, 2) = xlSheet.Cells(Row, col + 1)
.List(.ListCount - 1, 3) = xlSheet.Cells(Row, col + 4)
End With

great! thankss for all your help, xld!

mikerickson
02-19-2009, 11:37 PM
If you're loading the list box from a range, something like this?
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 6
.List = Range("A1:F5").Value
.ColumnWidths = ";;;0;0;"
End With
End Sub

lazyuser
02-24-2009, 01:59 PM
If you're loading the list box from a range, something like this?
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 6
.List = Range("A1:F5").Value
.ColumnWidths = ";;;0;0;"
End With
End Sub

is there a way to add a horizontal scroll bar to this listbox?

Kenneth Hobs
02-24-2009, 02:45 PM
It is added if the text is too long.