Results 1 to 10 of 10

Thread: Need Help with Globally Automating View/Zoom/Fit selection

  1. #1

    Need Help with Globally Automating View/Zoom/Fit selection



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am in need of a global macro that allows me to "view/zoom/select/fit selection" selected columns. Ideally it would allow me to go to any sheet of any book, select the columns I want to autofit view, e.g., A, and click on a macro button to execute it.

    I would be surprised if such a macro hasn't been written many times over but I am not able to find such a thing. Does anyone have an idea how I would find or write such a macro?

    Thanks.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi, and welcome to the forum!

    I'm not quite sure I follow what you mean by autofit view. Are you saying that you want to automatically zoom into 200% (or something) when you select a group of cells and press the button? If so, why not use the zoom slider for that?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,
    What I wanted to do was 'auto fit select' specific columns with a single click instead of the four or five that are otherwise necessary. Excel provides lots of buttons but none for the 'fit selection' (View/zoom). In the meantime I cobbled together some snippets and actually got it to work better that I had planned.

    Attached is the code I ended up with. I'm sending it for two reasons, one to show you what I wanted and the other to share in case others find it useful.

    Pardon me if I have quoted the code properly. I don't use these forums often enough and don't know how to wrap code.

    Code:
    Sub ZoomMySheet()
    ' Created out of a need for my brother to zoom in on active columns only.
    ' This was created to perform View/Zoom/Fit column/Enger with one click
    ' of an icon on the tool bar. The number of columns to be included is specified
    ' as being the right most column with with a non-blank cell in the first row.
    ' David W. Campbell - August 1, 2011
    Set MyActiveCell = ActiveCell                                 'save current cursor location
        iLastCol = Cells(1, 256).End(xlToLeft).Column             'find right most non-blank cell in first row
        Range(Columns(1), Columns(iLastCol)).EntireColumn.Select  'select columns to be focused on
        ActiveWindow.Zoom = True                                  'zoom to fit
        MyActiveCell.Select                                       'go back to original cursor location
    End Sub
    Thanks for the reply.

    Dave

  4. #4
    Hi dcampbe1...

    independantly from the code and Ken's answer; perhaps following suggestion can also be helpful: there is a button in the ribbon "View" for zooming into a selection. Please see screenshot below. Firstly select the columns or range you want to zoom and the click on the button.

    Regards :-)
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	zoom.jpg 
Views:	11 
Size:	73.1 KB 
ID:	190  

  5. #5
    Quote Originally Posted by maninweb View Post
    Hi dcampbe1...

    independantly from the code and Ken's answer; perhaps following suggestion can also be helpful: there is a button in the ribbon "View" for zooming into a selection. Please see screenshot below. Firstly select the columns or range you want to zoom and the click on the button.

    Regards :-)
    Thanks for the help but I'm using 2003 which because it has no appropriate toolbar button icon requires four or five clicks to get to that feature.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Ah, gotcha. So you take that code, link it to a button on the toolbar and you're good to go?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Quote Originally Posted by Ken Puls View Post
    Ah, gotcha. So you take that code, link it to a button on the toolbar and you're good to go?
    YES! I'm thrilled because I don't really know enough about what I'm doing to accomplish such a feat. I spent far less time finding the two or three main ingredients and cobbling them together than I spent querying the forums. I'm really excited about accomplishing my objective.

    If you've studied or played with that routine at all, you'll note that it has a weakness of grabbing everything below the highlighted cell to the first non-blank cell. That's fine as long as the desired selection is followed by a blank. The workaround now is to add a blank line below the intended area but it'd be nice to be able, as an option, to select all of the desired cells, instead of having to insert a blank at the bottom of the intended area. I just don't know enough about VBA to distinguish between just a selected area and the are bound by a blank -- what do they call such an area anyway? I've forgotten.

    Thanks for your interest.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    LOL! You'll love this... they call it a "Selection"

    Here's the code to zoom to your selected cells:
    Code:
    Sub ZoomToSelection()
    Activewindow.Zoom = True
    End Sub
    Yeah, seriously. That's it.

    And to revert to normal:
    Code:
    Sub ClearZoom()
    Activewindow.Zoom = False
    End Sub
    To add it to a button on the toolbar in Excel 2003 and earlier, you:
    • Right click a toolbar and choose Customize
    • Go to Commands --> Macros
    • Drag the "Custom Button" to the toolbar you want your macro on
    • Right click the button and choose "Assign Macro"
    • Select the Macro and choose OK
    • Click Close
    That's pretty much it.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    What I'd really like to do is incorporate this into my original code and test for multiple lines of selection such that if only one cell is selected it would jump one way (to the original code) and if more than one cell is selected to switch to the other (the new logic). Or is that possible? -- for an expert, nothing's impossible, right?

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    How's this?

    Code:
    Sub ZoomMySheet()
    Dim MyActiveCell As Range
    ' Created out of a need for my brother to zoom in on active columns only.
    ' This was created to perform View/Zoom/Fit column/Enger with one click
    ' of an icon on the tool bar. The number of columns to be included is specified
    ' as being the right most column with with a non-blank cell in the first row.
    ' David W. Campbell - August 1, 2011
    'save current cursor location
        Set MyActiveCell = ActiveCell
    'if only one cell selected, select columns to be focussed on
        If Selection.Cells.Count = 1 Then
            With ActiveSheet
                .Range(.Columns(1), .Columns(.Cells(1, .Columns.Count).End(xlToLeft).Column)).EntireColumn.Select
            End With
        End If
    'zoom to fit and go back to original cursor location
        ActiveWindow.Zoom = True
        MyActiveCell.Select
    End Sub
    If you're going to do any amount of coding, I HIGHLY recommend that you go into the Visual Basic Editor, go to Tools-->Options-->Editor and check the "Require Variable Declaration" box. This will make all new modules show up with the line "Option Explicit" at the top, forcing you to declare your variables. You didn't have any issues in your code, but when you're beginning/mucking around, it's really important to ensure things don't go sideways. (And for exisitng modules, just type Option Explicit as the very first line of any module.)

    I've declared the variables here, shortened up your code a bit, and also added some pieces to ensure it's working on the right sheet and will be foreward compatible with Office 2007+.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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