PDA

View Full Version : Search textbox and create chart in userform



waimea
02-27-2019, 05:41 AM
Hi,

I have an userform with a textbox and a combobox and when I select an item in the combobox I would like to show the chart for that item in an image element in the userform.

Also I am trying to search a textbox and display a chart based on all matches, so if there are 3 matches the chart would show values for all those items. If there are 5 matches I would like to see a chart with the combined values of those 5 items.

The userform is called userform1, the combobox is called combobox1, the textbox is called textbox1.

I have code to create a chart, export it to a gif file and then load the gif file in the image1 box.



Public Sub GetChart()
Dim CurrentChart As Variant
Dim Fname As Variant

On Error Resume Next

Set CurrentChart = Sheet1.ChartObjects("Test").Chart
CurrentChart.Parent.Width = 250
CurrentChart.Parent.Height = 250

' Fname = ThisWorkbook.Path & "/temp.gif"
' CurrentChart.Export Filename:=Fname, FilterName:="GIF"

Image1.Picture = LoadPicture(Fname)

' Delete file
'Kill Fname
End Sub




Code to create a chart for one item below:



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

Sheet1.Select
Cells(selectedRow + 1, 1).EntireRow.Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select

Dim rngChart As Range

With Sheets("Sheet1")
On Error Resume Next
Set rngChart = Intersect(.Range("A:A, D:D, F:F, E:E"), .Rows(selectedRow))
End With

'Name the chart
ActiveChart.Parent.Name = "Test"
ActiveChart.SetSourceData Source:=rngChart

'Delete all sheets on sheet1
'Worksheets("Sheet1").ChartObjects.Delete
End Sub


I don't know how to go from combobox selection to show that peticular chart and I don't know how to display a chart for say 5 matches in the textbox search.

All suggestions on how I can improve my code are welcome!

waimea
02-27-2019, 01:09 PM
This is the code that I am using to search the listbox with a textbox.


Private Sub TextBox1_Change()

Application.ScreenUpdating = False
Dim endarr(), ListEndRow As Long, lrows As Long, i As Long, j As Long
If TextBox1.Text = vbNullString Then ListBox1.List = sn: Exit Sub

ListEndRow = 1
With Sheet1
lrows = .ListObjects(1).DataBodyRange.Rows.Count
ReDim endarr(1 To lrows, 1 To 14)
For i = 1 To UBound(sn)
If Left(sn(i, 1), Len(TextBox1.Text)) = TextBox1.Text Then
For j = 1 To 14
endarr(ListEndRow, j) = sn(i, j)
Next
ListEndRow = ListEndRow + 1
End If
Next
End With
ListBox1.List = endarr
Application.ScreenUpdating = True
End Sub



sn = Sheet1.ListObjects("Table1").DataBodyRange.Value

ComboBox1.List = sn
ListBox1.List = sn

I want to integrate getchart and the create chart code into this search code?

waimea
02-28-2019, 12:14 AM
All suggestions on how to proceed are welcome?

I think that I need to break this task down into smaller steps:

1. Check if the list is filtered
2. Filter list
3. Update list
4. Hide not filtered items

5. Create a chart with xlCellTypeVisible
5. Create a chart from the filtered list box

6. Create a gif file from a chart
7. Show gif file
8. Delete gif file
9. Reset list

waimea
02-28-2019, 07:41 AM
Turns out having one chart for a large amount of items isn't a good idea.

Now I am trying to create an if statement that creates one chart on a click and doubleclick event on a listbox.

I am having trouble with getting the correct picture.

Any suggestions on how to improve the getchart procedure?

waimea
03-01-2019, 12:26 AM
Private Sub TextBox1_Change()


Application.ScreenUpdating = False
Dim endarr(), ListEndRow As Long, lrows As Long, i As Long, j As Long
If TextBox1.Text = vbNullString Then ListBox1.List = sn: Exit Sub

ListEndRow = 1
With Sheet1
lrows = .ListObjects(1).DataBodyRange.Rows.Count
ReDim endarr(1 To lrows, 1 To 14)
For i = 1 To UBound(sn)
If Left(sn(i, 1), Len(TextBox1.Text)) = TextBox1.Text Then
For j = 1 To 14
endarr(ListEndRow, j) = sn(i, j)
Next
ListEndRow = ListEndRow + 1
End If
Next
End With
ListBox1.List = endarr
Application.ScreenUpdating = True
End Sub


This code gives me blank rows below the search results.

How can I fix this?

waimea
03-01-2019, 08:45 AM
I think that I need to add second for loop to get rid of the empty/blank results in the listbox.

Can anyone give me a hint?