Hi
In your UserForm_Initialise() sub insert

Me.ListBox1.Rowsource = Sheet1!A1:A5

to populate Listbox1 with data in range A1 to A5 on sheet1.

regards
Paul

Hazel wrote:

Hi All

I'm using a userform populated from the Row Source on the first sheet - I
have used a snippet of code off this forum to change the sheet in the
workbook thats OK how do I then populate the list box from the new sheet. Its
only a simple form so have included all the code being used below.

Option Explicit


Private Sub UserForm_Initialise()



Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value



End Sub
Private Sub Add1_Click()
If Me.Lb1.ListIndex -1 Then
Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value
End If
End Sub

Private Sub Lb1_Click()
Tb1.Value = ""
End Sub

Private Sub UserForm_Activate()
Com1.Value = "Select Members Sheet"
Com1.AddItem "JBloggs"
Com1.AddItem "ASmith"

End Sub

Private Sub Com1_Click()
If Com1.ListIndex < -1 Then
Worksheets(Com1.Value).Select
End If
End Sub


--
Many thanks

hazel

Hi
I'm confused. How many listboxes are being changed? Are you selecting
one listbox (sheetname) and then want the list items in another listbox
to be based on that sheet?
You talk about "the listbox" but you seem to want to select sheetnames
AND data on a sheet???
Can you just clarify what you want to see in all listboxes and what you
do to make list data change.
regards
Paul

Hazel wrote:

Hi Paul

Have done as you suggested however it still changes the sheet from JBloggs
to ASmith but the List box data does not change it remains showing JBloggs
instead of ASmith data any other clues has how to change the list box data
when changing the Com1 selection.
--
Many thanks

hazel


" wrote:

Hi
In your UserForm_Initialise() sub insert

Me.ListBox1.Rowsource = Sheet1!A1:A5

to populate Listbox1 with data in range A1 to A5 on sheet1.

regards
Paul

Hazel wrote:

Hi All

I'm using a userform populated from the Row Source on the first sheet - I
have used a snippet of code off this forum to change the sheet in the
workbook thats OK how do I then populate the list box from the new sheet. Its
only a simple form so have included all the code being used below.

Option Explicit


Private Sub UserForm_Initialise()



Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value



End Sub
Private Sub Add1_Click()
If Me.Lb1.ListIndex -1 Then
Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value
End If
End Sub

Private Sub Lb1_Click()
Tb1.Value = ""
End Sub

Private Sub UserForm_Activate()
Com1.Value = "Select Members Sheet"
Com1.AddItem "JBloggs"
Com1.AddItem "ASmith"

End Sub

Private Sub Com1_Click()
If Com1.ListIndex < -1 Then
Worksheets(Com1.Value).Select
End If
End Sub


--
Many thanks

hazel

Hi
certainly helps. You need to put some code in the Click event of the
comboBox
e.g. If your data for the listbox is in A1:A3 on all sheets and
ComboBox1 has your sheet names in it then this will switch to the data
for that sheet in Listbox1.

Private Sub ComboBox1_Click()
UserForm1.ListBox1.RowSource = ComboBox1.Value & "!" & "A1:A3"
End Sub

Double Click on the combobox in the VBE to see this eventin the list.
If your listbox data range changes on each sheet you will need a select
case statement on the sheet name.
e.g
mySheetName = ComboBox1.Value
With Userform1.ListBox1
Select Case mySheetName
Case "SalesSheet"
.RowSource = mySheetName & "!" & "A1:A3"
Case "InvoiceSheet"
.RowSource = mySheetName & "!" & "A1:A6"
End Select
End With

regards
Paul


Hazel wrote:

Hi Paul

The userform has just one list box Lb1 the workbook has 10 sheets each tab
has the members name, each sheet has 3 columns of info in columns A B & C the
row source for sheet1 is A3:C99. when I click on the combobox Com1 it changes
to Sheet2 but Lb1 retains the rowsource info of Sheet1. I was hoping Lb1
would automatically change to the info on sheet2 and so on. Does this help??
--
Many thanks

hazel


" wrote:

Hi
I'm confused. How many listboxes are being changed? Are you selecting
one listbox (sheetname) and then want the list items in another listbox
to be based on that sheet?
You talk about "the listbox" but you seem to want to select sheetnames
AND data on a sheet???
Can you just clarify what you want to see in all listboxes and what you
do to make list data change.
regards
Paul

Hazel wrote:

Hi Paul

Have done as you suggested however it still changes the sheet from JBloggs
to ASmith but the List box data does not change it remains showing JBloggs
instead of ASmith data any other clues has how to change the list box data
when changing the Com1 selection.
--
Many thanks

hazel


" wrote:

Hi
In your UserForm_Initialise() sub insert

Me.ListBox1.Rowsource = Sheet1!A1:A5

to populate Listbox1 with data in range A1 to A5 on sheet1.

regards
Paul

Hazel wrote:

Hi All

I'm using a userform populated from the Row Source on the first sheet - I
have used a snippet of code off this forum to change the sheet in the
workbook thats OK how do I then populate the list box from the new sheet. Its
only a simple form so have included all the code being used below.

Option Explicit


Private Sub UserForm_Initialise()



Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value



End Sub
Private Sub Add1_Click()
If Me.Lb1.ListIndex -1 Then
Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value
End If
End Sub

Private Sub Lb1_Click()
Tb1.Value = ""
End Sub

Private Sub UserForm_Activate()
Com1.Value = "Select Members Sheet"
Com1.AddItem "JBloggs"
Com1.AddItem "ASmith"

End Sub

Private Sub Com1_Click()
If Com1.ListIndex < -1 Then
Worksheets(Com1.Value).Select
End If
End Sub


--
Many thanks

hazel

Hi Paul

One line of code solves it all - absolutely brilliant you have made my day,
as the row source on each sheet is exactly the same no problem. To really
finish the job off how would I code the following - on a selection of a name
in the list box in the 3rd column of the listbox is a number it would really
help if that number would display in a textbox (Tb2) on the Userform. Once
again many thanks for all your help.
--
Many thanks

hazel


" wrote:

Hi
certainly helps. You need to put some code in the Click event of the
comboBox
e.g. If your data for the listbox is in A1:A3 on all sheets and
ComboBox1 has your sheet names in it then this will switch to the data
for that sheet in Listbox1.

Private Sub ComboBox1_Click()
UserForm1.ListBox1.RowSource = ComboBox1.Value & "!" & "A1:A3"
End Sub

Double Click on the combobox in the VBE to see this eventin the list.
If your listbox data range changes on each sheet you will need a select
case statement on the sheet name.
e.g
mySheetName = ComboBox1.Value
With Userform1.ListBox1
Select Case mySheetName
Case "SalesSheet"
.RowSource = mySheetName & "!" & "A1:A3"
Case "InvoiceSheet"
.RowSource = mySheetName & "!" & "A1:A6"
End Select
End With

regards
Paul


Hazel wrote:

Hi Paul

The userform has just one list box Lb1 the workbook has 10 sheets each tab
has the members name, each sheet has 3 columns of info in columns A B & C the
row source for sheet1 is A3:C99. when I click on the combobox Com1 it changes
to Sheet2 but Lb1 retains the rowsource info of Sheet1. I was hoping Lb1
would automatically change to the info on sheet2 and so on. Does this help??
--
Many thanks

hazel


" wrote:

Hi
I'm confused. How many listboxes are being changed? Are you selecting
one listbox (sheetname) and then want the list items in another listbox
to be based on that sheet?
You talk about "the listbox" but you seem to want to select sheetnames
AND data on a sheet???
Can you just clarify what you want to see in all listboxes and what you
do to make list data change.
regards
Paul

Hazel wrote:

Hi Paul

Have done as you suggested however it still changes the sheet from JBloggs
to ASmith but the List box data does not change it remains showing JBloggs
instead of ASmith data any other clues has how to change the list box data
when changing the Com1 selection.
--
Many thanks

hazel


" wrote:

Hi
In your UserForm_Initialise() sub insert

Me.ListBox1.Rowsource = Sheet1!A1:A5

to populate Listbox1 with data in range A1 to A5 on sheet1.

regards
Paul

Hazel wrote:

Hi All

I'm using a userform populated from the Row Source on the first sheet - I
have used a snippet of code off this forum to change the sheet in the
workbook thats OK how do I then populate the list box from the new sheet. Its
only a simple form so have included all the code being used below.

Option Explicit


Private Sub UserForm_Initialise()



Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value



End Sub
Private Sub Add1_Click()
If Me.Lb1.ListIndex -1 Then
Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value
End If
End Sub

Private Sub Lb1_Click()
Tb1.Value = ""
End Sub

Private Sub UserForm_Activate()
Com1.Value = "Select Members Sheet"
Com1.AddItem "JBloggs"
Com1.AddItem "ASmith"

End Sub

Private Sub Com1_Click()
If Com1.ListIndex < -1 Then
Worksheets(Com1.Value).Select
End If
End Sub


--
Many thanks

hazel

Hi
In the click event of the listbox you need
With Listbox1
for i = 0 to .ListCount - 1
If .Selected(i) then Tb2.Value = .List(i,2)
Exit for
next i
End with

Columns go from 0 in a listbox, so the third column is column 2. If
your third column is the BoundColumn (so the return value of the
listbox) then you can use
Tb2.Value = ListBox1.Value

instead of the selected bit.

regards
Paul

Hazel wrote:

Hi Paul

One line of code solves it all - absolutely brilliant you have made my day,
as the row source on each sheet is exactly the same no problem. To really
finish the job off how would I code the following - on a selection of a name
in the list box in the 3rd column of the listbox is a number it would really
help if that number would display in a textbox (Tb2) on the Userform. Once
again many thanks for all your help.
--
Many thanks

hazel


" wrote:

Hi
certainly helps. You need to put some code in the Click event of the
comboBox
e.g. If your data for the listbox is in A1:A3 on all sheets and
ComboBox1 has your sheet names in it then this will switch to the data
for that sheet in Listbox1.

Private Sub ComboBox1_Click()
UserForm1.ListBox1.RowSource = ComboBox1.Value & "!" & "A1:A3"
End Sub

Double Click on the combobox in the VBE to see this eventin the list.
If your listbox data range changes on each sheet you will need a select
case statement on the sheet name.
e.g
mySheetName = ComboBox1.Value
With Userform1.ListBox1
Select Case mySheetName
Case "SalesSheet"
.RowSource = mySheetName & "!" & "A1:A3"
Case "InvoiceSheet"
.RowSource = mySheetName & "!" & "A1:A6"
End Select
End With

regards
Paul


Hazel wrote:

Hi Paul

The userform has just one list box Lb1 the workbook has 10 sheets each tab
has the members name, each sheet has 3 columns of info in columns A B & C the
row source for sheet1 is A3:C99. when I click on the combobox Com1 it changes
to Sheet2 but Lb1 retains the rowsource info of Sheet1. I was hoping Lb1
would automatically change to the info on sheet2 and so on. Does this help??
--
Many thanks

hazel


" wrote:

Hi
I'm confused. How many listboxes are being changed? Are you selecting
one listbox (sheetname) and then want the list items in another listbox
to be based on that sheet?
You talk about "the listbox" but you seem to want to select sheetnames
AND data on a sheet???
Can you just clarify what you want to see in all listboxes and what you
do to make list data change.
regards
Paul

Hazel wrote:

Hi Paul

Have done as you suggested however it still changes the sheet from JBloggs
to ASmith but the List box data does not change it remains showing JBloggs
instead of ASmith data any other clues has how to change the list box data
when changing the Com1 selection.
--
Many thanks

hazel


" wrote:

Hi
In your UserForm_Initialise() sub insert

Me.ListBox1.Rowsource = Sheet1!A1:A5

to populate Listbox1 with data in range A1 to A5 on sheet1.

regards
Paul

Hazel wrote:

Hi All

I'm using a userform populated from the Row Source on the first sheet - I
have used a snippet of code off this forum to change the sheet in the
workbook thats OK how do I then populate the list box from the new sheet. Its
only a simple form so have included all the code being used below.

Option Explicit


Private Sub UserForm_Initialise()



Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value



End Sub
Private Sub Add1_Click()
If Me.Lb1.ListIndex -1 Then
Range(Me.Lb1.RowSource).Resize(1, 1).Offset(Me.Lb1.ListIndex, 3) =
Me.Tb1.Value
End If
End Sub

Private Sub Lb1_Click()
Tb1.Value = ""
End Sub

Private Sub UserForm_Activate()
Com1.Value = "Select Members Sheet"
Com1.AddItem "JBloggs"
Com1.AddItem "ASmith"

End Sub

Private Sub Com1_Click()
If Com1.ListIndex < -1 Then
Worksheets(Com1.Value).Select
End If
End Sub


--
Many thanks

hazel