add listbox item for all sheet same range

[baha17]

Hi all,
Let`s say I have too many worksheet in my workbook.All worsheet
generated by using another userform.In my code i can add item to
listbox.How can I do below code to work for all sheet
Private Sub CommandButton1_Click()
Dim cell As Range
Dim xcell As Range
Dim trd variant
trd = TextBox1.Value
Set xcell = Range("a1:d15")
For Each cell In xcell.Columns(1).Cells
If cell.Value = trd Then
ListBox1.ColumnCount = 2
UserForm1.ListBox1.AddItem cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1,
2) = cell.Offset(0, 2)
End If
Next
End Sub
I mean I want to add item to list box for each sheet range("a115"),
is that possible?
thanks for your help

 

[Joel]

Private Sub CommandButton1_Click()
Dim cell As Range
Dim xcell As Range
Dim trd As Variant
trd = TextBox1.Value
For Each Sht In ThisWorkbook.Sheets
Set xcell = Sht.Range("a1:d15")
For Each cell In xcell.Columns(1).Cells
If cell.Value = trd Then
ListBox1.ColumnCount = 2
UserForm1.ListBox1.AddItem
cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1. _
ListCount - 1, 2) = cell.Offset(0, 2)
End If
Next cell
Next Sht
End Sub

 

[baha17]

Private Sub CommandButton1_Click()
Dim cell As Range
Dim xcell As Range
Dim trd As Variant
trd = TextBox1.Value
For Each Sht In ThisWorkbook.Sheets
Set xcell = Sht.Range("a1:d15")
For Each cell In xcell.Columns(1).Cells
If cell.Value = trd Then
ListBox1.ColumnCount = 2
UserForm1.ListBox1.AddItem
cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1. _
ListCount - 1, 2) = cell.Offset(0, 2)
End If
Next cell
Next Sht
End Sub





- Show quoted text -

Thanks for your reply Joel.I haven`t try yet but looks like it will
work.Thanks for your help again
regards,
Baha

 

[baha17]

Thanks for your reply Joel.I haven`t try yet but looks like it will
work.Thanks for your help again
regards,
Baha- Hide quoted text -

- Show quoted text -

Hi Joel,
Why that code give error on "UserForm1.ListBox1.AddItem cell.Offset(0,
1).Value" line(invalid use of property) any idea?

 

[Joel]

the line continuation character is missing

from
Userform1.ListBox1.AddItem
cell.Offset(0, 1).Value
to
Userform1.ListBox1.AddItem _
cell.Offset(0, 1).Value

 

[baha17]

the line continuation character is missing

from
Userform1.ListBox1.AddItem
cell.Offset(0, 1).Value
to
Userform1.ListBox1.AddItem _
cell.Offset(0, 1).Value

Thanks a lot Joel,
happy new year and merry Christmas by the way