list box selection?
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
|