Creating a User Form with VBA

Sabealgo
Sabealgo used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
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

Author

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?
Distinguished Expert 2017

Commented:
This task would be better suited to a database application such as MS Access.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
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.
Roy CoxGroup Finance Manager

Commented:
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

Author

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
Roy CoxGroup Finance Manager

Commented:
The UserForm depicted in the worksheet is not the same as in the VB Editor.
Roy CoxGroup Finance Manager

Commented:
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
Roy CoxGroup Finance Manager

Commented:
I put a lot of free work into this for you. A little feedback would be appropriate.
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.

Author

Commented:
I am not sure how to assign the points - if I need to do something additional please advise.
Roy CoxGroup Finance Manager

Commented:
I think the OP actually wants to accept my solution.

Author

Commented:
I learned allot by working with Roy.  Thanks Roy.  Apologies to everybody for the delay in responding.
Roy CoxGroup Finance Manager

Commented:
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!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial