Solved: Only running MyExcel.xls on named machine?

[Ken Puls]

All righty...

Here's another version of the Excel workbook. I've changed the code around a bit to allow you to check if someone is authorized to run, or where their issue is (bad computer or user name). Just an FYI, this will only work on the first record of the database, so if you have multiple users who do use the same PC, we'll need to adjust something again.

To use this, you'll again need to go into Module1 and change the path to the database. While you're there, notice the new constant:

Public Const AddUserMode As Boolean = True

This gives you an easy way to flip the switch on adding new users. When you've got all your users in there, change the True to False, and anyone who has not been added in any way will get a nice message asking them to send you the info for inclusion. Until that time, it will add info for anyone who does not have a match of either computer or username in there.

For anyone who has a computer/user name in the database, if they don't match, they're told to go back to their seat.

Lastly, uncomment the Save line in ThisWorkbook's Workbook_Open module as well. Then save it and give it some tests. You do have Access, right? Just open up to the table, change pieces of either, close the table and reopen the workbook. You don't need to completely close the database.

Have a play, and fire away with the questions. I'm sure you'll have some.

[Simon Lloyd]

Ken, yes i do have access and every other MS application (one of the privilidged few!)thats kinda cool and confusing it looks like you have set up a Case Select but is referenced in code in different modules?

 
Public Enum UserStatus
    us_NoExist = 1
    us_uNameWrong = 2
    us_cNameWrong = 3
    us_Authorized = 4
End Enum

does Enum have a specific job (i've never come across it)?, it seems that once you have done this it becomes available when you Dim something As. I suppose what i find confusing is that you can declare a Public Constant or Public something in one module and refer to it in other modules.

It worked brilliantly if i changed one item of captured data im told to go back to my seat, if i've changed the capture switch i'm denied access and given the information i need to send....just brilliant!

Of course you are right with the multiple machine users, its not all machines that have multiple users but there are up to 5 (no more) users for around half our machines, to capture this seems like a headache as the only way i could see of defining which user should use which machine is to find the machine they use most over a perid of time and then manualy delete the other entries, perhaps i could set up a query in access for all instances of a persons name find the most popular machine and delete the rest, of course if i get it wrong they will kick up a fuss but then i will get the required info.

As an added question: i have 27 different workbooks is it possible they can call this one first Do the checking and return them to their original workbook?, also 21 of those workbooks are shared, if a shared workbook is open by another user would it still go thru the validation process?

Regards,
Simon

[Ken Puls]

LOL!

I did set up a select case, referencing results from function calls to other modules, yes.

With regards to the Public statement... all of your subs and functions are by default Public. I don't need to declare them with the Public keyword, but I prefer to code to make things obvious. So long as they are public, they are available to all the other modules. If you declare them as Private, though, then they are restricted to within that module only.

The Enum is a way of declaring your own constants. (Sort of like vbOkOnly is a constant). It basically lets VBA know that when you see, say "us_uNameWrong" in the code, that it's equal to 2. It can make your code a little more legible than trying to figure out what UserStatus = 2 is. In addition, if you declare your functions using the term, then it adds it to intellisense for you. So:

Function TellMeAboutThis(SomeInfo as Userstatus) as Boolean

Would allow you to have intellisense give you the list of values when you were plugging the function into code somehwere. The following (which is equivalent) would not:

Function TellMeAboutThis(SomeInfo as Long) as Boolean

Just to clarify, though, the fact that the Enum exists is what allows it to work this way, not the fact that it is Public. The Public portion just allows us to make use of it across modules. If it were a Private Enum, we'd only be able to use it in Module1 (or wherever we put it)

Now, with the roaming users... if you have some users that are legitimately allowed to roam, we could add a loop to check all records returned from the database. You'd need to add each workstation that they could use though. This would also benefit if you have shared desks as well.

On the 27 different workbooks... let me think on that one for a bit. There's a couple of ways that we could look at it, I'm just trying to think of the best one.

[johnske]

Why not give passwords to the legit. roamers so they can access it that way?

[Ken Puls]

Hi John,

I think Simon's main concern is that his guys pass on their passwords to their friends. He's after a way to lock them down by user id and computer to make sure that they really are who they say they are and authorized.

Or is that not what you meant?

[johnske]

Not quite - browsing thru this thread, we seem to have already got to the stage where user ID and/or computer is checked...

Now, we've established they're working from home, so - now ask for their authorization password to establish that they're allowed to work from home.

You could have a number of passwords, including time-limited ones that allow access for one hour, day, week (whatever) and maybe unlimited ones for the really trusted employees. (The temp passwords could also be changed regularly if you think they're being passed around )

[Simon Lloyd]

Ken Thanks for the brief lesson - a few light bulbs switched on!, users really shouldnt roam all those that do need to be locked out, yes there are shared desks no more than 5 users a desk.

Johnske, you know what its like working on a network, theres your network logon and password, folder passwords, file passwords etc, it seems every turn you make you have to enter a password it gets very tiresome, our network login password has to be changed every 28 days!, through kens help and insight a way has emerged of protecting, verifying and authorising the workbook and access to it silently without much user interaction or time lag - in short it checks its you at your desk and authorised to use the program - no ifs or buts - its a controlled usage.

This thread was sparked because users were taking the workbook home on flash drives then saving back to the network the next day overwriting everyones work including mine, and because i constructed the workbooks it was my task to load the back up copy back and wait for someone to complain that their recent work had been deleted - so catching the culprit provided they had the gumption to complain - no user is allowed to take their work home - company policy anyway - the password sharing and user ID (login) sharing can be controlled using Kens method - everyone least of all me will be a happy bunny!

I do understand that you have browsed the thread and it looks as if it has gone full circle, and in the interest of brevity and Kens time would like to see it come to a swift conclusion, the thread i think has had around 329 views so along the way other folk are getting schooled just like me!.

We're nearly there.....or should i say Ken is, i could never have come so far or learnt so much without the length of this thread.

Regards,
Simon

[Ken Puls]

Hi Simon,

A new file attached. It contains two edits.

Edit number one was to the Workbook_Open code. I forgot to add the Goto ExitPoint in the Else clause of the FileorFolderExists check. Not having it there causes an error on the home users PC, rather than a clean message.

I've also added a loop around the user/computer name check. This will look to see if any user is allowed to use the computer, fixing the sahared workstation issue. Theoretically, if someone uses more than one workstation, you could still register the other combinations in the db as well. Lots of flexibility there.

Now, lastly we need to deal with the 27 workbooks... I believe that you said you had 2000 users as well, correct?

Approach 1
My personal preference (and best practices dictation) is to put the code in a separate workbook from the data. This way if you want to update either, you can do so, without affecting the other. It also means you can have one code source to update (vs 27), and reuse the code across multiple applications.

In order to do this, we'd need to convert the control workbook to an addin, which isn't hard to do. We'd also need to put in another class module (and this would have to be a class module) to monitor the opening of workbooks, to see if they should be checked for authorization. (Every workbook opened would be checked to see if it was in a list that you'd need to build.) The problem then becomes deploying your addin and getting it installed for all the users who should be running through it.

To solve that issue, we'd need to add a really simple piece of code to each workbook checking if the addin was installed. If it wasn't, install it, and run this code to validate the computer/user combos. If it doesn't install, they're at home. There's a double level of checking then, but we could strip the "at home" check from this one.

Approach 2
The easier way in the short term would be to just copy all of this code into each of your 27 workbooks. The only problem is that if you need to maintain it, you have 27 workbooks to do. It does give you a bit more freedom by having a separate database for each application though, so that you can control who is authorized for each workbook on a more granular level.

Let me know what you think.

[Simon Lloyd]

Ken i cant begin to tell you how much i appreciate your time and help on this!

Approach 1
My personal preference (and best practices dictation) is to put the code in a separate workbook from the data. This way if you want to update either, you can do so, without affecting the other. It also means you can have one code source to update (vs 27), and reuse the code across multiple applications.

This is the approach that i would prefer, however i thought it would have been a fairly simple case of [vba]Workbook.Open "F:\Controls.xls"[/vba]in each workbook then in Control.xls if user is not in database (provided i have closed collection) pass the error back to the original (calling) workbook and close it. My reservation with an Add-In as you said is the installation on each persons machine, if there is an Add-In doesn't the user have control over these via the toolbar?, you know what people are like - see something that wasnt there before believe they dont need it and delete it!

I believe that you said you had 2000 users as well, correct?

Yes there is the possiblity of up to 2000 entries according to our IT dept. there are 2,783 users at our level on our local network.

Approach 2
The easier way in the short term would be to just copy all of this code into each of your 27 workbooks. The only problem is that if you need to maintain it, you have 27 workbooks to do. It does give you a bit more freedom by having a separate database for each application though, so that you can control who is authorized for each workbook on a more granular level.

This level of control would be good, however i don't relish the thought of going through every workbook adding the code setting the reference in the VBE to the Microsoft ActiveX Data Object 2.x library!.

Opening the workbook at home is indeed cleaner i didnt get the error message telling me that F:\Auth.txt could not be found.

By the way do you know it was Sunday when you posted this?, don't you rest? LOL

Regards,
Simon

[Ken Puls]

This is the approach that i would prefer, however i thought it would have been a fairly simple case...

Unfortunately, no. It could probably be made to work along those lines, but it would be a bit messy.

My reservation with an Add-In as you said is the installation on each persons machine, if there is an Add-In doesn't the user have control over these via the toolbar?, you know what people are like - see something that wasnt there before believe they dont need it and delete it!

Actually, the add-in will not show up on the toolbar unless you tell it to. Unless you create code for it, no toolbars or menu items are created. The only place it will show is in the Add-ins dialog box. And even if a user clears it from there, so long as the code is placed in the 27 workbooks to check for it, those workbooks could just reinstall it.

This level of control would be good, however i don't relish the thought of going through every workbook adding the code setting the reference in the VBE to the Microsoft ActiveX Data Object 2.x library!

Oh, it's not that bad. You can even add a reference by code, if you like:

Sub AddReference()
    ' Macro purpose:  To add a reference to the project using the GUID for the reference library
    Dim strGUID As String, theRef As Variant, i As Long
    ' Update the GUID you need below.
    strGUID = "{00000200-0000-0010-8000-00AA006D2EA4}"
    ' Set to continue in case of error
    On Error Resume Next
    ' Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
        Set theRef = ThisWorkbook.VBProject.References.Item(i)
        If theRef.isbroken = True Then
            ThisWorkbook.VBProject.References.Remove theRef
        End If
    Next i
    ' Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear
    ' Add the reference
    ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:=strGUID, Major:=1, Minor:=0
    ' If an error was encountered, inform the user
    Select Case Err.Number
        Case Is = 32813
            ' Reference already in use.  No action necessary
        Case Is = vbNullString
           ' Reference added without issue
        Case Else
            ' An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0
End Sub

By the way do you know it was Sunday when you posted this?, don't you rest? LOL

LOL! This is my hobby, so no.

At any rate... the addin route is still probably the best route, but adding the code to each workbook is much easier. It's up to you still.

[Simon Lloyd]

Ken, its fast becoming a hobby of mine!

Evaluating what i'm trying to achieve and the maintainance i think it wil have to be the Add-In. I have currently moved all workbooks to one folder E:\Analyst\Development\Admin i thought it would be much easier than finding them spread out in different folders across the network (im going to create a sheet of hyperlinks to them for future reference when i put them all back!). So in this folder there are 28 workbooks, 27 brought back in and the controls.xls all workboks have had the passwords removed.

Do i now have to open all the workbooks and in the WorkBook Open add a reference to the Add-In or install the Add_in?

Would the Add-In be in the controls.xls and all workbooks will install from there if not already in place?

Do i write the Add-In in this format?

Function Area(Length As Double, Optional Width As Variant)
    If IsMissing(Width) Then
        Area = Length * Length
    Else
        Area = Length * Width
    End If
End Function

Then save it as Add-In (not that im entirely sure how or what to write. Then do i just call the Add-In from each workbook......if the Addin could not be found would this cause an error?

Does the user have to grant permission for the Add-In lets say if it were removed by the user and the workbook wants to re-install it?

Regards,
Simon

[Simon Lloyd]

I have been trawling the net looking for procedures to write code and modules to workbooks programatically and this is what i have found i cant get it to run even though i have set the reference to Microsoft Extensibility Library 5.3, it doesnt seem to like any reference to VBProject!. I thought this could have been used to write to all 27 workbooks either writing the reference you mentioned above or the entire code!....................me being lazy again!
Regards,
Simon

 
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal ClassName As String, ByVal WindowName As String) As Long
Public Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hWndLock As Long) As Long

Sub AddModule()
    Application.VBE.MainWindow.Visible = False'''It doesnt like this line
    Dim VBComp As VBComponent
    Set VBComp = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = "AddInRef"
    Application.Visible = True
    Call AddProcedure
End Sub

Sub AddProcedure()
    Dim VBCodeMod As CodeModule
    Dim LineNum As Long
    Dim VBEHwnd As Long
    On Error GoTo ErrH:
    Application.VBE.MainWindow.Visible = False
    VBEHwnd = FindWindow("wndclass_desked_gsk", _
    Application.VBE.MainWindow.Caption)
    If VBEHwnd Then
        LockWindowUpdate VBEHwnd
    End If
    ' your code to write code
    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("AddInRef").CodeModule
    With VBCodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, _
        "Sub MyNewProcedure()" & Chr(13) & _
        " Msgbox ""Here is the new procedure"" " & Chr(13) & _
        "End Sub"
    End With
    Application.Run "MyNewProcedure"
    Application.VBE.MainWindow.Visible = True
    'Application.VBE.MainWindow.Visible = False
    Call WBO
    ErrH:
    LockWindowUpdate 0&
End Sub

Sub WBO()
    Dim StartLine As Long
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        StartLine = .CreateEventProc("Open", "Workbook") + 1
        .InsertLines StartLine, "Msgbox ""Hello World"",vbOkOnly"
    End With
End Sub

Function ProcedureExists(ProcedureName As String, _
    ModuleName As String) As Boolean
    Dim ModuleExists
    On Error Resume Next
    If ModuleExists(ModuleName) = True Then
        ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
        .CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) <> 0
    End If
End Function

[Ken Puls]

Hey Simon,

Your enthusiasm is amazing!

What you're going to want to do is open each workbook (probably using fso to seach for the files in the directory), and create an event procedure in the ThisWorkbook module. Chip Peason has an article on Programming to the VBE. (Look up the Event Procedure portion.)

Question is, what code are you going to put in there?

[Simon Lloyd]

Yes Ken, it seems the more i learn the more i want to learn..VBA is facinating with the flexibility and control it gives you, i got that code from Chips site, like i said i cant get it to work....but what i was intending was to either load whatever finished code we come up with or create a module in each workbook that the calls the control.xls workbook to begin the check for authorisation, workstation and username, like i said i have moved all the workbooks to one folder at the moment and left shortcuts for the users in the places that they used to be, however i have had a fair few complaint because people who have shortcuts on their desktops now cannot access the files (annoying but they will have to wait until the development of this final coding is complete!).

Getting back to the create a module i probably would have done something like

 
workbooks = E:\Analyst\Development\Admin 
For Each Workbook in Workbooks '( iknow this probably isn't the correct syntax)
    Workbook.Open
    Call AddModule
Next WorkBook

then the code from chips site would create the module and add our code to it, so that when the workbook is opened it then opens Control.xls and performs check if not authorised close all open workbooks.

Am i going to far with the coding of these workbooks?, it seems we're so close to adding the code but so far every time i try to be lazy by automating everything.

Regards,
Simon

[Ken Puls]

Am i going to far with the coding of these workbooks?, it seems we're so close to adding the code but so far every time i try to be lazy by automating everything.

LOL!

Maybe. For the sake of the code that we will drop in there, it might be more work to write the routine to write VBA than it will be to just open, paste and close your workbooks.

I'll try to look at this today, but there is definately some changes that will need to be made to the control.xls workbook and also we'll need to create code for each workbook that:

• Checks if the addin is installed
• If not tries to install it (If error then user must be at home)

Honestly, I'd move the files back till you're ready to deploy it. Maybe make some copies for yourself to test. Unless you want to group them all in the same place anyway, that is.

[Simon Lloyd]

Ok Ken taken under advisement!, i have moved the workbooks back (created hyperlinks to them, dont know why i didnt do that when i created them!), as for the code to write code i found myself chuckling your probably right......but you do know that now that i have found that code i will mess around with it at home until i totally crash my PC! LOL!.

And............THANKS!, thanks for still sticking with this!

Regards,
Simon

[Simon Lloyd]

Ok Ken taken under advisement!, i have moved the workbooks back (created hyperlinks to them, dont know why i didnt do that when i created them!), as for the code to write code i found myself chuckling your probably right......but you do know that now that i have found that code i will mess around with it at home until i totally crash my PC! LOL!.

And............THANKS!, thanks for still sticking with this!

Regards,
Simon

[Bob Phillips]

I'm probably jumping in where I don't understand here, but why don't you develop your code in a module, export the module, then import it into the other workbooks?

[Bob Phillips]

Using VBA to import it of course.

[Simon Lloyd]

Ahhh! The great "El Xid", nice to hear from you, Ken is assembling an Add In for me to get a workbook to automatically open another which performs various types of verification (as discussed in the many posts of this thread), he is also creating some code which checks for the Add In and if its not there re-install it provided the user is at work.

My posting above was me trying to be lazy and not opening all 27 workbooks and copying and pasting code in to them thats why i was lookiin at Chips code to do the job but i understand what was Ken was saying and will paste the code as he suggested.

But thanks for the suggestion.

Regards,
Simon