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?

 

[Norie]

Do you have any code to create the chart(s)?

 

[Waimea]

HI Norie,

I have some code that I am working with.

This code is in the userform2.

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

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

This code is in userform1.

Private Sub ListBox1_Click()
Dim r As Integer
r = ListBox1.ListIndex + 1


Sheets("Sheet6").Select
Cells(r + 1, 1).EntireRow.Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select


ListBox1.Value = ""

' This part I am not sure on how it really works, I want to specify the columns that I want to display data to
' I would also like to add a dynamic name to the chart


ActiveChart.SetSourceData Source:=Range("Sheet6!$r$6,Sheet6!$r$6,Sheet6!$F$6,Sheet6!$E$6") 
    
UserForm2.Show


End Sub

This code creates a chart for each row in the rowsource, however it doesn't update the image in userform2.

 

[Norie]

Are you sure the second code creates a chart for each row in the row source?

It looks to me as though it creates a chart with hard coded data from the range 'Sheet6!$r$6,Sheet6!$r$6,Sheet6!$F$6,Sheet6!$E$6'.

Is that range even in the row source range?

 

[Waimea]

Hi Norie,

thank you for your reply!

The code creates a chart but I am not sure on how/why it does it.

It also creates a new chart on every click so I am trying to delete the chart after the userform2 is closed.

Below is a recorded macro for one chart on one row in the rowsource.

Range("A6,D6,E6,F6").Select
    Range("F6").Activate
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range( _
        "Sheet6!$A$6,Sheet6!$D$6,Sheet6!$E$6,Sheet6!$F$6")

1. I want to create a chart of the row in the rowsource that I clicked in the listbox.
2. Display chart in userform2
3. Delete chart on sheet6 afterupdate

 

[Waimea]

Now I have it almost working however I am not sure on how to change the range that the chart is using?

Create the chart with double click event.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Integer
r = ListBox1.ListIndex + 1


Sheets("Sheet6").Select
Cells(r + 1, 1).EntireRow.Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select


ListBox1.Value = ""


ActiveChart.SetSourceData Source:=Range("Sheet6!$r$6,Sheet6!$r$6,Sheet6!$F$6,Sheet6!$E$6")
ActiveChart.Parent.Name = "Test"   
UserForm2.Show

End Sub

Delete chart "Test" when userform2 is closed

Private Sub UserForm_Terminate()
ActiveSheet.ChartObjects("test").Delete
End Sub

This part of the code I haven't figured out yet.

ActiveChart.SetSourceData Source:=Range("Sheet6!$r$6,Sheet6!$r$6,Sheet6!$F$6,Sheet6!$E$6")

I want to keep the sheet6! reference but add the row number that I am currently on which is "r".

 

[Waimea]

Any suggestions on how I can concatenate the Range() to include columns A,D,E and F with the selected row variable (r) ??

 

[Norie]

Perhaps.

Dim rngChart As Range

    With Sheets("Sheet6")
         Set rngChart = Intersect(.Range("R:R, R:R, F:F, E:E"), .Rows(r))
    End With

    ActiveChart.SetSourceData Source:=rngChart

 

[Waimea]

Hi Norie,

thank you for your reply! Your code works but my code seems cluttered.

How can I improve my code?

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Integer
r = ListBox1.ListIndex + 1


Sheets("Sheet6").Select
Cells(r + 1, 1).EntireRow.Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select


ListBox1.Value = ""
Dim rngChart As Range




    With Sheets("Sheet6")
         Set rngChart = Intersect(.Range("R:R, R:R, F:F, E:E"), .Rows(r))
    End With


    ActiveChart.SetSourceData Source:=rngChart

ActiveChart.Parent.Name = "Test"   
UserForm2.Show


End Sub