0

I have some data in a worksheet with which I need to create charts. These charts are going to populate different pages of a multipage in a userform. These pages need to be populated in the sequence the charts are created.

I have no difficulty with the charts and creating an image from them. I can copy that image to the first page (it already has an image object). In order to create a new page, I'm using the following code:

MultiPage1.Pages.Add
MultiPage1.Pages(0).Controls.Copy
MultiPage1.Pages(k).Paste

When I copy the controls, it also copies the previous image. That shouldn't be a problem as long as the new image gets replaced by the newly created chart image. The problem is, the new chart is being copied to the first page, not to the last one created (but this is not always the case... I don't understand). I tried to force the selection of the new page by using UserForm2.MultiPage1.Value = k but it's still not working.

Here is the code I wrote:

Dim MyChart         As Chart
Dim ChartData       As Range
Dim ChartName       As String
Dim thiswb          As Workbook
Dim imageName       As String
Dim nColunas        As Long       
Dim i, j, k         As Integer 

Set thiswb = ThisWorkbook

k = 0
With thisWb.Sheets(3)
nColunas = .Cells(2, .Columns.Count).End(xlToLeft).Column
For i = 1 To nColunas - 1 Step 3
    If i > 1 Then
        MultiPage1.Pages.Add
        MultiPage1.Pages(0).Controls.Copy
        MultiPage1.Pages(k).Paste
    End If
    Set ChartData = .Range(.Cells(2, i), .Cells(95, i))
    Set MyChart = .Shapes.AddChart(xlXYScatterLines).Chart
    MyChart.SeriesCollection(1).Values = ChartData
    MyChart.SeriesCollection(1).XValues = .Range(.Cells(2, i + 1), .Cells(95, i + 1))

    imageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"
    MyChart.Export Filename:=imageName, FilterName:="GIF"
    .ChartObjects(1).Delete

    UserForm2.MultiPage1.Value = k
    UserForm2.Image1.Picture = LoadPicture(imageName)
    Kill imageName

    k = k + 1
Next i
End With

If I have 4 charts to display, the final result will be: chart4/chart1/chart2/chart3 instead of chart1/chart2/chart3/chart4

João Lima
  • 55
  • 6

1 Answers1

0

Nevermind, I solved it! Instead of using an image object inside each page of multipage1, I removed them and replaced 

 UserForm2.Image1.Picture = LoadPicture(imageName)

with

 UserForm2.MultiPage1.Pages(k).Picture = LoadPicture(imageName)
João Lima
  • 55
  • 6