Resolved >>> Creating new sht from template sht & filling in summary sht - userform

I was wondering if any of you can help me.

I have some experience with excel, but until now have not ventured into VBA and macros.

I have a workbook which will have the following sheets:

1.Absence Summary sheet - Summarises data from each employee's individual sheet.

2. Template Sheet - A sheet formatted as an absence record sheet, but without data.

3. Individual employee Absence record sheets - Based on the Template sheet.

I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.

My Aim:

UserForm containing -
"Surname" Text box;
"Initial" Text box;
"Employee Number" Text box;
"Start Date" Text box;
"Job Title" Combo Box, which offers 11 different Job Titles;
"Create Absence Sheet" Command Button (will explain aim of this in a mo);
"Clear Form" Command Button (in case of user error);
"Cancel" Command Button to allow user to exit user form.

I have programmed all of the contents of my user form so far using a tutorial I found on one of these pages and modifying it to my requirements.

I have also found some code to put in the Template sheet so that when the sheet is copied, it is renamed according to the name entered in cell E3, which I am pleased with.

What I am struggling with is what code to use with the "Create Absence Sheet" Command Button.

What I want to happen when the user clicks the "Create Absence Sheet" Command Button is:

1.Make a copy of the Template Sheet
2.Put the Surname into cell E3
3.Put the Initial into cell R3
4.Put the ERN (Employee Number) into cell AC3
5.Put the Job Title into cell E4
6.Put the Start Date into cell AC4
7.Rename the sheet (preferably using "Surname, Initial." format but can live with the result of the code I found).
8.Put the Surname into the "All Employees - Absence Summary" worksheet in the next available row of column B
9.Put the Initial into the into the "All Employees - Absence Summary" worksheet in the next available row of column C
10.Put the ERN into the "All Employees - Absence Summary" worksheet in the next available row of column D
11.Put the Start Date into the "All Employees - Absence Summary" worksheet in the next available row of column E

I can't attach my workbook for you to see, because the file size is larger than 100k. I appreciate that I am asking a lot, but even if you can just give me some pointers, I would be so grateful.

Your labels for textboxes, command buttons, etc may differ, but otherwise this should work:

Please Login or Register  to view this content.

Being new to all of this, it will take me a while to set up and use your code, but I just wanted to acknowledge your post and say thanks for replying. Thank you so much for writing a code!

Anytime. If you need any help putting it together just post your code and I'll give it a shot. Of course, I encourage you to understand the code yourself, but if you get stuck don't be shy. Best wishes.

Ok, having looked at the code, and adding in the extra bits, I now have to insert it in the spreadsheet. The code I have used for the form is:

Please Login or Register  to view this content.

I read the help sheet at

HTML Code: 

and therefore tried to assign a macro to a module using

Please Login or Register  to view this content.

When I try to run the macro, by clicking the button, I get the error message:

Run-time Error '424':

Object Required.

Can you tell me where I am going wrong, please? Sorry to be such a novice at this!

I have resolved the run-time error issue, now.

However, I have noticed that my Combo box is empty.

The code I was given said

Please Login or Register  to view this content.

But what I end up with is a box on the form that I can type anything in, and the drop down Icon reveals an empty box.

Also, is there any way to make the cursor go to each box in order when you press TAB? At the moment, pressing tab seems to take the cursor to the different boxes in a random order.

Many thanks - I feel like I am learning so much even just from reading the code you provided. It is all starting to make sense.

For tabbing change the "TabIndex" of each textbox/combobox/command buttom in "properties" starting with zero as the first one.

While in properties, make sure your combobox's name is "cboJobTitle" as that's likely the reason why it's not being populated.

Finally, you have txbSurname in some places and tbxSurname in others. This applies for your other textboxes as well (i.e. tbxInitial & txbInitial) so choose which one you want to use and change all the others to match. That was probably due to my using different syntax, but you'll run into problems with this if it's not corrected.

Thank you for all your help. Tabs are working perfectly now. I have also corrected my mistakes re: tbx & txb.

Have checked my combo box name is "cboJobTitle", and that all the code is matched for this. But the list is still empty. Any ideas?

Can you attach a spreadsheet with your userform and code? You can clear the data from all the worksheets to make the file small enough.

Ok, here is the spreadsheet skeleton. The Macro for the Userform is on the Sheet "New Starter Set Up".

The Code is:

Please Login or Register  to view this content.

Everything seems to work perfectly except the combo box.

Thank you for your time.

Taking another look, try changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Of course I'm unable to view your worksheet as I don't have Excel 2007 and am not in a position to download the addin to view/edit 2007 files, but I'm hoping this solves the issue.

Works like a dream. Thank you.