SELECT LINE AND COLUMN IN LISTVIEW DEPENDING ON THE VALUE OF TEXTBOX´S

P

paroduso

Board Regular
Joined
Sep 11, 2013
Messages
103
Office Version
  1. 2019
Platform
  1. Windows
Hi everyone,

I need help with this...i have a listview and two textboxes...I need that after filling out the textboxes, in the list the respective field appears selected
like the "indice + corresp" function... example: search for textbox1 field in column1; and search textbox2 text in column 2 o 3 depending on whether it is copper or aluminum

in the case of the foto the solution would be 1.15 selected...not the whole line

the foto is a montage.
Sem Título.jpg



thank you all.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Try the following code (change the name of the commandbutton and listview accordingly)...

VBA Code: 
Private Sub CommandButton1_Click()

    Dim LstItem As ListItem
    Set LstItem = Me.ListView1.FindItem(Me.TextBox1.Value, lvwText)
    
    If LstItem Is Nothing Then
        MsgBox "ListItem not found!", vbExclamation
        Exit Sub
    End If
    
    Dim SubItemIndex As Long
    Select Case Me.TextBox2.Value
        Case "Copper"
            SubItemIndex = 1
        Case "Aluminum"
            SubItemIndex = 2
        Case Else
            SubItemIndex = 0
    End Select
    
    If SubItemIndex = 0 Then
        MsgBox "SubItem not found!", vbExclamation
        Exit Sub
    End If
    
    Me.ListView1.SetFocus
    
    LstItem.Selected = True
    
    LstItem.EnsureVisible
    
    MsgBox LstItem.SubItems(SubItemIndex)
    
End Sub

Hope this helps!
 
Upvote 0
Domenic said:
Try the following code (change the name of the commandbutton and listview accordingly)...

VBA Code: 
Private Sub CommandButton1_Click()

    Dim LstItem As ListItem
    Set LstItem = Me.ListView1.FindItem(Me.TextBox1.Value, lvwText)
 
    If LstItem Is Nothing Then
        MsgBox "ListItem not found!", vbExclamation
        Exit Sub
    End If
 
    Dim SubItemIndex As Long
    Select Case Me.TextBox2.Value
        Case "Copper"
            SubItemIndex = 1
        Case "Aluminum"
            SubItemIndex = 2
        Case Else
            SubItemIndex = 0
    End Select
 
    If SubItemIndex = 0 Then
        MsgBox "SubItem not found!", vbExclamation
        Exit Sub
    End If
 
    Me.ListView1.SetFocus
 
    LstItem.Selected = True
 
    LstItem.EnsureVisible
 
    MsgBox LstItem.SubItems(SubItemIndex)
 
End Sub

Hope this helps!
Click to expand...
this code is working fine and i thank you a lot...but im sorry thats not the point...what i need...but i dont know if it is possible...is the value 1.15 selected in the listview after i press enter in the second textbox not a message and no botton click...new foto added...thank you.
 

Attachments

  • Sem Título.jpg
    Sem Título.jpg
    119.5 KB · Views: 30
Upvote 0
Let me see if I understood you correctly. You want the user to click on 1,15, and then you want the corresponding value from the Section column (16) entered in TextBox 1, and the corresponding header (Copper) entered in TextBox2, is this correct?
 
Upvote 0
Domenic said:
Let me see if I understood you correctly. You want the user to click on 1,15, and then you want the corresponding value from the Section column (16) entered in
Domenic said:
Let me see if I understood you correctly. You want the user to click on 1,15, and then you want the corresponding value from the Section column (16) entered in TextBox 1, and the corresponding header (Copper) entered
Click to expand...
Click to expand...

Domenic said:
Let me see if I understood you correctly. You want the user to click on 1,15, and then you want the corresponding value from the Section column (16) entered in TextBox 1, and the corresponding header (Copper) entered in TextBox2, is this correct?
Click to expand...
Hi...Domenic...it's just the opposite...I want to fill textbox1 ... textbox2 ... and when pressing enter the value corresponding to the search for the textboxe's appears selected in listview1.
thank you.
 
Upvote 0
Unfortunately, I'm not aware of a way to select/highlight only the corresponding single subitem. How about formatting the subitem instead? If this is acceptable, try the following code that will bold and change the font color to red...

VBA Code: 
Private Sub CommandButton1_Click()

    clearListSubItemsFormatting

    With Me
        With .TextBox1
            If Len(.Value) = 0 Then
                MsgBox "Please enter the section, and try again!", vbExclamation, "Section"
                .SetFocus
                Exit Sub
            End If
        End With
        With .TextBox2
            If Len(.Value) = 0 Then
                MsgBox "Please enter the material, and try again!", vbExclamation, "Material"
                .SetFocus
                Exit Sub
            End If
        End With
    End With

    Dim foundListItem As ListItem
    Set foundListItem = Me.ListView1.FindItem(Me.TextBox1.Value, lvwText)
 
    If foundListItem Is Nothing Then
        With Me.TextBox1
            MsgBox .Value & " was not found!", vbExclamation, "Section"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If
 
    Dim headerItem As columnHeader
    Dim listSubItemsIndex As Long
    listSubItemsIndex = 0
    For Each headerItem In Me.ListView1.ColumnHeaders
        If UCase(headerItem) = UCase(Me.TextBox2.Value) Then
            listSubItemsIndex = headerItem.Index - 1
            Exit For
        End If
    Next headerItem
 
    If listSubItemsIndex = 0 Then
        With Me.TextBox2
            MsgBox .Value & " was not found!", vbExclamation, "Material"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If
 
    With foundListItem.ListSubItems(listSubItemsIndex)
        .Bold = True
        .ForeColor = vbRed 'change as desired
    End With
 
    Me.ListView1.Refresh
 
    foundListItem.EnsureVisible
 
End Sub

Private Sub clearListSubItemsFormatting()

    Dim i As Long
    Dim j As Long
 
    With Me.ListView1
        For i = 1 To .ListItems.Count
            For j = 1 To .ListItems(i).ListSubItems.Count
                With .ListItems(i).ListSubItems(j)
                    .Bold = False
                    .ForeColor = vbBlack
                End With
            Next j
        Next i
        .Refresh
    End With
 
End Sub

Hope this helps!
 
Upvote 0
Domenic said:
Unfortunately, I'm not aware of a way to select/highlight only the corresponding single subitem. How about formatting the subitem instead? If this is acceptable, try the following code that will bold and change the font color to red...

VBA Code: 
Private Sub CommandButton1_Click()

    clearListSubItemsFormatting

    With Me
        With .TextBox1
            If Len(.Value) = 0 Then
                MsgBox "Please enter the section, and try again!", vbExclamation, "Section"
                .SetFocus
                Exit Sub
            End If
        End With
        With .TextBox2
            If Len(.Value) = 0 Then
                MsgBox "Please enter the material, and try again!", vbExclamation, "Material"
                .SetFocus
                Exit Sub
            End If
        End With
    End With

    Dim foundListItem As ListItem
    Set foundListItem = Me.ListView1.FindItem(Me.TextBox1.Value, lvwText)

    If foundListItem Is Nothing Then
        With Me.TextBox1
            MsgBox .Value & " was not found!", vbExclamation, "Section"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If

    Dim headerItem As columnHeader
    Dim listSubItemsIndex As Long
    listSubItemsIndex = 0
    For Each headerItem In Me.ListView1.ColumnHeaders
        If UCase(headerItem) = UCase(Me.TextBox2.Value) Then
            listSubItemsIndex = headerItem.Index - 1
            Exit For
        End If
    Next headerItem

    If listSubItemsIndex = 0 Then
        With Me.TextBox2
            MsgBox .Value & " was not found!", vbExclamation, "Material"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If

    With foundListItem.ListSubItems(listSubItemsIndex)
        .Bold = True
        .ForeColor = vbRed 'change as desired
    End With

    Me.ListView1.Refresh

    foundListItem.EnsureVisible

End Sub

Private Sub clearListSubItemsFormatting()

    Dim i As Long
    Dim j As Long

    With Me.ListView1
        For i = 1 To .ListItems.Count
            For j = 1 To .ListItems(i).ListSubItems.Count
                With .ListItems(i).ListSubItems(j)
                    .Bold = False
                    .ForeColor = vbBlack
                End With
            Next j
        Next i
        .Refresh
    End With

End Sub

Hope this helps!
Click to expand...
Hi Dominic...helps, and a lot...amazing...

sorry but not wanting to abuse your knowledge ... how can i format the section too?

ex= 16 and copper in red in the listview1

thank you very mutch.
 
Upvote 0
Sure, that's no problem. Try the following amended code...

VBA Code: 
Private Sub CommandButton2_Click()

    clearListSubItemsFormatting

    With Me
        With .TextBox1
            If Len(.Value) = 0 Then
                MsgBox "Please enter the section, and try again!", vbExclamation, "Section"
                .SetFocus
                Exit Sub
            End If
        End With
        With .TextBox2
            If Len(.Value) = 0 Then
                MsgBox "Please enter the material, and try again!", vbExclamation, "Material"
                .SetFocus
                Exit Sub
            End If
        End With
    End With

    Dim foundListItem As ListItem
    Set foundListItem = Me.ListView1.FindItem(Me.TextBox1.Value, lvwText)
    
    If foundListItem Is Nothing Then
        With Me.TextBox1
            MsgBox .Value & " was not found!", vbExclamation, "Section"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If
    
    Dim headerItem As columnHeader
    Dim listSubItemsIndex As Long
    listSubItemsIndex = 0
    For Each headerItem In Me.ListView1.ColumnHeaders
        If UCase(headerItem) = UCase(Me.TextBox2.Value) Then
            listSubItemsIndex = headerItem.Index - 1
            Exit For
        End If
    Next headerItem
    
    If listSubItemsIndex = 0 Then
        With Me.TextBox2
            MsgBox .Value & " was not found!", vbExclamation, "Material"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If
    
    'format list item
    With foundListItem
        .Bold = True
        .ForeColor = vbRed
    End With
    
    'format list subitem
    With foundListItem.ListSubItems(listSubItemsIndex)
        .Bold = True
        .ForeColor = vbRed
    End With
    
    Me.ListView1.Refresh
    
    foundListItem.EnsureVisible
    
End Sub

Private Sub clearListSubItemsFormatting()

    Dim i As Long
    Dim j As Long
    
    With Me.ListView1
        For i = 1 To .ListItems.Count
            'clear formatting for list item
            With .ListItems(i)
                .Bold = False
                .ForeColor = vbBlack
            End With
            'clear formatting for list subitems
            For j = 1 To .ListItems(i).ListSubItems.Count
                With .ListItems(i).ListSubItems(j)
                    .Bold = False
                    .ForeColor = vbBlack
                End With
            Next j
        Next i
        .Refresh
    End With
    
End Sub

Cheers!
 
Upvote 0
Solution
Domenic said:
Sure, that's no problem. Try the following amended code...

VBA Code: 
Private Sub CommandButton2_Click()

    clearListSubItemsFormatting

    With Me
        With .TextBox1
            If Len(.Value) = 0 Then
                MsgBox "Please enter the section, and try again!", vbExclamation, "Section"
                .SetFocus
                Exit Sub
            End If
        End With
        With .TextBox2
            If Len(.Value) = 0 Then
                MsgBox "Please enter the material, and try again!", vbExclamation, "Material"
                .SetFocus
                Exit Sub
            End If
        End With
    End With

    Dim foundListItem As ListItem
    Set foundListItem = Me.ListView1.FindItem(Me.TextBox1.Value, lvwText)
   
    If foundListItem Is Nothing Then
        With Me.TextBox1
            MsgBox .Value & " was not found!", vbExclamation, "Section"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If
   
    Dim headerItem As columnHeader
    Dim listSubItemsIndex As Long
    listSubItemsIndex = 0
    For Each headerItem In Me.ListView1.ColumnHeaders
        If UCase(headerItem) = UCase(Me.TextBox2.Value) Then
            listSubItemsIndex = headerItem.Index - 1
            Exit For
        End If
    Next headerItem
   
    If listSubItemsIndex = 0 Then
        With Me.TextBox2
            MsgBox .Value & " was not found!", vbExclamation, "Material"
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Exit Sub
    End If
   
    'format list item
    With foundListItem
        .Bold = True
        .ForeColor = vbRed
    End With
   
    'format list subitem
    With foundListItem.ListSubItems(listSubItemsIndex)
        .Bold = True
        .ForeColor = vbRed
    End With
   
    Me.ListView1.Refresh
   
    foundListItem.EnsureVisible
   
End Sub

Private Sub clearListSubItemsFormatting()

    Dim i As Long
    Dim j As Long
   
    With Me.ListView1
        For i = 1 To .ListItems.Count
            'clear formatting for list item
            With .ListItems(i)
                .Bold = False
                .ForeColor = vbBlack
            End With
            'clear formatting for list subitems
            For j = 1 To .ListItems(i).ListSubItems.Count
                With .ListItems(i).ListSubItems(j)
                    .Bold = False
                    .ForeColor = vbBlack
                End With
            Next j
        Next i
        .Refresh
    End With
   
End Sub

Cheers!
Click to expand...


working 5 stars...once again thank you.
 
Upvote 0
@paroduso - Please note you need to mark the solution post to finalize your question, not your own “thank you” post.

This is your second question that I changed the solution post with the one that you found as the answer.

Please mark the solution post next time instead of your “thank you” post.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
680
Kakaska
K
T
  • Question
Listview Find item
Replies
3
Views
4K
Domenic
D
T
  • Solved
Populate UserForm based off TextBox value
2
Replies
10
Views
1K
thechad
T
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
319
pure vito
P
D
  • Question
Helpwith my password sreadsheet please.
Replies
5
Views
471
dmt32
D

Forum statistics

Threads
1,214,805
Messages
6,121,656
Members
449,045
Latest member
Marcus05

Share this page

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