none
2 Questions about Bar Charts via Code RRS feed

  • Question

  • I am using Excel 2010.

    These 2 question are easy but I am very short on time. I have to turn this around quickly. Sorry.

    My 2 questions are . . .

    1.) I The user wants the bar chart to be on the first row in Column F. How do I move the chart to that location.

    2.) At the moment the Y axis shows 25%, 50% 100%. I need the Y axis to show money 1 million 2 million and so. It should be like the numbers in the data. Today the numbers is in millions so the chart should be the same. If some day the numbers is below 1 million so should the Y axis.

    Here is my code.

    Sub Chart_It()

    Dim lastrow As Integer
    Dim nRow As Long
    Dim strWBName As String
    Dim strWBTemplateName As String

    strWBTemplateName = ActiveWorkbook.Name
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy
    Workbooks.Add

    strWBName = ActiveWorkbook.Name
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit

    Range("A1").Select
    Application.DisplayFullScreen = True
    lastrow = ActiveSheet.UsedRange.Rows.Count

    'Go thru first column and change number of month to month name

    nRow = 3
    Do While nRow <= lastrow
      Cells(nRow, 1).Value = MonthName(Cells(nRow, 1).Value)
      nRow = nRow + 1
    Loop

    'Create Stacked Chart

    Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked100).Select
    ActiveChart.SetSourceData Source:=Range("A1:C" & lastrow)

    Windows(strWBTemplateName).Activate
    ActiveWorkbook.Close savechanges:=False
     

    End Sub

    Friday, October 20, 2017 2:08 AM

All replies

  • Try the following for the first question

        Dim shp As Shape
        Dim lastRow As Long

        lastRow = ActiveSheet.UsedRange.Rows.Count   'Used here for testing
       
        Range("A1").Select
        Selection.CurrentRegion.Select
        Set shp = ActiveSheet.Shapes.AddChart2(297, xlColumnStacked100)  'Remove .Select from end of your line
        With shp
            .Select
            .Top = Range("F1").Top
            .Left = Range("F1").Left
        End With

        ActiveChart.SetSourceData Source:=Range("A1:C" & lastRow)

    For the second question see if the following link will help.

    https://excelribbon.tips.net/T007888_Numeric_Value_and_Percentage_Value_in_a_Graph_Column.html


    Regards, OssieMac

    Friday, October 20, 2017 3:54 AM