Niclasfa
07-21-2015, 03:54 AM
I have a task to do that I am having trouble with.
Apart from being super busy with websites.
I am also required to create a VBA macro for a word template that needs to work with an excel template as well.
I was unable to attach the templates and the work I have done so far so I just saved the templates as documents and uploaded them.
You will see I have already started the UserForm but cannot program the functions.
The excel document is nearly done as well but I need help finishing it off.
these are the macro requirements for Word:
mcrOK: to populate the bookmarks within the letter text in the body of the template. The Macro will be attached to the UserForm button
mcrLog: to append person data to text file Latelog.txt and hide the UserForm. The macro will be attached to UserForm button with caption Log
mcrClear: To clear the UserForm Fields. The Macro will be attached to the UserForm button with caption Clear.
mcrClose: to close UserForm. The Macro will be attached to the UserForm button with caption Close.
these are the macro requirements for Excel:
mcrImport: Import eight state/territory workbooks each into their own separate sheets in template National. Macro should accomplish eight imports using looping. Macro will be attached to the button with caption Import on summary sheet.
mcrCombine: Combine eight new sheets into one new sheet called Combined. Data should begin from row 2 in sheet combined to leave room for slumn headings. After combining the separate eight sheets, macro should delete the eight separate state/territory sheets. Macro will be attached to button caption Combine on Summary sheet.
mcrValidate: Perform validate and summarize tasks according to details below. Macro will be attached to button with caption Validate on summary sheet.
Validate and summarise details
The following tasks (not listed in any particular order) are to be accomplished with mcrValidate:
In row 1 of sheet Combined write the following column headings: Last name, First name, Gender, DOB, Address, Suburb, State, Postcode, Role, Category.
There was a variety of fonts in the eight individual states sheets which will be carried into sheet Combined during the import. Standardise all fonts to plain Arial 12 pt.
The column Category in sheet Combined was not in the original states data. Its purpose is to record the age category for each member. The various categories from Junior to Great Grand Master are shown in the table below. For each row of member data, generate the age category for columnCategory. Hint: Firstly, for each row subtract DOB from your Nationals date (see Summary sheet) to find the member's expected age on the day of competition, then compare with the category table below. Write out the category data for the member in full - for example if a person is 50 years old write Grand Master.
Category
Age
Junior
Under 18
Senior
18+
Master
40+
Grand Master
50+
Great Grand Master
60+
Calculate summary numbers and write them in the correct cells of the table in sheet Summary.
Apart from being super busy with websites.
I am also required to create a VBA macro for a word template that needs to work with an excel template as well.
I was unable to attach the templates and the work I have done so far so I just saved the templates as documents and uploaded them.
You will see I have already started the UserForm but cannot program the functions.
The excel document is nearly done as well but I need help finishing it off.
these are the macro requirements for Word:
mcrOK: to populate the bookmarks within the letter text in the body of the template. The Macro will be attached to the UserForm button
mcrLog: to append person data to text file Latelog.txt and hide the UserForm. The macro will be attached to UserForm button with caption Log
mcrClear: To clear the UserForm Fields. The Macro will be attached to the UserForm button with caption Clear.
mcrClose: to close UserForm. The Macro will be attached to the UserForm button with caption Close.
these are the macro requirements for Excel:
mcrImport: Import eight state/territory workbooks each into their own separate sheets in template National. Macro should accomplish eight imports using looping. Macro will be attached to the button with caption Import on summary sheet.
mcrCombine: Combine eight new sheets into one new sheet called Combined. Data should begin from row 2 in sheet combined to leave room for slumn headings. After combining the separate eight sheets, macro should delete the eight separate state/territory sheets. Macro will be attached to button caption Combine on Summary sheet.
mcrValidate: Perform validate and summarize tasks according to details below. Macro will be attached to button with caption Validate on summary sheet.
Validate and summarise details
The following tasks (not listed in any particular order) are to be accomplished with mcrValidate:
In row 1 of sheet Combined write the following column headings: Last name, First name, Gender, DOB, Address, Suburb, State, Postcode, Role, Category.
There was a variety of fonts in the eight individual states sheets which will be carried into sheet Combined during the import. Standardise all fonts to plain Arial 12 pt.
The column Category in sheet Combined was not in the original states data. Its purpose is to record the age category for each member. The various categories from Junior to Great Grand Master are shown in the table below. For each row of member data, generate the age category for columnCategory. Hint: Firstly, for each row subtract DOB from your Nationals date (see Summary sheet) to find the member's expected age on the day of competition, then compare with the category table below. Write out the category data for the member in full - for example if a person is 50 years old write Grand Master.
Category
Age
Junior
Under 18
Senior
18+
Master
40+
Grand Master
50+
Great Grand Master
60+
Calculate summary numbers and write them in the correct cells of the table in sheet Summary.