I have created the following macro to draw graphs from selected data. I want to convert the macro so that it will draw a graph FROM WHICHEVER data is selected in the spreadsheet. How do I do it?

' Keyboard Shortcut: Ctrl+w
'
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Default"
ActiveChart.SetSourceData Source:=Sheets("AF objs % responses").Range("B5:C8" _
)
ActiveChart.Location Where:=xlLocationAsObject, Name:="AF objs % responses"
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "P"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
End Sub

Many thanks

One way to do it is to define a range at the beginning, then use the InputBox to get the range desired:

Adjust as needed.

HTH

My code now as follows.

It doesn't like:-
"ActiveChart.SetSourceData Source:=Sheets("AF objs % responses").Range("myRng")" and crashes at that point.

What am I doing wrong?


Dim myRng As Range
Prompt = "Select cells for work"
Title = "Select a Range"
SetmyRng = Application.InputBox(Prompt:=Prompt, Title:=Title)
Charts.Add

ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Default"
ActiveChart.SetSourceData Source:=Sheets("AF objs % responses").Range("myRng")
ActiveChart.Location Where:=xlLocationAsObject, Name:="AF objs % responses"
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "P"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
End Sub

Just new here so hope i dont tread on toes ... but I just noticed

----------------------------------------------------------------------
SetmyRng = Application.InputBox(Prompt:=Prompt, Title:=Title)
Charts.Add

ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Default"
ActiveChart.SetSourceData Source:=Sheets("AF objs % responses").Range("myRng")
------------------------------------------------------------------------

You need a space between 'Set' and 'myrng' and then remove quotes from .Range("myRng")

D

Welcome to the board. You're not stepping on any toes. Any help is appreciated. I didn't get back to the removing of the quotes, so good correction.