Results 1 to 2 of 2
  1. #1
    19Mark45 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    1

    Excel 2016 VBA to auto fit column size all shhts in a workbook

    Hello:

    I am new to this web site and hope that I have selected the right forum. If I have selected the wrong form please let me know where I should post this and I will do so.

    I have a workbook consisting of 12 worksheets and each worksheet made up of many columns. As a result I find myself constantly having to select format and then auto fit. Having to do this so often is about to drive me nuts. I am hoping that someone can help me write a VBA that will cause the columns in the entire workbook to auto fit the column size each time I enter new data.

    I should probably tell you that I am using Excel 2016 and that I am not experienced in using VBAs. About all I know is that I need to go to the developer tab and select visual basic. In my case, when I do that I get a box showing all the different worksheets and "this workbook." Further, I know that I can select any of the worksheets, but am guessing that since I want this to apply to the entire workbook that I would need to select "this workbook." Once making a selection I know that a window opens with two drop down boxes which I presume I need to make a selection from. What selections to make and how to write the VBA are beyond me. Can anyone please help me?

    Thanks for your kindness,


    Mark

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Probably would have been better to post in an Excel forum.

    Experience and knowledge of Access VBA coding is helpful but Excel coding will present new and unique challenges.

    Triggering a procedure that reformats worksheet every time a value is entered into a cell might interfere with data entry productivity.

    I once had a spreadsheet with expression in a cell to call a VBA function to calculate a value when certain cells were edited. It was a real headache just to get to the point I could understand how to do it. As best as I can recall, triggering the function call was not simple and think it involved code like in this tutorial http://www.ozgrid.com/VBA/run-macros-change.htm. I long ago totally abandoned that approach and converted it to Access and no longer have the workbook. In your case you would not have an expression in a cell to call a function so might actually be simpler.

    Can use macro recorder to help generate VBA (macros in Excel are really VBA procedures). So use macro to record the steps of setting column widths. It will likely include a line similar to example in https://msdn.microsoft.com/en-us/lib...ffice.11).aspx. Modify code as necessary for your process.

    So why are you using Excel and not Access?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form Font Size Larger in Access 2016 than 2010
    By tarzanphan60 in forum Forms
    Replies: 2
    Last Post: 10-26-2016, 04:40 AM
  2. Worksheet within Excel Workbook
    By rpkfish in forum Macros
    Replies: 4
    Last Post: 05-06-2016, 03:02 PM
  3. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  4. Link to Excel Workbook
    By Shelly9633 in forum Access
    Replies: 2
    Last Post: 01-06-2012, 10:43 AM
  5. Get column headings from closed Workbook
    By Deutz in forum Access
    Replies: 1
    Last Post: 12-10-2010, 09:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums