VBA - 3 tiered approval process with sheet data comparison

**titushanke** · 07-02-2011, 08:25 AM

Hi everyone,

I have a tougher one here... Actually a part of what I am asking I have already solved, but I am struggling with the rest and want to see if anybody out here can come with a more elegant approach.

I have an Excel workbook with three sheets:

Import
This sheet imports data from an external, tab-delimited text file (import routine is working fine). It is locked and hidden right after the import process (already in place) and only serves as a reference for the other sheets.
Adjustment
This sheet contains all that is in the import sheet, to be copied through VBA (paste values). An individual will be able to conduct two actions here:
- approve or disapprove each row
- add rows of data in the section identified
Approval
Another individual will approve what was done by the person adjusting the adjustment sheet. Scope is that the approval sheet highlights all the changes made by the individual that worked on the adjustment sheet.
It also needs to consolidate them as shown in the example file (separately list the disapproved data).

Some notes:

The person working on the adjustment sheet shouldn't be able to work on any other sheet. This could be solved by requesting a password in the approval sheet when clicking on it (to be seen, I'll figure out something).

The length of the import list can vary from 10-100 rows.

Attached is a sample of the data.

I would appreciate if anybody could help me with this, it's not very complicated, I just am not so good with VB and found some snippets, that I didn't manage to adjust to my needs though. Anybody up for the challenge on a Saturday? ;-)

Thanks so much!

Titus

**docMed** · 07-03-2011, 12:02 AM

you can restrict access to sheets using various methods... and it all depends how many users you have per group, and how they access it..

For example;

Lets say only 1 person can see the adjustment list...

This person could be identified via a windows login name; such as "msmith" and you could use the command

Private Sub Workbook_open()
If lcase(Excel.Application.UserName) = "msmith" Then
'show the sheet
else
'hide the sheet
End Sub

Otherwise, if you want to simply have a password, you can have vba prompt for a password, BUT *** there is a big issue here.. as anyone can end vba code by pressing ctrl + pause/break, and easily circumvent the protection you have entered.

**DonkeyOte** · 07-03-2011, 02:53 AM

Originally Posted by docMed

...This person could be identified via a windows login name; such as "msmith" and you could use the command

Private Sub Workbook_open()
If lcase(Excel.Application.UserName) = "msmith" Then
'show the sheet
else
'hide the sheet
End Sub

Commenting only regards the above - to use NT Login credentials use:

Environ("username")

Application Username is set simply via Excel Options and as such can be modified at any time.

If you opt to use VBA I would suggest investigating the xlSheetVeryHidden property (implemented via an Open event perhaps?) ... see: http://www.cpearson.com/excel/EnableMacros.aspx

Password security in Excel is relatively weak.

**titushanke** · 07-03-2011, 04:37 AM

Hi everyone and thanks for your replies. I can see the authentication of the third (approval) sheet being a little tougher. Maybe it will make sense to work this in a separate workbook, I'll have to see.

Question: could anybody help me with the macro building to copy one sheet's data to the other? Maybe I didn't explain this requirement clear enough in my initial post.

What I want to achieve is:

1.) IMPORT contains a raw list of data, imported from another application.
2.) ADJUSTMENT allows an individual to import the data from the IMPORT sheet, invalidate, validate and add data sets and submit for approval once done.
3.) APPROVAL should visualize the three data options (group valid and added together), separately list invalidated options). He should be able to override any valid, invalid setting initially suggested by the person working on the adjustments sheet.
The approver should then click a SUBMIT button which will create a new excel sheet with JUST the VALID and ADDED data as per his approval.

How can I achieve this with VBA? I am thinking of using various macro buttons in the process:

ADJUSTMENTS: the user clicks on "import data", the sheet automatically populates with the raw data from the IMPORT sheet.
He then validates the data (with the Y/N option) and adds data sets if needed.
He then clicks a "submit for approval" button.

APPROVAL: the user that opens the file will see that there is data that has been submitted for approval. Things the approver will need to have visualized:

1.) Differences between import and adjustment data: color highlighting of added data (similar to conditional formatting but with VB)
2.) Invalid rows separately

The approver than clicks a SUBMIT button and has a new excel file created.

Does this make sense? It's lengthy to explain, but not actually that difficult to execute. Forget about the rights management right now, just consider the process:

IMPORTING (copy paste from IMPORT sheet to ADJUSTMENT sheet)
ADJUSTING (and submitting the adjusted data for approval)
APPROVAL (amending and submitting to a new excel file).

Anybody up for this? ;-)))

Thanks so much for your time!!
Titus

**snb** · 07-03-2011, 07:01 AM

Nice project !

What's your budget ?

**titushanke** · 07-03-2011, 01:47 PM

lol, think I would be asking for help here if I had one?

It's really not as complicated. I just need a script to copy data from sheet 2 to sheet 3 with a sorting function....