Adding/Removing items from ListBox and from RowSource

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
I am trying to add and remove items from an activex listbox with multiple columns.

Whis this code I only remove parts of the items in the listbox and in the rowsource, I think you could say that it doesn't remove all columns.

Code to remove items:
Code:
Private Sub CommandButton2_Click()
 Dim sFind As String, rFound As Range
    
    Select Case Me.ListBox1.Value
        Case Is <> vbNullString
            sFind = Me.ListBox1.Value
        
            With Sheet2
                Set rFound = .Cells.Find(what:=sFind, After:=.Cells(1, 1))
                
                If Not rFound Is Nothing Then
                    rFound.Delete Shift:=xlUp
                End If
            End With
        Case Else: Exit Sub
    End Select
End Sub

Code to add items:
Code:
Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Sheet3
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.ComboBox10.Value
        .Cells(lRow, 2).Value = Me.TextBox100.Value
        .Cells(lRow, 3).Value = Me.ComboBox11.Value
        .Cells(lRow, 4).Value = Me.TextBox101.Value
        .Cells(lRow, 5).Value = Me.TextBox102.Value
        .Cells(lRow, 6).Value = Me.TextBox103.Value
    End With
End Sub

What is a good way to add/remove items from a listbox? How can I delete items in the listbox and in the rowsource? Should my rowsource be a table?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
This code somewhat works but it deletes all similar items,

I only want to delete 1 row not all rows that are the same value.

Code:
Private Sub CommandButton2_Click()
Dim i As Integer


For i = 1 To Sheet2.Range("A10000").End(xlUp).Row
    If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Select
        Selection.Delete
    End If
Next i
End Sub
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
Any suggestions on how to remove one item at the time in a listbox? Or how to doubleclick a selected item and bring up another userform?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
How are you populating the listbox?
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372

ADVERTISEMENT

Hi Norie,

thank you for your reply! I am using rowsource to populate the listbox.

I am trying to remove "one item" and "all items" with two different buttons, commandbutton1 and commandbutton2.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
If you want to delete from the row source try something like this.
Code:
Private Sub CommandButton2_Click()
Dim sFind As String, rFound As Range
    
    Select Case Me.ListBox1.Value
        Case Is <> vbNullString
            sFind = Me.ListBox1.Value
        
            With Sheet2
                Set rFound = .Cells.Find(what:=sFind, After:=.Cells(1, 1))
                
                If Not rFound Is Nothing Then
                    Intersect(rFound.EntireRow, .Range(Me.ListBox1.RowSource)).Delete
                End If
            End With
        Case Else: Exit Sub
    End Select
End Sub
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372

ADVERTISEMENT

Hi Norie,

thank you for your reply and your solution works great!

Perhaps I should start another thread but is it possible to create charts from listboxes?

So if I select an item in the listbox and double click I open an userform and in that userform I would like to show a graph for that item in the listbox???

The code below shows an image in a userform but I want the chart/image to correspond to the selected item in the listbox.

Code:
Public Sub GetChart()
    Dim CurrentChart As Variant
    Dim Fname As Variant
       
    Set CurrentChart = Sheets("Chart").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "/temp.gif"
    CurrentChart.Export Filename:=Fname, FilterName:="GIF"
End Sub

Private Sub UserForm_Activate()
   Dim Fname As Variant
    
    GetChart
    Fname = ThisWorkbook.Path & "/temp.gif"
    Image1.Picture = LoadPicture(Fname)
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
What exactly is in the listbox?

Is it listing the names of the chart(s) you want to show on the userform?
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
Hi Norie,

thank you for your reply!

The listbox is a multicolumn listbox with 14 columns. The first column contains article ID and the 13 other columns contains various information like inventory number, ordered numbers.

The listbox is populated with rowsource from a table named Data. I guess I want each line in the row source to be a graph. Where the graph shows inventory/stock level and orders from that row in the data table.

To be more specific, I am using the double click event to load a second userform, in the second userform I want to show the graph for the selected line/item that I double clicked in the listbox.

Is this possible?
 
Last edited:

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
I want to click on a listbox1, open userform2 and show a graph in userform2 on the selected row in listbox1. Listbox1 uses rowsource and the rowsource is a table named Data.

Any suggestions on how I should proceed forward with this code?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,272
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top