Get row number of each selected Listbox item

[sharky12345]

I have a multi-select Listbox on a Userform and I need to get the row number of each selected item if someone can help please.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Mar14
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rws [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] ListBox1
[COLOR="Navy"]For[/COLOR] n = 0 To .ListCount - 1
    [COLOR="Navy"]If[/COLOR] .Selected(n) [COLOR="Navy"]Then[/COLOR]
        Rws = Rws & IIf(Rws = "", n + 1, ", " & n + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]If[/COLOR] Rws <> "" [COLOR="Navy"]Then[/COLOR] MsgBox "Selected Rows :- " & Rws
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sharky12345]

Mick, you're a legend - this has been driving me nuts for days...

Thanks!

 

[MickG]

You're welcome

 

[sharky12345]

Mick - can you help me please with a further step?

I need to update column D on sheet1 for each selected row with the value in TextDate.

 

[MickG]

Assuming you have more than one column in your listbox, what list box column do you what to use to update column "D" of the sheet., Or maybe you only have one column in your Listbox !!!!
Also
Do I assume that the listbox row numbers is the same row numbers as on the sheet.??

 

[sharky12345]

Thanks Mick....

The Listbox row numbers are + 2 with regard to the sheet and what I need to do is update each row in Column D with the value from a Textbox named TextDate - there are 15 columns in the Listbox.

It's never that straightforward with me I'm afraid!

 

[MickG]

So can I assume that column 4 in the List box is equivalent to column "D" on the sheet.
Also, How do the values in the listbox get to the Textbox ???
Also, why can't you just update the sheet from the selected items in the listbox ???

 

[sharky12345]

Yes - column 4 is equivalent to D....
TextDate just has the current date in it - so what I'm trying to do is enter the current date on the sheet for the items selected in the Listbox....
I have no idea how to update the sheet direct from the Listbox!

Thanks Mick - appreciated.

 

[MickG]

Try this code in you Userform Listbox.
The code just uses the Row reference from the Listbox selected items to paste the textbox Date into the appropriate cell in column "D" sheet1, using CommandButton 1.

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rws [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] ListBox1
    [COLOR="Navy"]If[/COLOR] TextBox1 <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 0 To .ListCount - 1
            [COLOR="Navy"]If[/COLOR] .Selected(n) [COLOR="Navy"]Then[/COLOR]
                '[COLOR="Green"][B]Nb:- Change "+ 3" as appropriate !![/B][/COLOR]
                Sheets("Sheet1").Cells(n + 3, "D").Value = TextBox1.Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick