how to updates multiple rows in muliselect listbox

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
365
Platform
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DanteAmor

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

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
365
Platform
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,558
Office Version
2007
Platform
Windows
Are you using all the code that I put in post #7?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,966
Messages
5,508,442
Members
408,686
Latest member
celobacara

This Week's Hot Topics

Top