Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-10-2018, 06:04 AM   #1
ScottXe
Newly Registered User
 
Join Date: Jul 2012
Posts: 120
Thanks: 8
Thanked 0 Times in 0 Posts
ScottXe is on a distinguished road
Approval column

I would like to create an approval column in a spreadsheet that contains holiday applications. My initial idea is to have a locked column with password protected. However, it has more than one person to approve for different sections. Any idea to accomplish this requirement? Thanks!

ScottXe is offline   Reply With Quote
Old 01-10-2018, 08:21 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,992
Thanks: 54
Thanked 1,923 Times in 1,837 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Approval column

You can have a Userform pop up with list of signatorys. When a peson double ckick his name it prompt for his password. If the password is correct the approved column is automatically filled.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-10-2018, 03:58 PM   #3
ScottXe
Newly Registered User
 
Join Date: Jul 2012
Posts: 120
Thanks: 8
Thanked 0 Times in 0 Posts
ScottXe is on a distinguished road
Re: Approval column

Great idea! Does it require programming? If not, I appreciate how to set up the pop-up User form. Tks!

ScottXe is offline   Reply With Quote
Old 01-11-2018, 03:06 AM   #4
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,992
Thanks: 54
Thanked 1,923 Times in 1,837 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Approval column

you can place code in your excel file
but you need to save it as
macro enabled excel file (.xlsm).

this is simple sample. i did not
use Userform, but would be nice
to have one.
see the code behind the excel file.
Attached Files
File Type: zip Book1.zip (16.1 KB, 16 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-11-2018, 05:20 AM   #5
ScottXe
Newly Registered User
 
Join Date: Jul 2012
Posts: 120
Thanks: 8
Thanked 0 Times in 0 Posts
ScottXe is on a distinguished road
Re: Approval column

I open the file and see your design - perfect but I may not need Admin approval and just supervisor approval and disapproval - the application on one line only. However I could not see the operation - no password! I have no idea how to copy it into my spreadsheet and necessary configuration. Can you give me the instructions to follow.
ScottXe is offline   Reply With Quote
Old 01-11-2018, 06:02 AM   #6
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,992
Thanks: 54
Thanked 1,923 Times in 1,837 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Approval column

here i modified it so only
supervisor range you supply.
there is instruction in the code
and i think it is very straight forward.
you only need to substitute the values
of the CONSTANT (CONST).

go to VBA (alt-F11).
on the Left pane, double-click on ThisWorkbook.
insert the code there.

you also need to make the code
safe so no one can see your code.
on VBA go to Tools on the menu.
then VBAProject Properties...
Protection tab, tick Lock project from viewing.
set the password.
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    Const SUPERVISOR_RANGE As String = "E1:H6"
    Const ACTIVE_SHEET As String = "Sheet1"
    Const PASSWORD As String = "arnel"
    Const RANGE_TO_KICK_USER As String = "D1"
    
    ' SUPERVISOR_RANGE  =   the rectangular area where
    '                       approval/disapproval is located. include
    '                       the range where there is a checkbox.
    ' ACTIVE_SHEET      =   the sheet where the application for leave
    '                       is located (my sample SHEET1)
    ' PASSWORD          =   the static password needed so
    '                       user can "enter" the SUPERVISOR_RANGE
    ' RANGE_TO_KICK_USER    when password is invalid you
    '                       don't want the user to enter
    '                       that range, instead put him
    '                       on another range.
    
    Static bolInSupervisor As Boolean
    
    Dim rng1 As Range
    Dim bolCellInSupervisor As Boolean
    Dim strPass As String
    
    On Error Resume Next
    
    If ActiveSheet.Name = ACTIVE_SHEET Then
        bolCellInSupervisor = False
        
        Set rng1 = Intersect(Range(SUPERVISOR_RANGE), Target)
        bolCellInSupervisor = (Not rng1 Is Nothing)
        Err.Clear
        
        If bolCellInSupervisor = True Then
            If Not bolInSupervisor Then
                strPass = InputBox("Please enter Supervisor password", "Password Required") & ""
                bolInSupervisor = (strPass = PASSWORD)
                If Not bolInSupervisor Then
                    Range(RANGE_TO_KICK_USER).Select
                End If
            End If
        End If
    End If
End Sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-12-2018, 08:12 AM   #7
ScottXe
Newly Registered User
 
Join Date: Jul 2012
Posts: 120
Thanks: 8
Thanked 0 Times in 0 Posts
ScottXe is on a distinguished road
Re: Approval column

It looks beyond my capability! However, I love to try it during this weekend to see what I can learn from you. Many thanks for your advice!


ScottXe is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Denied / Approval ID wdhodges Macros 1 12-05-2014 12:35 PM
Wating approval Eagle Creek Site Suggestions 3 05-29-2007 05:32 AM
Email approval request with link to approval form oihjk General 1 02-06-2004 05:39 AM




All times are GMT -8. The time now is 09:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World