|
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 |
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 |
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 |
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? |
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? |
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:) |
| All times are GMT +1. The time now is 01:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com