how to updates multiple rows in muliselect listbox

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code lstDatabase.List(0, 4). i am looking to add loop count or list index to update multiple rows by command button.
can you pl help with to update below code?

VBA Code:
Private Sub cmdaction_Click()
Dim i As Long, n As Long, f As Range


Dim t, t1 As String
If cmbaction.Value = "RP" Then
t = Chr(Asc(Mid(lstDatabase.List(0, 4), 2, 1)) + 1)
'Me.lstDatabase.Row (0), Column(4) = "ABA"
t1 = Mid(lstDatabase.List(0, 4), 1, 1) & t & Mid(lstDatabase.List(0, 4), 3, 1)
Me.lstDatabase.List(0, 4) = t1

End If

End Sub

thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Apparently something changed. I don't have that problem.
You could put the code you are using here.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
please find file @ below link,

I don't really understand what you need, because you haven't explained it, but I guess you need the following:

VBA Code:
Private Sub cmdaction_Click()
  Dim t, t1 As String
  Dim vrech As Range, lColumn As Range
  Dim sh As Worksheet
  Dim i As Long
 
  Set sh = ThisWorkbook.Sheets("part bump")
  Set lColumn = sh.Range("H1:AZA1").Find(UserForm3.txtchangenumber.Value, , xlValues, xlWhole)
  If lColumn Is Nothing Then
    MsgBox "Column not found"
    Exit Sub
  End If
 
  With UserForm3.lstDatabase
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        If cmbaction.Value = "RP" Then
          Set vrech = sh.Range("E3:E250").Find(.Column(4, i), , xlValues, xlWhole)
          If Not vrech Is Nothing Then
            t = Chr(Asc(Mid(.List(i, 4), 2, 1)) + 1)
            'Me.lstDatabase.Row (0), Column(4) = "ABA"
            t1 = Mid(.List(i, 4), 1, 1) & t & Mid(.List(i, 4), 3, 1)
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1
          End If
        End If
      End If
    Next i
  End With
End Sub
all code gives me same error. please find file @ below link,
Test Dummy (1).xlsm

thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

Of course you changed the code. I understand, we are learning. But did you at least try with the code I put in post #6?

Rich (BB code):
Private Sub cmdaction_Click()
  Dim t, t1 As String
  Dim vrech As Range, lColumn As Range
  Dim sh As Worksheet
  Dim i As Long
   
  'If lColumn Is Nothing Then
   ' MsgBox "Column not found"
    'Exit Sub
  'End If
 
  With UserForm3.lstDatabase
    For i = 0 To .ListCount - 1
      If UserForm3.lstDatabase.Selected(i) = True Then
        If cmbaction.Value = "RP" Then
          Set sh = ThisWorkbook.Sheets("part bump")
          Set lColumn = sh.Range("H1:AZA1").Find(UserForm3.txtchangenumber.Value, , xlValues, xlWhole)
          Set vrech = sh.Range("E3:E250").Find(.Column(4, i), , xlValues, xlWhole)
         
          If Not vrech Is Nothing Then
            t = Chr(Asc(Mid(.List(i, 4), 2, 1)) + 1)
            'Me.lstDatabase.Row (0), Column(4) = "ABA"
            t1 = Mid(.List(i, 4), 1, 1) & t & Mid(.List(i, 4), 3, 1)
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1
          End If
        End If
      End If
    Next i
  End With
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I've already checked your data, and apparently the Find method has trouble finding the data when you have it in a "vertical" orientation in the cell.
I changed the orientation of the data and the code works.

1596759806453.png



I added the Val function to the code to change the text to numeric value.
Try my code, once it works for you, make all the changes you want.

VBA Code:
Private Sub cmdaction_Click()
  Dim t, t1 As String
  Dim vrech As Range, lColumn As Range
  Dim sh As Worksheet
  Dim i As Long
  
  Set sh = ThisWorkbook.Sheets("part bump")
  Set lColumn = sh.Range("H1:AZA1").Find(Val(txtchangenumber.Value), , xlValues, xlWhole)
  If lColumn Is Nothing Then
    MsgBox "Column not found"
    Exit Sub
  End If
  
  With UserForm3.lstDatabase
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        If cmbaction.Value = "RP" Then
          Set vrech = sh.Range("E3:E250").Find(.Column(4, i), , xlValues, xlWhole)
          If Not vrech Is Nothing Then
            t = Chr(Asc(Mid(.List(i, 4), 2, 1)) + 1)
            'Me.lstDatabase.Row (0), Column(4) = "ABA"
            t1 = Mid(.List(i, 4), 1, 1) & t & Mid(.List(i, 4), 3, 1)
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1
          End If
        End If
      End If
    Next i
  End With
End Sub
 

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks. much appreciated your support. this was bugging me from last 2 days.
thanks again.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just a more question if you don't mind directing, i have updated code provided by you to incorporate addition drop down items from Combo box. i am struggling with below action to write a code for DP action.. highlighted with bold action.
VBA Code:
Private Sub cmdaction_Click()
  Dim t, t1 As String
  Dim vrech As Range, lColumn As Range
  Dim sh As Worksheet
  Dim i As Long
  Dim selItem As String
  
  Set sh = ThisWorkbook.Sheets("part bump")
  Set lColumn = sh.Range("H1:AZA1").Find(Val(txtchangenumber.Value), , xlValues, xlWhole)
  If lColumn Is Nothing Then
    MsgBox "Column not found"
    Exit Sub
  End If
  
  With UserForm3.lstDatabase
    For i = 0 To .ListCount - 1
    
      If .Selected(i) = True Then
      selItem = UserForm3.lstDatabase.List(i, 4)
      Set vrech = sh.Range("E3:E250").Find(.Column(4, i), , xlValues, xlWhole)
        If cmbaction.Value = "RP" Then
          
          'If Not vrech Is Nothing Then
            t = Chr(Asc(Mid(.List(i, 4), 2, 1)) + 1)
            'Me.lstDatabase.Row (0), Column(4) = "ABA"
            t1 = Mid(.List(i, 4), 1, 1) & t & Mid(.List(i, 4), 3, 1)
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1
        ElseIf cmbaction.Value = "RV" Then
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = selItem
        ElseIf cmbaction.Value = "DP" Then

          
          '  [B][SIZE=5]Intersect(vrech.EntireRow, lColumn.EntireColumn) = Text value "Deleted" and strikthrough overall selected row.  [/SIZE][/B]
          End If
        End If
      
    Next i
  End With
End Sub
can you pl help?
thanks again.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Do you need this?

VBA Code:
Intersect(vrech.EntireRow, lColumn.EntireColumn) = "Text value Deleted and strikthrough overall selected row."
 

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
yes. thanks you. i got to work adding text Value Deleted. struggling to add 2nd sentence to strikthrough overall selected row.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
So it already works for you?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,937
Messages
5,525,731
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top