In my environment I can't see the link you provided, so it may be an irrelevant answer.
First, prepare two excel sheets(Ex. sheet1 and sheet2).
prepare two combobox and two commandbutton in the userform.
The two combobox store the sheet names of the two books In UserForm_Initialize() function.
Select an item from each of the two combobox.
Click the first button to link Sheet1 and Sheet2 with the sheets of the two books selected in the combobox in CommandButton1_Click() function.
Click the second button to display a scatter plot of the data on the two sheets in CommandButton2_Click() function..
Modify data range used to draw the graph, graph type and each Const data for your envronment.
- Dim SheetA As String
-
Dim SheetB As String
-
Const Path As String = "C:\Excel_data\"
-
Const BookA_fname = "BookA.xlsx"
-
Const BookB_fname = "BookB.xlsx"
-
Const BookA As String = Path & BookA_fname
-
Const BookB As String = Path & BookB_fname
-
-
Private Sub UserForm_Initialize()
-
Dim ws As Worksheet
-
Workbooks.Open Filename:=BookA
-
With ComboBox1
-
For Each ws In ActiveWorkbook.Worksheets
-
.AddItem ws.Name
-
Next
-
End With
-
Workbooks.Open Filename:=BookB
-
With ComboBox2
-
For Each ws In ActiveWorkbook.Worksheets
-
.AddItem ws.Name
-
Next
-
End With
-
Workbooks(BookA_fname).Close
-
Workbooks(BookB_fname).Close
-
End Sub
-
-
Private Sub ComboBox1_Change()
-
SheetA = ComboBox1.List(ComboBox1.ListIndex)
-
End Sub
-
-
Private Sub ComboBox2_Change()
-
SheetB = ComboBox2.List(ComboBox2.ListIndex)
-
End Sub
-
-
Private Sub CommandButton1_Click()
-
'Link both workbook data
-
Dim i1 As Long
-
Dim j1 As Long
-
Dim i2 As Long
-
Dim j2 As Long
-
Worksheets("Sheet1").Select
-
For i1 = 1 To 5
-
For j1 = 1 To 2
-
Cells(i1, j1).value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
-
Next j1
-
Next i1
-
Worksheets("Sheet2").Select
-
For i2 = 1 To 5
-
For j2 = 1 To 2
-
Cells(i2, j2).value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
-
Next j2
-
Next i2
-
End Sub
-
-
Private Sub CommandButton2_Click()
-
'Plot Graph
-
Range("A1:B5").Select
-
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
-
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
-
ActiveChart.Axes(xlValue).MajorGridlines.Select
-
ActiveChart.SeriesCollection.NewSeries
-
ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
-
ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
-
ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
-
ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
-
End Sub