Verifying users

Topic

New Reply

I am wanting to give access to specific files by verifying the users ID, probably from AD, but haven’t a clue how to go about this.

HELP!!

Reply | Quote

Replies

Phil,

Can’t help you with Acvtive Directory but on a local machine you would capture the UserID as follows:

Code:

zUserName = Environ("USERNAME")

Once captured you would then compare it against an array containing all valid users.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG hi again
I decided to go with your suggestion but am having problems getting it to work.
I have defined the variable zUserName,

Dim zUserName As String

zUserName = Environ(“UserName”)

and used it like this

Case OptSharon
If zUserName = “Sharon Thomas” Or “Jen Ferguson” Then

Selection.InlineShapes.AddPicture FileName:= _
“P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesSharon Thomas.jpg” _
, LinkToFile:=False, SaveWithDocument:=True

With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
.LockAspectRatio = msoTrue
.Width = CentimetersToPoints(2.5)
End With
Else
MsgBox “You do not have permission to use this signature!”, 48
End

What am I doing wrong?

Reply | Quote

Warning: Environ(“USERNAME”) can easily be change by the user.

I use this: http://access.mvps.org/access/api/api0008.htm

FWIW: During testing I use Environ(“USERNAME”) because it is so easy to change and then reopen the database to change users. Before deployment I switch back to the API above. I have never been able to fool the API.

Getting the current Windows user name lhat is logged on is great if the Windows users is also the database user. This allow for automatic log on or Single Sign On. I like to use a table is access to look up the permissions based on the Windows user name. If the Windows User name is not found tin the user table then they are not allow in the database.

You may need to use AD if the database user may not be the user logged onto Windows or you need to check the user’s AD security groups for permissions. If you do need to use AD, let me know and I will post some code.

.I prefer to roll my own security model all in Access. I will use the Windows User name to log in but I have my own users and permissions tables that the Windows user name is verified against. This keeps the security groups in AD simple. It allows the Access sercuity to be robust and easily changed without any AD updates.

Reply | Quote

Phil,

What is the username? Is it “OptSharon” or is it “Sharon Thomas” ?

Generally you want something like this:

Code:

Select Case zUserName
      Case "Sharon Thomas","Jen Ferguson"
        ---   What you want done here   ---
      Case "Next User Name with different actions"
        .
        .
        .
      Case Else
            MsgBox "You do not have permission to use this signature!",  _
                        vbOkonly+vbCritical, _
                        "Msg Box Title Here!"
End Select

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG
The purpose of this is for the users or their assistants to select signatures to attach to documents. I have created a user form with 10 signatories listed and have used OptionButtons to select, hence the Case statements.
The user, in this case, is Sharon and the secretary is Jen.

Reply | Quote

If you do need to use AD, let me know and I will post some code.

HiTechCoach,

I would be very much interested in taking a look at your code to authenticate using the AD.

Thanks,
Maud

Reply | Quote

Phil,

Ok if I understand your goal here’s what I’d suggest.

In your Form Activate event use code like this:

Code:

Private Sub UserForm_Activate()

   OptSharon.Visible = False
 '--- Repeat for each OptButton ---

  Select Case zUserName
      Case "Sharon Thomas"
          OptSharon.Visible = True
      Case "Jen Ferguson"
          OptSharon.Visible = True

      Case "Next User Name with different actions"
        .
        .
        .
      Case Else
            MsgBox "You do not have permission to use this Document!",  _
                        vbOkonly+vbCritical, _
                        "Msg Box Title Here!"
      Unload Me

  End Select

End Sub

For any given user the form will now only show those signatures they are authorized to use.

Now for each Option button create a Click Event as follows:

Code:

Sub OptSharon_Click()

    Process signature here!
    Unload Me
End

Now when any user selects a signature by clicking the option button it is immediately processed and the form unloaded. Note you can have as many OptButtons under each case statement as necessary. So say a supervisor could have them all visible and pick any signature. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG
Looks better than what I was proposing
However, having got all the code in it now will not recognise any users and we just get the VBWarning with a blank userform in behind, which is correct because it has been instructed to not show anything until the user is verified.

I have set myself as the “Supervisor”

Mmmmmm!!!!!!!!

Reply | Quote

Are you sure your users are named “Sharon Thomas” etc? Use names don’t usually have spaces in them.

cheers, Paul

Reply | Quote

Yes
Checked my username and it is of the same format “Phil Carter”. As stated I have set myself as supervisor, not that I need to use the signatures, but because I need to verified that the links work.

Reply | Quote

Phil,

Could you post your modified code? HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Sure
It is in the attached *.dotm, frmSigInsert

Reply | Quote

I’ve had a look at it and tidied the code up to remove the massive amounts of duplication. I generally prefer to use the properties of the controls themselves to make the VBA code a lot shorter. In this example I used the ControlTipText and the Tag properties on each option control to carry the changeable information. See attached file with the form frmSigInsert2

Personally, I find the method of hiding option buttons an odd way to present these choices to the user. Users will see an oddly arranged list since unexplained gaps will appear in the spacing. This would be significantly improved by using a listbox instead. For even better usability, I would probably do this by using a dynamic ribbon which presents the available options on a ribbon button so there is no userform involved at all. I would most likely also change the methodology to store copies of each signature in the template itself (as building blocks) so that connection to the network drive is not required.

Reply | Quote

Thanks Andrew
I can’t see any difference in the way this performs. It still loads the blank form in the background and then pops the warning message. So my UserName is not being recognised.

Reply | Quote

Andrew and RG
Discovered that we use the user initials as the Environ”UserName” so as soon as I changed mine to PGC every thing worked for both examples.

Andrew I tend to agree with you the code is much cleaner, need to examine it more closely to understand how it works!

Reply | Quote

Andrew
Another twist. I was certain the code worked fine after I changed the username detail. But connected via wifi at work didn’t want to recognise my login. However, just checked gin at home via VPN and all works fine.

Not sure what that is about!!!

Reply | Quote

Phil,

Don’t know what the problem is but it’s on your end. I changed your UserId to mine and it worked as it should.

Code:

  Select Case zUserName
      
      Case "Bruce"
          OptLlew.Visible = True
          OptPhilB.Visible = True
          OptSharon.Visible = True
          OptAnne.Visible = True
          OptKeith.Visible = True
          OptAdrienne.Visible = True
          OptGavin.Visible = True
          OptAGMAS.Visible = True

41840-PhilCarterSigs

I would suggest checking those UserIds again using the Set command in a command window.

Code:

ALLUSERSPROFILE=C:ProgramData
APPDATA=C:UsersBruceAppDataRoaming
CommonProgramFiles=C:Program FilesCommon Files
CommonProgramFiles(x86)=C:Program Files (x86)Common Files
CommonProgramW6432=C:Program FilesCommon Files
COMPUTERNAME=DELLXPS8700
ComSpec=C:WINDOWSsystem32cmd.exe
EMET_CE=I:-1;S:0;F:0;E:0;V:Mar 13 2015 18:35:39
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=UsersBruce
LOCALAPPDATA=C:UsersBruceAppDataLocal
LOGONSERVER=\DELLXPS8700
MOZ_PLUGIN_PATH=C:PROGRAM FILES (X86)FOXIT SOFTWAREFOXIT READERplugins
MSOffice=C:Program Files (x86)Microsoft
MyDocs=G:BEKDocs
NAS=\MYBOOKLIVE
NUMBER_OF_PROCESSORS=8
OS=Windows_NT
Path=C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem;C:WINDOWSSystem32WindowsPowerShellv1.0;C:Program Files (x86)ATI TechnologiesATI.ACECore-Static;C:Program Files (x86)Windows LiveShared;C:Program FilesCalibre2
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 60 Stepping 3, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=3c03
ProgramData=C:ProgramData
ProgramFiles=C:Program Files
ProgramFiles(x86)=C:Program Files (x86)
ProgramW6432=C:Program Files
PROMPT=$P$G
PSModulePath=C:WINDOWSsystem32WindowsPowerShellv1.0Modules
PUBLIC=C:UsersPublic
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:WINDOWS
TEMP=C:UsersBruceAppDataLocalTemp
TMP=C:UsersBruceAppDataLocalTemp
USERDOMAIN=DellXPS8700
USERDOMAIN_ROAMINGPROFILE=DellXPS8700
[COLOR="#0000FF"][B]USERNAME=Bruce[/B][/COLOR]
USERPROFILE=C:UsersBruce
VBOX_MSI_INSTALL_PATH=C:Program FilesOracleVirtualBox
windir=C:WINDOWS

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil

Your method of verifying the username works only if you know who each user is and what their particular user account has configured their name as. You can check out the relevant information by typing the following into the immediate window.
? Environ(“UserName”)

Once you know the username, you can make sure the code recognises that user.

Reply | Quote

Thanks Andrew
The login details I have, have come from our IT guys. The IT guys inform me that there should not be any difference in the login verification for online or VPN even though there clearly is some difference. We do not have different passwords for each.

Tried your suggestion and confirms my login

Have searched around and tried various forms of the same, VBA.Environ(……, Environ$(……, VBA.Environ$(…., checked the ToolsReferences and all seems OK. I have even tried concatenating the Domain name as part of the UserName

Reply | Quote

You might need to post your current code.

I assume that you are using
zUserName = Environ(“UserName”)

Try putting a breakpoint on that line and see what value you get for zUserName when that line has run. Is it the same on VPN and online?
Do you get the same values if you test zUserName immediately afterwards by doing
debug.print zUserName & vbTab & Len(zUserName) & ” characters”

Reply | Quote

Andrew hi
I have finally managed to get this sorted. Seems AD doesn’t differentiate between case although it does seem to in some cases!!

Next question, how can I make the userform dynamic because the signatures used by the various staff range from 1 – 8 and in varying order. This makes the userform look pretty awful.

Reply | Quote

This article has a mechanism for getting the AD user name.
http://stackoverflow.com/a/11196206

cheers, Paul

Reply | Quote

You may also want to try the Windows AI meeting that I posted earlier. At least give it s test to rule it out.

Reply | Quote

Phil,

You can adjust your code easily since the spacing from the top of one to top of the next is 18 as follows:

Code:

Select Case zUserName
      
      Case "xBruce"
          OptLlew.Visible = True
          OptLlew.Top = 12
          OptPhilB.Visible = True
          OptPhilB.Top = 30
          OptSharon.Visible = True
          OptSharon.Top = 48
          OptAnne.Visible = True
          OptAnne.Top = 66
          OptKeith.Visible = True
          OptKeith.Top = 84
          OptAdrienne.Visible = True
          OptAdrienne.Top = 102
          OptGavin.Visible = True
          OptGavin.Top = 120
          OptAGMAS.Visible = True
          OptAGMAS.Top = 138
      Case "Llew Richards"
          OptLlew.Visible = True
          OptLew.Top = 12
      Case "Bruce"
          OptPhilB.Visible = True
          OptPhilB.Top = 12
          OptLlew.Visible = True
          OptLlew.Top = 30
      Case "Barry Ashcroft"
          OptAGMAS.Visible = True
      Case "Anne Hofstra"

Original Form Design:
41978-SignerDesign
Your menu (all signers)
41979-ALLSigners
The third in the list (currently my userid Bruce)
41980-OtherSigner

If you want to get really fancy you could also adjust the form height so it you get rid of the blank space at the bottom.

41981-SizeAdjusted

Just Add to each section:

Code:

Me.Height = 80

Where the value is the last .Top + 50.
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG’s code would do the trick but it appears to be a major task to maintain over time. We can work with option buttons but I still recommend you convert the userform to use a listbox instead. That would avoid the need to code each option button and hence would streamline the code considerably.

If you want to persist with option buttons, I would rename the option buttons to a straight series (eg Opt0, Opt1, Opt2 etc) and assign an array of names to each user. Then a loop could be run on the form initialise event to write each name in the array to each option button until you run out of names and then hide the remaining option buttons.

If you post your current userform and code I can show you how I would amend it.

Reply | Quote

The code hasn’t changed since you developed it but I would be interested in seeing how you would change

Om another matter can you recommend a good text(s) as a reference for VBA coding

Reply | Quote

Phil,

Here’s what I think Andrew was hinting at:

Setup your form like this:
41992-PhilForm
Which entails changing your current items names to Opt1, Opt2,….Opt8 and clearing the Caption field for each.

Code:

Option Explicit
Option Base 1

Dim zSelectedSigner As String

Private Sub UserForm_Activate()
'code modified 25/08/2015 to include suggestions from RG/HTH at Windows Secrets

   Dim zUserName As String
   Dim vSigners  As Variant
   Dim iCntr     As Integer
   Dim iTop      As Integer
   
    zUserName = Environ("UserName")

   Opt1.Visible = False
   Opt1.Visible = False
   Opt3.Visible = False
   Opt4.Visible = False
   Opt5.Visible = False
   Opt6.Visible = False
   Opt7.Visible = False
   Opt8.Visible = False
 '--- Repeat for each OptButton ---

  Select Case zUserName
      
      Case "xBruce"
          vSigners = Array("Liew Richards", "Phil Barnes", "Barry Ashcroft", "Anne Hofstra", "Keith Towl", "Adrienne Woollard", "Gavin Tasker", "Sharon Thomas")
      Case "Llew Richards"
          vSigners = Array("Liew Richards")
      Case "Bruce"
          vSigners = Array("Phil Barnes", "Liew Richards")
      Case Else
            MsgBox "You do not have permission to use this Document!", _
                        vbOKOnly + vbCritical, _
                        "Security Warning!"
      Unload Me

  End Select
  
  iTop = 12

          For iCntr = 1 To UBound(vSigners)
             With Me.Controls.Item("Opt" & Format(iCntr, "#"))
                 .Caption = vSigners(iCntr)
                 .Visible = True
                 .Top = iTop
                 iTop = iTop + 18
             End With  'Me.Controls....
          Next iCntr
  Me.Height = iTop + 32
  
End Sub

Sub Opt1_Click()
   InsertSigniture Opt1.Caption
End Sub

Sub Opt2_Click()
   InsertSigniture Opt2.Caption
End Sub

Sub Opt3_Click()
   InsertSigniture Opt3.Caption
End Sub

Sub Opt4_Click()
   InsertSigniture Opt4.Caption
End Sub

Sub Opt5_Click()
   InsertSigniture Opt5.Caption
End Sub

Sub Opt6_Click()
   InsertSigniture Opt6.Caption
End Sub

Sub Opt7_Click()
   InsertSigniture Opt7.Caption
End Sub

Sub Opt8_Click()
   InsertSigniture Opt8.Caption
End Sub

Private Sub InsertSigniture(zSelectedSigner As String)

Select Case zSelectedSigner

      Case "Liew Richards"
          Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignatureswlr.gif" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
            
     Case "Phil Barnes"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturespbarnes.jpg" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
            
     Case "Barry Ashcroft"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesBRA.JPG" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
     Case "Anne Hofstra"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesanneh.jpg" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
     Case "Keith Towl"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesKeith Towl.jpg" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
    Case "Adrienne Wollard"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesWoolard.jpg" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
    Case "Gavin Tasker"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesGavin Tasker.jpg" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
    Case "Sharon Thomas"
    Selection.InlineShapes.AddPicture FileName:= _
        "P:_IANZCorporateServicesBusinessServicesGraphicsSignaturesSharon Thomas.jpg" _
            , LinkToFile:=False, SaveWithDocument:=True

            With ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
                .LockAspectRatio = msoTrue
                .Width = CentimetersToPoints(2.5)
            End With
  End Select

  Me.Unload

End Sub  'InsertSignature

Note: Since I didn’t have your signature files I verified that it was selecting the right signature via stepping through the code. So I don’t know if the Me.Unload will work at the end of InsertSignature, it should, so if it errors you’ll have to put it in each of the _Click events. I also shortened the Users section, I’m lazy, so you’ll have to add in the Case statements and Arrays for those I deleted.

Test File:

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil

Try this version in the frmSigInsert3 userform. I used a listbox in the userform since it manages longer and shorter lists easily without the need to micromanage placements.

Note that to avoid the need for assigning filenames to each possible choice, it is much simpler to organise your signatures to match the displayed name and all be in a consistent file format. I coded this to assume all the signatures exist as PNGs which is the best file format for that type of bitmap. If you don’t rename your signature files then the code in the click event will need to be extensively changed.

Reply | Quote

Andrew
Thanks very much for this
I have had to massage my files a bit but what you say makes sense and works a treat.

I will deploy tomorrow and hopefully!! no problems

Reply | Quote

Andrew,

Very nicely done. I’ve never seen the Split command used in lieu of a ReDim Very Cool! :thewave:

This one is going into my tool box post haste.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote