MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12Next
Return Post new threads
View: 471|Reply: 15

Listbox update multiple items in Listbox

[Copy link]

20

Topics

89

Posts

236

Integral

Conqueror

Rank: 3Rank: 3

Integral
236
Post on 2-10-2017 03:16:09 | All posts |Read mode
My Listbox selects all the items I have an update button to update. Currently Listbox.Value update only 1 value. I tried Listbox.List so when I click update same thing only one value from the listbox gets updated. Can someone tell me what in the code must be changed to update the entire Listbox values onto my spreedsheet...My Listbox is called LbNumbers.Thank you Rep given for a solution
Numbers for Listbox gets activated here
Private Sub UserForm_Initialize()
UserForm1.LbNumbers.RowSource = Sheet2!A1:A3
ComboBox1.List = Array(D, H, S, SH, C, RP, P, P, M)
End Sub
Private Sub update_Click()
Dim no, rowNO, i, r As Integer
Dim dat As Date

If Label8.Caption =  Then
    MsgBox Please choose value!
    Exit Sub
End If
If ComboBox1.Value =  Then
    MsgBox Please choose option!
End If
r = 0
no = CInt(Label8.Caption)
rowNO = Application.WorksheetFunction.Match(no, Sheets(Database).Range(A:A), 0)
  
  For i = 8 To 377
     If Worksheets(Database).Cells(3, i) = DTPicker1.Value And Worksheets(Database).Cells(3, i) = DTPicker2.Value Then
        Worksheets(Database).Cells(rowNO, i) =
[color=]LbNumbers.List(LbNumbers.ListIndex)
        r = 1
     End If
  Next i
If r = 0 Then MsgBox No updates made. Check data!
End Sub
Reply

Props Report

1

Topics

799

Posts

1715

Integral

King

Rank: 6Rank: 6

Integral
1715
Post on 2-10-2017 10:52:31 | All posts



Hi,
Could you please upload sample file with some dummy data?
Reply Support Opposition

Props Report

1

Topics

799

Posts

1715

Integral

King

Rank: 6Rank: 6

Integral
1715
Post on 2-11-2017 04:53:46 | All posts



Hi,
Could you please upload sample file with some dummy data?




Reply Support Opposition

Props Report

20

Topics

89

Posts

236

Integral

Conqueror

Rank: 3Rank: 3

Integral
236
 Author| Post on 2-11-2017 05:53:55 | All posts





Kasan thank you for your reply sure.......this is a solution you did for someone. So credit for this code is to you thanks for that.I want to update a listbox where you had a combobox thats the only change.








  • BacktestingSystem.xlsm
    (328.8 KB, 0 views)
    Download

  • Reply Support Opposition

    Props Report

    20

    Topics

    89

    Posts

    236

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    236
     Author| Post on 2-11-2017 09:21:01 | All posts





    Kasan thank you for your reply sure.......this is a solution you did for someone. So credit for this code is to you thanks for that.I want to update a listbox where you had a combobox thats the only change.








  • BacktestingSystem.xlsm
    (328.8 KB, 6 views)
    Download

  • Reply Support Opposition

    Props Report

    20

    Topics

    89

    Posts

    236

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    236
     Author| Post on 2-11-2017 12:53:01 | All posts





    Kasan thank you for your reply sure.......this is a solution you did for someone. So credit for this code is to you thanks for that.I want to update a listbox where you had a combobox thats the only change.








  • BacktestingSystem.xlsm
    (328.8 KB, 11 views)
    Download














  • Reply Support Opposition

    Props Report

    3

    Topics

    110

    Posts

    255

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    255
    Post on 2-11-2017 14:00:44 | All posts



    ricklou,
    It is not very clear how you want the selected values entered on the sheet.  I will interpret you to mean that you want them appended in the cell (ex. 1|2|3).  The example uses a pipe as the separator.  To do this, please change add the code lines in blue
    Private Sub update_Click()
    Dim no, rowNO, i, r As Integer,
    [color=]stng As String
    Dim dat As Date
    If Label8.Caption =  Then
        MsgBox Please choose value!
        Exit Sub
    End If
    If ComboBox1.Value =  Then
        MsgBox Please choose option!
    End If
    r = 0
    no = CInt(Label8.Caption)
    rowNO = Application.WorksheetFunction.Match(no, Sheets(Database).Range(A:A), 0)
      For i = 8 To 377
         If Worksheets(Database).Cells(3, i) = DTPicker1.Value And Worksheets(Database).Cells(3, i) = DTPicker2.Value Then
            
    'Worksheets(Database).Cells(rowNO, i) = LbNumbers.List(LbNumbers.ListIndex)
            
    [color=]For j = 0 To LbNumbers.ListCount - 1
                If LbNumbers.Selected(j) Then
                    If stng =  Then
                        stng = LbNumbers.List(j)
                    Else
                        stng = stng  |  LbNumbers.List(j)
                    End If
                End If
            Next j
            Worksheets(Database).Cells(rowNO, i) = stng
            stng =
            r = 1
         End If
      Next I
    If r = 0 Then MsgBox No updates made. Check data!
    End SubHTH,
    Maud
    Reply Support Opposition

    Props Report

    20

    Topics

    89

    Posts

    236

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    236
     Author| Post on 2-11-2017 15:44:01 | All posts





    Kasan thank you for your reply sure.......this is a solution you did for someone. So credit for this code is to you thanks for that.I want to update a listbox where you had a combobox thats the only change.








  • BacktestingSystem.xlsm
    (328.8 KB, 14 views)
    Download














  • Reply Support Opposition

    Props Report

    20

    Topics

    89

    Posts

    236

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    236
     Author| Post on 2-11-2017 16:17:17 | All posts





    Kasan thank you for your reply sure.......this is a solution you did for someone. So credit for this code is to you thanks for that.I want to update a listbox where you had a combobox thats the only change.








  • BacktestingSystem.xlsm
    (328.8 KB, 16 views)
    Download














  • Reply Support Opposition

    Props Report

    20

    Topics

    89

    Posts

    236

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    236
     Author| Post on 2-11-2017 18:48:14 | All posts





    Kasan thank you for your reply sure.......this is a solution you did for someone. So credit for this code is to you thanks for that.I want to update a listbox where you had a combobox thats the only change.








  • BacktestingSystem.xlsm
    (328.8 KB, 18 views)
    Download














  • Reply Support Opposition

    Props Report

    12Next
    Return Post new threads

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 9-21-2017 14:36 , Processed in 0.128461 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!