Create Check List to Delete Columns and Create New File

**margetc** · 07-25-2011, 07:00 PM

My problem:
I have a file (sheet 1) and I want a user to be able to create a new file from the data but which only contains the columns they choose.
More detail included in textbox on sheet 2 of attached file.

So if anyone can help me set up a macro that :
a) initially protects the original file/data in some way then
b) automatically creates a checkbox list on sheet 2 : (some of which are non-selectable eg name, reg group) then
c) asks the user to confirm they wish to save as a new file then
d) allows the user to specify the location and filename of their chosen columns and saves their file without any macros.

Thanks for any help you can give.

Also, if you think there is a better way to do this I'd be happy to hear your suggestions.

**BigBas** · 07-26-2011, 12:17 AM

Margetc,

Please see the attached file. This may only be a start, but let me know if the format is workable, and then let's try to modify together accordingly.

**margetc** · 07-26-2011, 06:50 AM

Hi
Thanks for the quick reply. However, for some reason I can't see any of your changes. The file looks no different to the original. I'm using Excel 2007 if that helps?
Thanks

**BigBas** · 07-26-2011, 11:31 AM

Hi Margetc,

A userform should pop-up as soon as the workbook is opened. Did you enable macros?

**margetc** · 07-26-2011, 05:03 PM

Hi BigBas
I have enabled macros.
I tried to download again but still no userform opened. So I looked in the Developer section and I can see Userform 1 and 2.
I've never used Userforms before so I'm not sure how to enable them. I will investigate using HELP ........... edit (5 mins later).......... I manged to run the userforms from the VBA screen and they seemed to work fine.

So if I can find out how to get the userforms to open on file-open it looks like you are on the right lines.

The next thing would be to make a few columns compulsory (eg name and Reg group).
Then (and this would only be a "nice to have" facility if it is not too much trouble) to force the user to save the file as a certain filename ( eg 7anb/M2 10/11) which is taken from the bottom of sheet 1 (in this case it is in cell A36). No doubt this would need to be changed to something like "7anbM2 2010_11" and the user could add their own suffix in case of duplicates or even overwrite with their own file name.

The reason for the 7anbM2 2010_11 format is because this refers to the class (7anbM2 and the year 2010 to 2011).

For info the cells in column A with the class and year details come in the following formats:
Marksheet Name : Maths Year 11 : 11a/M3 09/10
Marksheet Name : Maths Year 8 : 8ab/M2 09/10
Marksheet Name : Maths Y9 Reports from 2010 : 9ab/M3 10/11

(these always have "/M" so I suppose this could be used to workout the preceding space and the date can be picked up from the last five chars.

Thanks again
Chris