VBA Listbox question

[Birger021]

Hi!

New to this forum and to VBA so I'm really hoping we have some experts present

Let's say I have a list of users and each user has a value for mon-fri.

What I want is a listbox with multiselect so I can select multiple users and then the userform should export those usernames J3 and the total values of those users for mon-fri in K3.


Regards
Birger

 

[MickG]

Based on your data being in sheet1, Names in Column "A" days of week value in columns "B :F"
Headers in row 1
Userform contains Listbox1 and CommandButton1.

Then try this in you userform for selected results start in "J3/K3".

[COLOR="Navy"]Sub[/COLOR] MG09May14
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
 Ray = Sheets("Sheet1").Range("A1").CurrentRegion
[COLOR="Navy"]With[/COLOR] Me.ListBox1
    .ColumnCount = 6
    .ColumnWidths = "30,30,30,30,30,30"
    .MultiSelect = fmMultiSelectMulti
    .List = Ray
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oSum [COLOR="Navy"]As[/COLOR] Double, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Variant
c = 3
[COLOR="Navy"]With[/COLOR] Me.ListBox1
[COLOR="Navy"]For[/COLOR] n = 1 To .ListCount - 1
    [COLOR="Navy"]If[/COLOR] .Selected(n) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c, "J") = .List(n)
        [COLOR="Navy"]With[/COLOR] Application
            Cells(c, "K") = .Sum(.Index(.Columns, n + 1, Array(2, 3, 4, 5, 6)))
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick