I had a bunch of questions typed out, but decided to stop when I got to five paragraphs. I will try to keep it short(ish) and simple. My boss wants a sort of database in Excel (not Access), and I am having issues with some stuff.



I have created a macro-enabled template in Excel (2007 SP3, Win 7 64-bit), and I need it to output to two or more (up to four) worksheets based on some check boxes. Example: John Doe is available to work in the Central Area, and the West Area, so I would check "C" and "W". The output needs to go to the following sheets: "ALL", "Central", and "West". I also need it to output to another sheet, but that is a big hairy one, so I will ask a followup question later/separately.

I also need the template to stay with the rest of the workbook/file/project, as it will be used on at least one other computer. Attached are the two forms:

(The site will not allow me to upload a macro enabled form, so these are just .xlsx files)

Edit: Wrong profile file

Attached Files

	TEST Substitute Custodian On-Call List TEST.xlsx (25.3 KB, 9 views)
	employee profile test 2.xlsx (56.5 KB, 8 views)

Following questions with another: If I have a template, can it output to something other than an exact duplicate of itself?

And to clarify, I am not requesting someone do the work, just HOW I go about doing these things. Thanks a ton.

WNGetz,

I modified the sample sheet you provided.

Create a directory on called
C:\test2
and place the files there.

If you are not limited by your original design and the use of merged cells
this may work for you.

When you open the "employee profile" sheet do not enter eny data in to your original form.
Click the button labled "A Simple Method".
this will launch a User Form where you can enter employee info.

Then click the "Write Record" button.

The employee info will be written to "On-Call List2" sheet to the proper sheets.


cplmckenzie

Attached Files

	employee profile test 2.xlsm (79.3 KB, 11 views)
	On-Call List2.xlsx (23.6 KB, 9 views)

Thank you for the help. I have a question. When I try this with the On-Call list open, I get an error that states "Run Time Error "1004": Cannot access the read-only document On-Call List2.xlsx". When I use it with the on-call list not open, it writes the information, but if I use the button again, it over-writes the just updated file (well, offers to replace). Can this be done in a manner that updates the existing file, rather than replacing it (and the information I entered before)?

WMGetz,
As to the error msg. Excel is telling you that the document is already opened.
The first user to open a worksheet opens it "edit - read/write mode"

Any subsuquent users can only open the same worksheet in "read only mode".

The underlying form code can be modified to check if the sheet is already opened,
if it is then the code should skip trying trying to open it.

If there is a possibility, either intentionally or inadvertently, that this could be
already opened the code should be modified to address this case.

As to the overwriting of the same data.

This was coded as a example of how to accomplish your objective.

The same code can be used to write all data into sequential rows in the sheet.
The variable that holds the value of the row to write to need to keep state.

Meaning it needs to know the next empty row in each sheet, Central -Northeast and West.
Then assign that value to the variable.

This could be accomplished in a number of ways.
The simplest way would be to use cells in the sheet to hold that value and then test
if that value is greater than "0", if it is assign that value to the variable "rw".

At the end of the code that processes the sheets, simply save the work sheet which has the values of next empty row saved to it

cplmckenzie

Thank you very much. Working on it now.