Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > add listbox item for all sheet same range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default add listbox item for all sheet same range
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.ListCou nt - 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("a1:D15"),
is that possible?
thanks for your help
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default add listbox item for all sheet same range

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
" wrote:

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.ListCou nt - 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("a1:D15"),
is that possible?
thanks for your help

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default add listbox item for all sheet same range
On Dec 19, 7:33 pm, Joel wrote:
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



" wrote:
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.ListCou nt - 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("a1:D15"),
is that possible?
thanks for your help- Hide quoted text -

- 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default add listbox item for all sheet same range
On Dec 19, 9:32 pm, " wrote:
On Dec 19, 7:33 pm, Joel wrote:





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

" wrote:
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.ListCou nt - 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("a1:D15"),
is that possible?
thanks for your help- Hide quoted text -

- 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- 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?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default add listbox item for all sheet same range
the line continuation character is missing

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


" wrote:

On Dec 19, 9:32 pm, " wrote:
On Dec 19, 7:33 pm, Joel wrote:





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

" wrote:
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.ListCou nt - 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("a1:D15"),
is that possible?
thanks for your help- Hide quoted text -

- 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- 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?

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default add listbox item for all sheet same range
On Dec 19, 10:40 pm, Joel wrote:
the line continuation character is missing

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

" wrote:
On Dec 19, 9:32 pm, " wrote:
On Dec 19, 7:33 pm, Joel wrote:

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

" wrote:
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.ListCou nt - 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("a1:D15"),
is that possible?
thanks for your help- Hide quoted text -

- 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- 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?

Thanks a lot Joel,
happy new year and merry Christmas by the way:)
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove Item from Listbox by Item Name Randy[_2_] Excel Programming 3 September 19th 07 02:36 PM
Double click item in Listbox to select item and close Listbox GusEvans Excel Programming 3 July 19th 07 12:36 PM
ListBox RowSource set to range of in-active sheet - possible? What-A-Tool Excel Programming 1 December 3rd 06 12:09 AM
How to copy selected listbox item to sheet michdan Excel Programming 1 July 14th 05 12:49 PM
Filling a listbox with transpose of a range from an excel sheet viswanthank Excel Programming 2 June 9th 05 03:37 PM


All times are GMT +1. The time now is 02:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement