+ Reply to Thread
Results 1 to 16 of 16

Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

  1. #1
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Exclamation Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Hi,

    Firstly new to the Forum so please bear with me.

    I have looked for a while now online and cannot find any information which relates to the area I require help with, hence my registration.

    I am an accountant looking to create a master working paper excel workbook. Once all the relevant sheets have been added I then want a front sheet that lists all the tabs BUT gives me the option to essentially tick which ones I want to keep and which ones I do not need. Basically to tailor the master excel workbook to each individual client, quickly and efficiently.

    I know its achivieable as some of the excel workbooks used by various audit working paper businesses use it.

    Any thoughts would be gratefully appreciated.

    Many Thanks.

    Biggsy2020
    Last edited by Biggsy2020; 09-15-2020 at 08:26 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Excel Question

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional.)


    You'd need VBA to do this. Would you want to hide the sheets not required, or delete them?

    You'd be best mocking up your Master/Template workbook and indicating your requirements. For example, are there any interdependencies between any of the sheets?



    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Hi,

    Many thanks for your reply.

    Ideally delete them, but hiding them would also be fine.

    Sorry to be uneducated, but what do you mean by interdependencies bewteen the sheets?

    Many Thanks.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Thanks for changing the title. The Mods will be much happier with that.

    Not uneducated . Just me asking if there are formulae linking the sheets to one another. That might cause problems if you delete any of the sheets. But, if they're all "free standing", that shouldn't be a problem.

    The choices are:
    Hide them so they can be unhidden by right clicking on a visible tab and selecting Unhide
    Hide them so they can only be unhidden through the VBE interface
    Delete the sheets you don't need (but that can't be undone)

    Doesn't make a lot of difference what you choose.

    Please post a sample workbook.

  5. #5
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Great, hiding them so they can only be unhidden through the VBE interface would be brilliant. Free standing albeit linked to the front sheet.

    I will prepare the file and upload.

    Many thanks for your help.

  6. #6
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Hi,

    Finally Attached is an example. I would like to tailor the "optimise sheet" so that I have the option to delete B1 - B12 if needs be.

    The actual workbook will be more significant.
    Attached Files Attached Files

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Wow, welcome back

    Please see the attached updated workbook


    You should really protect the VBA Project with a password so that the sheets cannot be unhidden manually.

    Enjoy
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Hi, that's amazing, thank you. Do you mind if I ask how you have done that? Are then any learning videos/ sites which will give me a better understanding?

    P.s. you mention "protect the VBA Project", how would I go about this?

    Greatly appreciated.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    You're welcome. Thanks for the rep.


    Press Alt-F11 to enter the VB Editor. You can double click on a sheet name to see the code behind it, or double click on a module. I use a Worksheet Change Event handler to monitor the changes in column C.



    To protect the VBA Project: https://www.exceltrainingvideos.com/...sing-password/

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets


  11. #11
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Excellent thank you so much!

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Again, you're welcome.

  13. #13
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Hi Sorry to bother you again.

    On the attach spreadsheet I have a few questions I would be grateful for your help with.

    On the optimise sheet, when you press show all or hide all, it puts a Y or N in rows 3,4,5,6. Is it possible to stop it doing this?

    From row 7, is it also possible to stop it doing this where there is a blank row, as an example, between B12 and C Overheads.

    Last Question, I seem to hidden the side bar in the VBA screen, how do you find this again? and following from this, there is sheet called C1
    which is showing here but not on the excel screen, can I remove this because it wont now allow me to add a sheet which is name C1, therefore I had to put a "." after the one showing on the excel screen.

    Many thanks.
    Attached Files Attached Files

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    First point:

    Change:
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    Skipping the blanks will take some more thought. I was trying to avoid a loop. I'll come back to that.

    With regard to "C1" and "C1.", add a new entry in the list for "C1" and then click on Show All. When it is visible, delete sheet "C1" and rename sheet "C1." as "C1". Now you can remove the entry in the list for "C1." Show All and Hide All should Show and Hide sheet "C1".

  15. #15
    Registered User
    Join Date
    09-15-2020
    Location
    Taunton
    MS-Off Ver
    MS Office 365
    Posts
    18

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    Excellent, thank you again for your time.

    I was just thinking, the "blank" bit might not be that much of an issue, I can just set the text to "White", then problem solved

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Front Sheet with the ability to Tailor the Excel Workbook to the required Sheets

    OK, try this. Note that I have moved a couple of headings into column B as I check that for blanks.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-14-2015, 08:25 AM
  2. [SOLVED] Mathematical question or excel question? Multiple several numbers in a row.
    By douglascaixeta in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-22-2015, 05:48 PM
  3. database question, excel data sorting question
    By weblisterltd.com in forum Excel General
    Replies: 1
    Last Post: 12-17-2014, 01:35 PM
  4. I'm an amateur Excel user with a simple question on Excel 2013-
    By cpax22 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-26-2014, 06:43 PM
  5. Replies: 2
    Last Post: 11-20-2012, 11:14 PM
  6. Replies: 6
    Last Post: 07-05-2006, 10:20 AM
  7. Replies: 3
    Last Post: 01-23-2006, 02:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1