Excel VBA Chart

Question

I am required to create many charts on excel because I have many sets of numbers. Ideally, I would like to create a scatter plot and I am new in learning how to use excel and VBA.

I would like to create a template module code to generate a scatter plot where I can choose X and Y values for columns or rows that are not next to each other. Furthermore, I would like to be able to format the plot's details - font, size, etc.

Could someone help me create a simple template? Thank you.

I have tried doing this. I essentially want to be able to use this Macro multiple times to be able to generate many scatter plots with the same formatting, but selecting different X and Y values every single time.

What can I add to this code so that when I run it it prompts me to select desired X and Y values?

Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'   Dim rng As Range
    Set rng = Application.InputBox(prompt:="Sample", Type:=8)
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=rng
End Sub

Answer 1

This is a bit rough and ready but shows some ideas. Apologies if any typos whilst entering via this device and you definitely want to implement better error handling.

Option Explicit

Public Sub Test()

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data") 'change as appropriate

    Application.ScreenUpdating = False

    BuildChart ws, SelectRanges(ws)

    Application.ScreenUpdating = True

End Sub

Private Function SelectRanges(ByRef ws As Worksheet) As Range

     Dim rngX As Range
     Dim rngY As Range

     ws.Activate

     Application.DisplayAlerts = False

     On Error Resume Next

     Set rngX = Application.InputBox("Please select X values. One column.", Type:=8)

     If rngX Is Nothing Then GoTo InvalidSelection

     Set rngY = Application.InputBox("Please select Y values. One column.", Type:=8)

     If rngY Is Nothing Then GoTo InvalidSelection

     If rngX.Columns.Count > 1 Or rngY.Columns.Count > 1 Then GoTo InvalidSelection

     On Error GoTo 0

     Set SelectRanges = Union(rngX, rngY)
     Application.DisplayAlerts = True
     Exit Function

InvalidSelection:
    If rngX Is Nothing Or rngY Is Nothing Then
        MsgBox "Please ensure you have selected both X and Y ranges."
    ElseIf rngX.Rows.Count <> rngX.Rows.Count Then
         MsgBox "Please ensure the same number of rows are selected for X and Y ranges"
    ElseIf rngX.Columns.Count > 1 Or rngY.Columns.Count > 1 Then
        MsgBox "Please ensure X range has only one column and Y range has only one column"
    Else
       MsgBox "Unspecified"
    End If

    Application.DisplayAlerts = True
    End

End Function

Private Sub BuildChart(ByRef ws As Worksheet, ByRef unionRng As Range)

     With ws.Shapes.AddChart2(240, xlXYScatter).Chart
        .SetSourceData Source:=unionRng
    End With

End Sub