List Box

  • Thread starter Thread starter RK
  • Start date Start date
R

RK

I hope you can help.

I created a form in excel. simple enough.
I placed a listbox on the form.
How do I bind it to sheet1 and how do I reference the
control source.

another words, how can I display sheet 1 rowsource ac1
on form1, listbox1?

I can't find anything or very little on listbox controls.

thank you for your time
RK
 
I tried this, but only get errors.


Private Sub UserForm1_Initialize()

ListBox1.ColumnCount = 1
ListBox1.RowSource = "sheet1:ac1"

' ListBox1.BoundColumn = 0
End Sub



RK
 
I'd use:

Option Explicit
Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 1
ListBox1.RowSource _
= Worksheets("sheet1").Range("ac1:ac10").Address(external:=True)

' ListBox1.BoundColumn = 0
End Sub

Note that the name of this procedure is UserForm_Initialize--not
Userform1_initialize.

(and I wasn't sure what AC1 meant.)
 
Well, I tried this, and I can't seem to get anything to
display in my textbox.

'*************
'Changed 11/23/2004
'*************

Private Sub CommandButton2_Click()

Dim i As Long
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rng As Excel.Range

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
Set wb = Application.Workbooks.Open(ListBox1.List(i,
0))
Set ws = wb.Worksheets(Left$(ListBox1.List(i, 1),
InStr(ListBox1.List(i, 1), "!") - 1))
Set rng = ws.Range(Mid$(ListBox1.List(i, 1), InStr
(ListBox1.List(i, 1), "!") + 1))
With rng
.Value = 99
.PrintOut
End With
End If
Next i

End Sub


Private Sub frmUserForm1_Initialize()

Dim cell As Range

Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 1
Me.ListBox1.ColumnWidths = "2.5 in;2.5 in"

For Each cell In Sheet1.Range("AC2:AC69").Cells
Me.ListBox1.AddItem cell.Hyperlinks(1).SubAddress
Me.ListBox1.AddItem cell.Hyperlinks(1).Address
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) =
cell.Hyperlinks.TextToDisplay
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) =
cell.Hyperlinks(1).SubAddress
Next cell

'Added to test
'Add these lines here
'Debug.Print cell.Address, cell.Hyperlinks.Count,
cell.Hyperlinks(1).Address
'Debug.Print cell.Hyperlinks(1).SubAddress,
cell.Hyperlinks(1).TextToDisplay
'Debug.Print "----------------------------------------
-"
' End of added lines to test

End Sub

'***********
'End Change
'***********

Any ideas? Any comments would be appreciated.

RK
 
This line:

Private Sub frmUserForm1_Initialize()

should be:

Private Sub UserForm_Initialize()

It's the procedure name for initializing the userform that owns the code. Don't
change it.
 
Well that at least show something in the list box.
1st column is blank, second column show my second column.

When I selected one blank field, of course, I get the
error on the line below

Set wb = Application.Workbooks.Open(ListBox1.List(i, 0))

Run time error 1004

"could not be found. Check spelling of the file name, and
verify that the file location is correct.

thank for your help.

Any suggestions as to why the first column is blank?

RK

ps I have been fussing with this for a long time and this
is closer than I have been able to get.

Thank again for your help
 
This looks kind of funny:

For Each cell In Sheet1.Range("d2:d18").Cells
Me.ListBox1.AddItem ...
Me.ListBox1.AddItem ...
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = ...
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = ...
Next cell


You're overwriting the first entry with the second
"Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = ..."

But without knowing what's in those links, I'd toss a couple of msgboxes or
debug.prints to see if you're really getting what you expect

debug.print listbox1.list(i,0)
Set wb = Application.Workbooks.Open(ListBox1.List(i, 0))
 
Back
Top