How do I get a list from an excel sheet into a userform/listbox?

  • Thread starter Thread starter =?iso-8859-1?q?=D6rjan_Kihlbaum?=
  • Start date Start date
?

=?iso-8859-1?q?=D6rjan_Kihlbaum?=

Hello all,

I am very new to the VBA programming language. I have managed to solve
most of my problems by looking for answers here, on the Internet and
in a nice little book called "Excel 2003 VBA".

Background:
I am planning to have a excel sheet that can display information based
on a list located in another sheet (a database of sorts). So, the
"database sheet" contains names,numbers and values and I plan to have
a "display sheet" that will display values and calculations for one
member of the database/list at a time.

VBA involvment:
I want to be able to press a button to open up a simple userform with
a listbox.
In the listbox I want to list the members of the database.
When I click on any of the members I want the userform to close and
that the values for that perticular member to be displayed in my
"display sheet".

Problem:
Well, the excel part is easy enough but I really don't know where to
start with the VBA.
I have created a userfrom and I have added a listbox.

Now how do I get the listbox to display the "members" from the
database sheet?

All the rest I'll worry about later.. :)


I haven't found any article yet to help me so maybe anyone here knows
where I should start to look for info?
Or can you give a hint so I know where to start?
 
Hello all,

I am very new to the VBA programming language. I have managed to solve
most of my problems by looking for answers here, on the Internet and
in a nice little book called "Excel 2003 VBA".

Background:
I am planning to have a excel sheet that can display information based
on a list located in another sheet (a database of sorts). So, the
"database sheet" contains names,numbers and values and I plan to have
a "display sheet" that will display values and calculations for one
member of the database/list at a time.

VBA involvment:
I want to be able to press a button to open up a simple userform with
a listbox.
In the listbox I want to list the members of the database.
When I click on any of the members I want the userform to close and
that the values for that perticular member to be displayed in my
"display sheet".

Problem:
Well, the excel part is easy enough but I really don't know where to
start with the VBA.
I have created a userfrom and I have added a listbox.

Now how do I get the listbox to display the "members" from the
database sheet?

All the rest I'll worry about later.. :)

I haven't found any article yet to help me so maybe anyone here knows
where I should start to look for info?
Or can you give a hint so I know where to start?
Click to expand...

Wouldnt it be easier to just add a find button, and a textbox to
input the name they are looking for ?
Private Sub CommandButton1_Click()
Dim rng As Range
Dim ws As Worksheet
Dim SearchTxt As String
Dim Row As Integer
Dim firstAddress As String
Set ws = Worksheets("sheet1")
SearchTxt = TextBox1.Text ' This is textbox they are
putting the name into


If TextBox1.Text= "" Then
GoTo ErrorMessage
End If
Set rng = ws.Cells.Find(What:=SearchTxt, After:=ws.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not r Is Nothing Then
firstAddress = r.Address
Do
Set rng = Cells.FindNext(rng)
Listbox.AddItem (rng)
Loop While Not rng Is Nothing And rng.Address <> firstAddress
Else
' What you want it to do if thats false
End if

ErrorMessage:
MsgBox "Please Type a Name", vbInformation, " No Names found"
End Sub
 
You can use RowSource, AddItem or List methods to load the ListBox or ComboBox.
Open the VBA editor (Alt + F11) and click help, then type one of the above
methods into the search box to display a menu of related articles.
 
Wouldnt it be easier to just add a find button, and a textbox to
input the name they are looking for ?
Private Sub CommandButton1_Click()
Dim rng As Range
Dim ws As Worksheet
Dim SearchTxt As String
Dim Row As Integer
Dim firstAddress As String
Set ws = Worksheets("sheet1")
SearchTxt = TextBox1.Text ' This is textbox they are
putting the name into

If TextBox1.Text= "" Then
GoTo ErrorMessage
End If
Set rng = ws.Cells.Find(What:=SearchTxt, After:=ws.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not r Is Nothing Then
firstAddress = r.Address
Do
Set rng = Cells.FindNext(rng)
Listbox.AddItem (rng)
Loop While Not rng Is Nothing And rng.Address <> firstAddress
Else
' What you want it to do if thats false
End if

ErrorMessage:
MsgBox "Please Type a Name", vbInformation, " No Names found"
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

Nope, sorry. It needs to be supersimple. Having to type in the names
wont do unfortunally.

Thanks though!
 
You can use RowSource, AddItem or List methods to load the ListBox or ComboBox.
Open the VBA editor (Alt + F11) and click help, then type one of the above
methods into the search box to display a menu of related articles.












- Show quoted text -
Click to expand...

Thank you!

I'll dive into this right away! :)
 
Back
Top