Creating a User Form with VBA

Good Morning,
I am developing an Excel based internal authorization form and log for our organization.  The form will work as follows:
User form on one worksheet (the form will request information from the user regarding their request for approval and it will identify the person who needs to provide the approval).  Once the user clicks the submit button, we would like the requested information to be populated on to the tab designated for that particular Approver.  There will be several Approvers and each should have their own worksheet.  At the same time, we would like an approval log to be populated with all the details of the request and if possible, an e-mail to be sent to the Approver advising they have something to approve.  Once the Approver goes to their tab in the workbook and they approve the request, we would like a second e-mail to go back to the requester and the approval log to be populated with the Approver's name, date and time of approval.  The Approver's tab will locked and will require a password to access.  The Approval log will also be locked with access only by an administrator.  I already have a couple pieces of the puzzle.  I have the user form and Approvers tabs (attached)  I also have code for the e-mail function and populating the approval log (all attached).  I don't have code to populate the approvers tab and I don't have any code for the return e-mail back to the requestor.  I have been playing with this using various suggestions from online and can't seem to get it to work.  I am just starting to understand VBA, but I am the beginner level.  The tabs at the bottom of the sheet will actually have the approver's names rather than the generic labels.

Any help or guidance would be greatly appreciated.
FORM.xlsm
SabealgoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I see no reason why you need  individual sheets. The best way would be to record all data to the Log with a column to hold the approver's name. This data could then be used to populate a form that acts as a report.

I'm not exactly sure what information goes where, but this should give you an idea how it should work, post back with more information and specific questions.

The user makes a selection in the ListBox which is the destination sheet, the data is then written to that sheet.
FORM.xlsm
0
SabealgoAuthor Commented:
Good Morning Roy (NY time),  The original intent was to provide each of the Approvers a location where they could approve the request made to them.  There are three approval levels.  Certain transactions require only level 1 approval, while others require level 2 approval.  On rare occasions a level 3 approval is required.  For auditing purposes we were trying to prevent an accidental approval by a level 1 for a level 2 or 3 request.  The plan was to lock down the level 2 approval tabs so that level 1 could not access, Level 3 would be locked down so that level 1 and 2 couldn't access.  The result would be that the Log would should who and when a request was approved.

Here are my questions -
Using VBA, how can I populate both the Approval Log and the Appropriate Approver's tab?  If you think there is a alternative, given our audit issue, please let me know.

Once the Approver approves the request on his or her specific tab, the Approval Log should capture that information.

The last question is that I would like an e-mail to be sent to the approver alerting them that a request has been submitted and then another e-mail be sent back to the requester once the the request is approved.  Can somebody help me incorporate that?
0
PatHartmanCommented:
This task would be better suited to a database application such as MS Access.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup Finance ManagerCommented:
All of that is doable. I've done something very similar recently for another member. It will take a little while because I am busy at the moment

Where are these approvals to be entered? Can you edit the example to show where the levels of approval are.

Have I got it right for posting to the individual sheets, posting to the log as well will be simple.
0
Roy CoxGroup Finance ManagerCommented:
It's not clear where each TextBox writes to, i.e the columns do not seem to match.

Here's the basic code amended to write to two sheets.
FORM.xlsm
0
SabealgoAuthor Commented:
Good Morning and thank you for your time.  I attached an updated sheet that shows the fields and how they line up to the form.
FORM-Demonstrator.xlsm
0
Roy CoxGroup Finance ManagerCommented:
The UserForm depicted in the worksheet is not the same as in the VB Editor.
0
Roy CoxGroup Finance ManagerCommented:
I've tidied up the whole form and added code that posts to the Log and approver sheet. Your labels all used the ControlTip but I have removed that as it is unnecessary.

I've resized the controls and their text. I think the form now looks much tidier.

The column to write to is stored in the TextBoxes' Tag Property making it easier to code the submit button

Let me know if this works OK
FORM--2-.xlsm
1
Roy CoxGroup Finance ManagerCommented:
I put a lot of free work into this for you. A little feedback would be appropriate.
0
SabealgoAuthor Commented:
I appreciate all of you hard work, I could not have done it with out you.  I apologize for the delay as I was unable to get to a laptop over the past few weeks due to travel.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SabealgoAuthor Commented:
I am not sure how to assign the points - if I need to do something additional please advise.
0
Roy CoxGroup Finance ManagerCommented:
I think the OP actually wants to accept my solution.
0
SabealgoAuthor Commented:
I learned allot by working with Roy.  Thanks Roy.  Apologies to everybody for the delay in responding.
0
Roy CoxGroup Finance ManagerCommented:
How come the despite the OP asking for help accepting the answer and saying that

I appreciate all of you hard work, I could not have done it with out you.  I apologize for the delay as I was unable to get to a laptop over the past few weeks due to travel.

and

I learned allot by working with Roy.  Thanks Roy.  Apologies to everybody for the delay in responding.

My solution has not been accepted!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.