Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2014
    Posts
    3

    Unanswered: Macro for a selected range

    Hello, this is my very first post, so bear with me.

    We have a standard format we need charts in excel to be, so I thought it would be useful to make macros to put the chart into the correct format. I recorded the following using the recorder, and I thought I could just change what it effects to the selected cells. However, I have found very little out there helpful. Any help is always apprecieated.

    Code:
    Sub Chart_Body_Format()
    '
    ' Chart_Body_Format Macro
    ' Format chart body to Novus Format
    '
    ' Keyboard Shortcut: Ctrl+Shift+B
    '
        
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I must confess I don't know what you actual problem is?

    The only problem I can see is that if your selection is not 2 dimension then the inside vertical and horizontal line option(s) will error.

    ??

    MTB

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    from
    We have a standard format we need charts in excel to be, so I thought it would be useful to make macros to put the chart into the correct format
    Im guessing the OP wants to be able to specify the data for the chart to be used in the macro.

    but as the recorded macro relies on a preselected range there is no way to alter the data WITHIN this macro. what I'd expect is soem code to workout what data is wanted and therefore what range of cells & columns is required, then press on with this macro

    ...but that's just a guess
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2014
    Posts
    3
    Quote Originally Posted by healdem View Post
    from
    Im guessing the OP wants to be able to specify the data for the chart to be used in the macro.

    but as the recorded macro relies on a preselected range there is no way to alter the data WITHIN this macro. what I'd expect is soem code to workout what data is wanted and therefore what range of cells & columns is required, then press on with this macro

    ...but that's just a guess
    Thanks for the responses. Sorry if I wasn't clear, but I am new to the macro thing and I am feeling it out. Recording macros seems like the easiest way for me to get close to what I want to do with the least time spent.

    To clarify, I have found 2 ways recorded macros work; for a fixed range (B1:B10, Sheet1), or starting from the selected cell (Selected Cell to Selected Cell +10). What I was hoping to achieve was a macro which goes a step past that and will function over a selected range. (highlighted B1:B15, or highlighted A20:C57) Since your charts are not always the same dimensions, a static macro wouldn't be useful.

    thanks for your help.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what isn't working then
    your macro works on the currently selected range, assuming you have selected a range

    and yes, recording a macro is a good way of seeing what you need to do to write VBA code in Excel. Ive always found it a great way to start developing a function
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2014
    Posts
    3

    requote of macro

    Quote Originally Posted by healdem View Post
    so what isn't working then
    your macro works on the currently selected range, assuming you have selected a range

    and yes, recording a macro is a good way of seeing what you need to do to write VBA code in Excel. Ive always found it a great way to start developing a function
    Below I re-recorded the macro, without trying to change it at all beforehand (which I may have done with the last one). It functions for the 4x20 cells listed, but when you selected a different dimension, it just uses the 4x20.

    I assume I need to change the ActiveCell.Range("A120").Select to something else to get it to do what I am trying to do.

    Code:
    Sub Macro3()
    '
    ' Macro3 Macro
    ' test the function on the 1st chart formatter
    '
    ' Keyboard Shortcut: Ctrl+p
    '
        ActiveCell.Range("A1:D20").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So specify the active range.
    your code specifys A020, so 4 columns or 20 rows is what youd expect.

    Your first macro just used the current selected range, meaning it should work with any pre selected range. In Macro3 you specify the range in the code, so that is what you get.

    Again I dont see the problem. Just run tbe code, tweak it, re run the code and refine the process.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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