Dialog Box to Select Sheets to Make Visible and Delete the Remaining Sheets

[TEAM]

I have about 10 different workbooks, each with about 20 worksheets. Each workbook will be sent to different groups of about 4 people. Follow me so far??

Out of these groups of 4 people, each person will use only 5 of the sheets. Rather than determine which 5 sheets are important to each user and make 40 user-specific workbooks, I would like to make 10 generic workbooks and allow the user to select the sheets that are important.


EXAMPLE

I am one of the 4 people in a group that receives a workbook. I open my workbook and see that it contains 20 sheets. Shoot! That is a waste of time because I only need sheets 1 to 5 (always).

NEEDED SOLUTION

Because the users are not usually very computer literate, I would like to setup a control button that, when clicked, displays a listing of all 20 sheets. From that list, the user selects the important 4, 5, 6 etc. sheets and the rest are permanently deleted.


Please, please help me!!

[sowetoddid]

The Code below will open a prompt for the user to select the sheets that are important, and the remaining sheets are permanently deleted.

Please Login or Register  to view this content.

[sowetoddid]

I modified the "Displaying a Menu of Worksheets to Print" code found on J-walk's webpage.

j-walk.com/ss/excel/tips/tip48.htm

[TEAM]

This is great, but I have too many sheets!!!

The window opens and displays a list of all sheets, but the ones at the end are cutoff at the bottom of the screen. Is there a command to add a scroll bar to this or to break the sheets into a wider window with two columns??


<THANK YOU>

[sowetoddid]

Someone else will have to help you with that one, Team.

[TEAM]

I have read that a scroll bar is not always a good way of doing it. Does anyone have a command line to split the checkboxes into two columns??


Thanks!

[TEAM]

Okay, I found this code posted in another forum by Bob Phillips! This one makes a reference to

Const nPerColumn As Long = 35 'number of items per* column


Can this be applied in anyway to my code. Below is the code that I am currently using to display a listing of all sheets in the book. My total sheet count is about 50....slightly too long and runs off the screen.

Please Login or Register  to view this content.

[TEAM]

Okay, I changed this code to do everything that I want. It now deletes all sheets that are NOT selected in the dialog box. However, I have a "Menu" sheet at the start of my workbook. Is there a way to remove this from the dialog box? I tried changing "i = 1" to "i = 2", but that ended up removing the last sheet in the workbook. !~Confusing~

Please Login or Register  to view this content.

[bhofsetz]

You can resize the dialog box and listbox to fit more sheets without the scroll bar.
in the following line of code

.ListBoxes.Add 78, 40, 148, 120

change the 120 to 360 (or whatever suits your needs) and this will give you a taller listbox

and in this code

With .DialogFrame
.Height = 160
.Width = 260
.Caption = kCaption
End With

change the 160 to 380 (or whatever shits your needs) and this will give you a taller dialog box as well.

Do you want to EXCLUDE or INCLUDE the Menu sheet for your users?

Hope this helps

[TEAM]

I would like to exclude the MENU page from the dialog box.

And...I read your comments related to making the dialog box larger. Without the scroll bar, it is pretty large already (it goes off the bottom of the page). What I was really trying to do was divide it into two columns. But, I resorted to the scroll bar and ran into the problem of not being able to exclud the MENU page.


Thanks for your help.


TEAM

[TEAM]

Is there a command to strictly EXCLUDE certain sheets from this function? I do not want the menu sheet to appear in the dialog box.

Thank you.

[bhofsetz]

I don't know of a specific command that would do this but you could put an if statement before your code that fills the list box which would keep the menu sheet from being added to the listbox.

[TEAM]

I tried to stick a Worksheet("SkippedSheet").Skip command in the code, and that did not work.

The only reason why I am looking for a solution other than an "IF" statement is that I am not really sure how to change this code to do what you recommend.

[bhofsetz]

TEAM,
Add the blue lines to your code where the list is populated. This will skip over any sheets named "Menu" (or whatever you choose to put in the quotes). If you want to exclude more than just one sheet a select case statement may work better but for just one or two sheets to exclude this will work just fine.

Please Login or Register  to view this content.

Hope this helps.

[TEAM]

No errors or anything, but no success removing the "Menu" sheet either.

[TEAM]

It worked!! It worked!!

I didn't quite understand all of the code, but I think that the "letters" code preceding the If Not - Then statement caused it to be case sensitive. At least that is my uniformed logic. Anyway, my "Menu" sheet is all caps. When I changed it to "MENU", it worked!!


Thank you very much!

[bhofsetz]

TEAM,
I'm not sure why it isn't working for you. What OS and version of Excel are you using?

Is your worksheet named " Menu " on the sheet tab exactly. Since the code is looking for an exact match. In the VBA editor Project Explorer window it should look like:

-Sheet## (Menu)

with ## representing whatever worksheet you have designated as Menu

I've tested it with several sheets named Menu. The first, last and several randomly in the workbook and always have had success.

??