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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

DanteAmor

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

VBA Code:
Private Sub cmdaction_Click()
  Dim i As Long, t As String, t1 As String
  If cmbaction.Value = "RP" Then
    With lstDatabase
      For i = 0 To .ListCount - 1
        t = Chr(Asc(Mid(.List(i, 4), 2, 1)) + 1)
        t1 = Mid(.List(i, 4), 1, 1) & t & Mid(.List(i, 4), 3, 1)
        .List(i, 4) = t1
      Next
    End With
  End If
End Sub
 

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Thanks. with Minor modification it works. i have modified to below code and it works.
VBA Code:
Private Sub cmdaction_Click()
  Dim i As Long, t As String, t1 As String
  With UserForm3.lstDatabase

For i = 0 To UserForm3.lstDatabase.ListCount - 1
If UserForm3.lstDatabase.Selected(i) = True Then

If cmbaction.Value = "RP" Then
t = Chr(Asc(Mid(lstDatabase.List(i, 4), 2, 1)) + 1)
'Me.lstDatabase.Row (0), Column(4) = "ABA"
t1 = Mid(lstDatabase.List(i, 4), 1, 1) & t & Mid(lstDatabase.List(i, 4), 3, 1)
        .List(i, 4) = t1
     
    
  End If
 End If
 Next
 End With
 
  
End Sub

above update value only in userform. what i am trying to do is use below code to update spread sheet. however it gives me error, "column not found". can you pl help me to check where i am doing mistake to write a code.
VBA Code:
Private Sub cmdaction_Click()
Dim t, t1 As String
Dim vrech As Range
Dim 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)

Set vrech = sh.Range("E3:E250").Find(UserForm3.lstDatabase.Column(4, i), , xlValues, xlWhole)

With UserForm3.lstDatabase

For i = 0 To UserForm3.lstDatabase.ListCount - 1
If UserForm3.lstDatabase.Selected(i) = True Then


If cmbaction.Value = "RP" Then
t = Chr(Asc(Mid(lstDatabase.List(i, 4), 2, 1)) + 1)
'Me.lstDatabase.Row (0), Column(4) = "ABA"
t1 = Mid(lstDatabase.List(i, 4), 1, 1) & t & Mid(lstDatabase.List(i, 4), 3, 1)
If vrech Is Nothing Then
    MsgBox "Row not found"
ElseIf lColumn Is Nothing Then
    MsgBox "Column not found"
Else
    
Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1

thanks for your support.
stay safe.
 

jaym6939

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

ADVERTISEMENT

VBA Code:
please find below complete code,
Private Sub cmdaction_Click()
Dim t, t1 As String
Dim vrech As Range
Dim 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)

Set vrech = sh.Range("E3:E250").Find(UserForm3.lstDatabase.Column(4, i), , xlValues, xlWhole)

With UserForm3.lstDatabase

For i = 0 To UserForm3.lstDatabase.ListCount - 1
If UserForm3.lstDatabase.Selected(i) = True Then


If cmbaction.Value = "RP" Then
t = Chr(Asc(Mid(lstDatabase.List(i, 4), 2, 1)) + 1)
'Me.lstDatabase.Row (0), Column(4) = "ABA"
t1 = Mid(lstDatabase.List(i, 4), 1, 1) & t & Mid(lstDatabase.List(i, 4), 3, 1)
If vrech Is Nothing Then
    MsgBox "Row not found"
ElseIf lColumn Is Nothing Then
    MsgBox "Column not found"
Else
    
Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1

End If

End If
Next i
End With

End Sub

to perform below step system gives me error,

VBA Code:
Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1

@ this message box,
VBA Code:
ElseIf lColumn Is Nothing Then
    MsgBox "Column not found"

Do you think it is because i have kept below two code prior to userform start,
VBA Code:
Set lColumn = sh.Range("h1:AZa1").Find(UserForm3.txtchangenumber.Value, , xlValues, xlWhole)

Set vrech = sh.Range("E3:E250").Find(UserForm3.lstDatabase.Column(4, i), , xlValues, xlWhole)

With UserForm3.lstDatabase
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

jaym6939

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

ADVERTISEMENT

Thanks for your response.
what i am trying to do is, if user has selected parts from listbox and press action selected part command button. it should populate part bump spreadsheet for particular part reference to part change number in column range H1 to AZA1.
i am still getting error code column not found. please refer below images.
 

Attachments

  • Test dummy.JPG
    Test dummy.JPG
    175.9 KB · Views: 11
  • output.JPG
    output.JPG
    138.5 KB · Views: 12

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
I am getting error for below code,
Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1

it is giving error <object variable or with block variable not set>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Are you using all the code that I put in post #7?
 

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