how to updates multiple rows in muliselect listbox

[jaym6939]

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?

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]

Apparently something changed. I don't have that problem.
You could put the code you are using here.

 

[jaym6939]

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:

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]

Yes.

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?

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]

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.

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.

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]

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

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[jaym6939]

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.

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]

Do you need this?

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

 

[jaym6939]

yes. thanks you. i got to work adding text Value Deleted. struggling to add 2nd sentence to strikthrough overall selected row.

 

[DanteAmor]

So it already works for you?