UserForm populating Multiple Sheets

[Toonies]

Hi and thanks for looking at my post.

This is my 1st real attempt at putting together VBA coding so its may be wrong.

Ok here goes

I have made a UserForm that lets me populate a sheet based upon the

1. Name
2. Date
3. Vacation Hours

all chosen from ComboBoxes(couldn't quite do it with TextBoxes)

It works fine on each individual sheet from seperate buttons & UserForms.

What I'm looking to do is to do it all with 1 Userform for all the sheets.

The sheets are named after each month so there will be 12

heres a copy of the file I have only put 2 sheets in to hopefully make it easier

Book abc.xls

the VBA code is long so could it be shortened (I said I was New)

I look forward to any help or replies

Cheers

Toonies

[incjourn]

Try using the Me.Controls method to assign and get values from the userform e.g.

Please Login or Register  to view this content.

You will also need the 'Application.Worksheets'

Please Login or Register  to view this content.

Hope that helps.

[Toonies]

I will check it out when I finish work. Many thanks Toonies

[Toonies]

I've tried to get it to work but no luck as it does not populate sheets

[Toonies]

Bump. Anyone

[royUK]

The code could be shortened considerably.

Do you intend using the form with the sheet being used active?

If you do then use ActiveSheet. If not you need to have a combobox to select the month from, then use, assuming the months are listed in a ComboBox called cboMonths

Please Login or Register  to view this content.

[Toonies]

yes Roy I would prefer active, but not quite sure on how to do it that way.

Thanks Tom

[Toonies]

I'm open to any help or ideas

Many thanks

Toonies

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Toonies]

Hi thanks for the advice, I will explain it again

I am looking to create a single Userform to populate 12 spreadsheets within the same workbook

January to December depending on the following

1. Name
2. Date
3. Vacation Hours

I do have a UserForm (shown in the attachment) that will only populate 1 sheet, but I am unable to change the VBA code to work on all sheets from the one UserForm.

The enclosed example has only 2 sheets to possibly make it easier to workout a solution if there is one.

Book abc.v1.xls

The VBA code is a very long as its my first real attempt, it may be possible to reduce the size however at present I do not have the knowledge to do that.

I have used part of the suggested coding by "incjourn",

Please Login or Register  to view this content.

(however couldn't get the full code to work)

Cheers

Toonies

[royUK]

I've added a ComboBox to select the month & amended the code to write to that month

[Toonies]

Many thanks for your help.

One slight problem, when I run the amended code I get the following

"Run-Time error code 13"
type mismatch

on this part of the code

Please Login or Register  to view this content.

Cheers
Toonies

[royUK]

Try this, there was a typo

Please Login or Register  to view this content.

[Toonies]

Hi Roy,

that got rid of the Run-time Error

however

the amended code only populates January ws?

Many thanks for the help

Cheers Toonies

[royUK]

Read your code throught & see what is happening. The code will populate whatever sheet is selected in the ComboBox, if the sheet exists. If you look through your code for the input you re-set the ws variable to January.

Your code should be

Please Login or Register  to view this content.

All these lines will fail, there is no Column reference in them

Please Login or Register  to view this content.