Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: excel macro problem

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    One way to do it is to define a range at the beginning, then use the InputBox to get the range desired:
    Code:
    Dim myRng as Range
    Prompt = "Select cells for work"
    Title = "Select a Range"
    
    Set myRng = Application.InputBox(Prompt:=Prompt, Title:=Title)
    
    ActiveChart.SetSourceData Source:=Sheets("AF objs % responses").Range("myRng")

    Adjust as needed.

    HTH
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Newcastle UK
    Posts
    10
    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

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Originally posted by redma_d
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •