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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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: 20
  • output.JPG
    output.JPG
    138.5 KB · Views: 20
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top