Use of a Userform to Create copies of hidden Excel sheets

Excel gods,
Once again I come in supplication with help on what appears to be a rather simple macro/procedure I want my workbook to perform.

Scenario:
I have a workbook with 6 hidden sheets (Sheet4, Sheet5, Sheet6, Sheet7, Sheet8, Sheet10).

I also have a Userform I created called UserForm1 userformimage.jpg

In the Main Sheet (Sheet3) I have several shapes (all Rectangles) that I will be attaching this required Macro too.

Actions:
I want a macro that activates when the user clicks on the rectangle that does the following:

A) Pulls up the Userform1 (see image above)

B) if the User selects the first Command Button (CommandButton1 that has the label "Port Arrival Report"), a Message/question Box Pops up saying "Are you sure you want to create a Port Arrival Report(CommandButton1 label)? Y/N"
if the user selects "YES", then a copy of hidden Sheet4 is created and the name of the newly created copy of Sheet4 is named based on the values "Arrival", the value in cell AF21 in Sheet3 & the current date.
(e.g. if Cell AF21 has the value "Port5_Discharge" and the macro was run on the date June 20, 2015, then the name of the copied sheet would be "Arrival _Port5_Discharge_Jun20").

Note: if the new sheet created already has a sheet with the same name, in (B) above, then add an increasing number to the end of the name (e.g. the copied sheet would be "Arrival _Port5_Discharge_Jun20" or "Arrival _Port5_Discharge_Jun20(2)"..etc . Also please note that the value in AF21 is reference from another cell

C) Once the new sheet is created, a pop up saying " The "Port Arrival Report" "Name of new worksheet" has been created"

The hidden Sheet4 will remain hidden but the newly created worksheet will be visible.




Now I would love for the macro to carry out the exact same actions for the remaining 5 Command buttons in the Userform1;

i) CommandButton2 "Port Departure Report" will generate copy of hidden Sheet6. Worksheet name based on same rules as above with the name of the copied sheet would be "Depart _Port5_Discharge_Jun20").

ii) CommandButton3 "Port Progress Report" will generate copy of hidden Sheet5. Worksheet name based on same rules as above with the name of the copied sheet would be "Progress _Port5_Discharge_Jun20").

iii) CommandButton4 "Noon Report" will generate copy of hidden Sheet7. Worksheet name based on same rules as above with the name of the copied sheet would be "Noon _Port5_Discharge_Jun20").

iv) CommandButton5 "Port Evaluation" will generate copy of hidden Sheet8. Worksheet name based on same rules as above with the name of the copied sheet would be "PE _Port5_Discharge_Jun20").

v) CommandButton6 "Bunker Evaluation" will generate copy of hidden Sheet10. Worksheet name based on same rules as above with the name of the copied sheet would be "Bunker _Port5_Discharge_Jun20").

PLS note that the second Message/question Box Pops up saying "Are you sure you want to create a (CommandButton label name)? Y/N"

Thanks in advance so much for your help!!!

Hi Harry,

I'm working on your problem.

Some of the Sheet names exceed the Excel 31 Character limitation when there is a duplicate. For example, 'Arrival_Port5_Discharge_Jul31(2)' is 32 characters long and 'Progress_Port5_Discharge_Jul31(2)' is 33 characters long.

Lewis

Originally Posted by LJMetzger

Hi Harry,

I'm working on your problem.

Some of the Sheet names exceed the Excel 31 Character limitation when there is a duplicate. For example, 'Arrival_Port5_Discharge_Jul31(2)' is 32 characters long and 'Progress_Port5_Discharge_Jul31(2)' is 33 characters long.

Lewis

Thanks Lewis. Perhaps another way around that 31 character limitation is to use only the first 4 letters of the first word, in this case instead of 'Arrival_Port5_Discharge_Jul31(2)' it can be "Arri__Port5_Discharge_Jul31(2)"

Thanks!!!

Try the attached file which contains the following new code. I piggybacked the file on top of the file I did last month, because I was able to reuse some of that code.

ThisWorkbook Module:

Please Login or Register  to view this content.

UserForm1 Module:

Please Login or Register  to view this content.

Ordinary Code Module ModFormSheetGeneration:

Please Login or Register  to view this content.

Lewis

Thanks LJMetzger,
I have adopted the codes you graciously provided into my worksheet but upon the opening of the form I get the below error when i click the first (Port Arrival) commandbutton...

Sub CreateCloneSheet_error1.jpg
from the Sub CreateCloneSheet macro found in the ModFormSheetGeneration Module

I've tried looking for any differences in code in both workbook cant find any...pls help )

It's my fault Harry for not explaining better. There are some utility routines I reused from your previous post, that I included in the download file, but were not included in the cut and paste code above.

Make sure you backup your file.
Please make sure that none of the following routines are duplicated in your file or Excel may lock up when you attempt to open the file after saving.

The following routines should be in module ModCloneSheet2 in the download file in post #4 in this thread. You can get them from the file or cut and paste from below:

Please Login or Register  to view this content.

Please let me know if you have any more problems.

IT WORKS!!!!! My mistake in not properly going through all the code. I am currently testing for any bugs but so far it is great.

I do have one more minor mod if you can pls assist with this.

The above is all triggered by the "Sub DisplayUserForm1()" attahed to the rectangle and the part of the name of the newly created form is from cell "AF21"

Now I have several of these rectangles each naming the new forms generated from reading from specific cells like above e.g. Rectangle2 is reading from cell "AF22", Rectangle3 is reading from "AF24" and so on and on...


I was looking at the "Sub CreateCloneSheet" macro and wondered if we can modify it to accommodate the above ...???

Thanks

Hi Harry,

I tried to do what you asked for. I hope I understood your request. See the attached file and the code excerpts below. Items in red in the code excerpts highlight major points.

I made wholesale changes to the existing code in module ModFormSheetGeneration and added UserForm0 and UserForm2. There were also small changes to the UserForm code.

You should be able to add to the framework I created to customize to your needs. Everything could have been done in one UserForm, but the method I chose is probably easier for you to follow and maintain.

The UserForms are as follows:
a. UserForm0 (optional) opens the other UserForms as the User requires.
b. UserForm1 is for 'AF21'.
c. UserForm2 is for 'AF22'
d. You can add other UserForms as you require.

UserForm1 code excerpts:

Please Login or Register  to view this content.

Excerpts from module ModFormSheetGeneration:

Please Login or Register  to view this content.

Lewis

Lewis,
Truly, thanks for the speed of response. Its truly amazing to me. I am just now looking at what you sent but from a quick glance (and correct me if I am wrong), it seems I will need a userform for each rectangle I have, right?
The thing is I have about 55 of these and now I am worried about the volume of code (for easy management) as well as the size of the final file

I am thinking I would like to stick to just one rectangle, but instead of just reading from cell "AF21", the user is given a range to select a name from "AF21 to AF71"

So Basically after selecting the form we want to generate from the Userform, we include an step that gives the user a range (AF21:AF71) to select the port name they want

I think this will be easier &cleaner than having 55 userforms. I'll use just one rectangle and create all the sheets Needed.

I'll give the above solution a whirl in the meantime! you are a God-sent and sorry for all changes

Harry

Harry,

Since you have 55 different sets of values, my approach with multiple UserForms is not optimum.

I don't completely understand your data structure, but the best way to approach the problem is probably:
a. User Selects either one of 55 items from a list in a UserForm (or from a ListBox or ComboBox on the Spreadsheet) or atttempts to move to cell AF21 thru AF71.
b. UserForm automatically puts up the choices associated with that cell, or Rectangles in the SpreadSheet change the text in them to reflect the choices associated with that cell.

In order to implement this, I would need to see a sample workbook, with 2 or 3 items from column 'AF' and the associated choices that go with those items. I would need to know what has to be done when each rectangle (or rectangle equivalent in a UserForm) is selected, if it is different from what you don in AF21.

Lewis,
Thanks Again. Your OPTION A is pretty much what is needed. And to further Illustrate I have attached 2 graphics below...The First Illustrates how the code works now (Which is perfect) in Steps 1,2 and 3 (in RED CIRCLES).

The Second Illustrates the small Modification required (perhaps using a useform + list box)

------See below (Current Process) -------
SampleWorkProcess_new form pg 1.jpg






------------(Modified Process) ----------
SampleWorkProcess_new form pg 2.jpg



Hope this helps )

Hi Harry,

The attached file (based on version 3 [post #4], not version 4 [post #8]) should take care of what you want. The design changes were minimal. I added a Forms ComboBox (Drop Down) to the spreadsheet, and added a ComboBox to the UserForm. Both used AF21 thru AF71 to obtain the possible values.

In the spreadsheet, the Forms ComboBox formatting is elementary at best. An 'Active X' ComboBox could be used in the spreadsheet instead to make the formatting more robust, however, 'Active X' controls tend to be unstable and can cause problems which appear to be file corruptions. My personal preference is to not use a ComboBox on the spreadsheet, and to use the UserForm exclusively.

To implement the code in your file:
a. Export the 2 ordinary code modules and the 1 UserForm module from my file.
b. Import module ModCloneSheet2 if it doesn't exist (no changes made).
c. Replace module ModFormSheetGeneration (e.g. Remove, then Import).
d. Replace the UserForm1 module.
e. Cut and paste the ThisWorkbook module code into your file if it doesn't already exist.
f. Cut and past the Sheet1 module code if the code from your initial request (Post #1) is needed.


Major code changes are highlighted below in red, so you can see what was done.

UserForm1 code module excerpts:

Please Login or Register  to view this content.

Ordinary code module ModFormSheetGeneration excerpts:

Please Login or Register  to view this content.

Lewis

It works BEAUTIFULLY!!!!! Omg Thanks....I cannot thank you enough for your help!!!!