Plotting a graph with multiple Y-variables using VBA

Contributor

So at the end of my project, we have created a sheet of data, and the last step is to create a macro that will be able to plot a graph based on the users input. I have created and implemented the following code although it is too basic and does not fit my needs, I was wondering if someone can suggest how I can adjust it accordingly. Essentially, the code asks the user to select an x variables, as well as 1 or 2 y-variables. The reality is that for my project I will have to create graphs with potentially 4 y-variables. Theoretically I imagine the new code could work like this: Ask user to input column of X-axis. Ask user to input y variables for the left-hand-side range. Ask user to input y variables for the right-hand-side range (so that correct units/scale is selected). Im not sure how I would imlpement this using VBA, any suggestions? Thanks very much for all of your help, it is much appreciated!

Option Explicit

Function GetCellFromUser(Prompt As String, title As String, default As range) As range
    Dim ans As range
    On Error Resume Next
    Set ans = Application.InputBox(Prompt, title, default.Address, , , , , 8)
    Set GetCellFromUser = ans
End Function


Public Sub CreateGraph()

Dim chrt As Object
Dim xaxis As range
Dim yaxis1 As range
Dim sinput As Integer
Dim dummy As Variant

Sheets("GraphSheet").Activate

Columns(1).NumberFormat = "hh:mm:ss"

    sinput = Application.InputBox("Please select the number of Y Variables (1,2 or 3)" & vbLf & vbLf & "1 Y Variable" & vbLf & "2 Y Variables", "Enter a value", 1)
    If sinput = False Then
        Exit Sub
    Else
        Select Case sinput
            Case 1
                MsgBox ("you have selected 1")   'change to your desired action for each case
            Case 2
                MsgBox ("you have selected 2")
            Case Else
                dummy = MsgBox("wrong input", vbCritical)
        End Select
    End If
    

    
    If sinput = 1 Then
    
    Set xaxis = GetCellFromUser("Please select the Column of data to be your X and Y-Axis's", "Selecting the X-axis Variable", ActiveCell)
    ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=xaxis


Else
Set xaxis = GetCellFromUser("Please select the Column of data to be your X and Y-Axis's", "Selecting the X-axis Variable", ActiveCell)
    ActiveSheet.Shapes.AddChart(201, xlXYScatter).Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=xaxis
ActiveChart.FullSeriesCollection(1).ChartType = xlXYScatter
ActiveChart.FullSeriesCollection(1).AxisGroup = 1
ActiveChart.FullSeriesCollection(2).ChartType = xlXYScatter
ActiveChart.FullSeriesCollection(2).AxisGroup = 2

End If

End Sub

 

0 Replies