Adding/Removing items from ListBox and from RowSource

[Waimea]

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:

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:

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?

 

[Waimea]

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.

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]

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]

How are you populating the listbox?

 

[Waimea]

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]

If you want to delete from the row source try something like this.

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]

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.

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]

What exactly is in the listbox?

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

 

[Waimea]

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?

 

[Waimea]

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?