| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
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("a1  15"),is that possible? thanks for your help |
|
|
|
|||
|
|
| |
|
Guest
Posts: n/a
|
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 "(E-Mail Removed)" 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.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("a1 15"),> is that possible? > thanks for your help > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Dec 19, 7:33 pm, Joel <J...@discussions.microsoft.com> 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 > > > > "bah...@gmail.com" 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.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("a1 15"),> > 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Dec 19, 9:32 pm, "bah...@gmail.com" <bah...@gmail.com> wrote:
> On Dec 19, 7:33 pm, Joel <J...@discussions.microsoft.com> 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 > > > "bah...@gmail.com" 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.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("a1 15"),> > > 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? |
|
|
|
|
|||
|
Guest
Posts: n/a
|
the line continuation character is missing
from Userform1.ListBox1.AddItem cell.Offset(0, 1).Value to Userform1.ListBox1.AddItem _ cell.Offset(0, 1).Value "(E-Mail Removed)" wrote: > On Dec 19, 9:32 pm, "bah...@gmail.com" <bah...@gmail.com> wrote: > > On Dec 19, 7:33 pm, Joel <J...@discussions.microsoft.com> 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 > > > > > "bah...@gmail.com" 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.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("a1 15"),> > > > 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? > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Dec 19, 10:40 pm, Joel <J...@discussions.microsoft.com> 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 > > "bah...@gmail.com" wrote: > > On Dec 19, 9:32 pm, "bah...@gmail.com" <bah...@gmail.com> wrote: > > > On Dec 19, 7:33 pm, Joel <J...@discussions.microsoft.com> 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 > > > > > "bah...@gmail.com" 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.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("a1 15"),> > > > > 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  |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Double click item in Listbox to select item and close Listbox | =?Utf-8?B?R3VzRXZhbnM=?= | Microsoft Excel Programming | 3 | 19th Jul 2007 12:36 PM |
| ListBox RowSource set to range of in-active sheet - possible? | What-A-Tool | Microsoft Excel Programming | 1 | 3rd Dec 2006 12:09 AM |
| How to copy selected listbox item to sheet | michdan | Microsoft Excel Programming | 1 | 14th Jul 2005 12:49 PM |
| Re: Filling a listbox with transpose of a range from an excel sheet | Tom Ogilvy | Microsoft Excel Programming | 1 | 9th Jun 2005 03:37 PM |
| Filling a listbox with transpose of a range from an excel sheet | viswanthank | Microsoft Excel Programming | 0 | 9th Jun 2005 03:11 PM |
