Microsoft Excel VBA Plot Macro

Question

Using VBA, I would like to create a macro that will plot a selected region prior to running the macro. I have a macro that can plot the same data selection each time, but I would like it to plot a selection. My current code is included below. Thanks.

Sub AddChart()
'
' AddChart Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
 With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B9")
        .Chart.ChartType = xlXYScatter
    End With


End Sub

Mike · Accepted Answer · 2019-01-25 21:49:27Z

1

If you're looking to turn the currently selected cells into a chart, you can use Application.Selection. When you have a cell or cells selected, Application.Selection will return a range value. We can feed that directly into the source.

We check the typeOf to ensure that the current selection is a range to avoid any pesky errors.

Public Sub AddChart()
'
' AddChart Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'

If TypeOf Selection Is Range Then
 With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.SetSourceData Source:=Application.Selection
        .Chart.ChartType = xlXYScatter
    End With
End If

End Sub

answered Jan 25, 2019 at 21:49

Mike

6444 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcelo Scofano Diniz · Accepted Answer · 2019-01-25 21:38:06Z

0

If I correctly understood:

Sub AddChart()
'
' AddChart Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim auxStr As String
auxStr = InputBox("Enter valid cell range, like A1:B9")
 With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range(auxStr)
        .Chart.ChartType = xlXYScatter
  End With
End Sub

You may add other variables too, for Left, Width, etc; all using the Inputbox. Of course, if you need to edit a lot of variables, a modal Userform will be easier to maintain and the right way to not allow wrong inputs.

edited Jan 25, 2019 at 21:38

answered Jan 25, 2019 at 21:01

Marcelo Scofano Diniz

7091 gold badge12 silver badges27 bronze badges

Collectives™ on Stack Overflow

Microsoft Excel VBA Plot Macro

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related