Solved: Only running MyExcel.xls on named machine?

Hi all, every users machine has a unique name, is it possible in VBA to only allow your program to run on a machine with a specific name?. I have a program which we use here at work and its on a network drive but some people are taking a copy home working on it and then copying it back to the network, however if people have made changes to it in the meantime they get wiped out when the unscrupulous person who took a copy home writes back to the network.

What i would like to do is send everyone who has access to the program an excel file which when opened collects the machine name, they can then mail it back to me where i could then try to incorporate it in my VBA to only allow machines in the list.

Is it possible?

Regards,
Simon

Hi Simon,

The snippet

Code:

---

environ("computername")

---

Will return the computer's name. You could put something like this at the top of your vba:

Code:

---

if environ("computername") <> "VBAXPC" then exit sub

---

To collect the computer names like you described (assuming you have outlook on each machine):

Code:

---

Sub SendComputerName()
    Dim olApp As Object
    Set olApp = CreateObject("outlook.application")
    With olApp.CreateItem(0)
        .To = "slloyd@abc.com"
        .Subject = "My computer name"
        .Body = Environ("computername")
        .Send
    End With
    Set olApp = Nothing
End Sub

---

Though there should be some administrator tools to get all those names without having to deal with enabling macros, outlook security warnings, etc. However, I don't know them.

Matt

thanks mvidas :thumb

just used some of the above code on my spreadsheet.

works well for what i need.

Hey guys,

While what you're trying to do will work, you're going to have to maintain it, which can be kind of a pain. Also, what happens when you move on, and a new user needs to be added?

The route I go with this, which involves a lot less headache, is to check for the existence of Drive H:\ on my system. Most users do not have more than a C,D or E drive at home, so this works for me. To go one step further, you could find a specific drive that everyone has access to, and drop a file in there. If the file doesn't exist, then don't let them write.

The beauty is that as long as the mapped drive doesn't change (something most IT departments try to avoid), you should be okay. Just make sure you pick one that everyone has access to, and is high enough that most users won't have it at home. If you can't, then go the file route.

I use this function to check for either files or directories, as it works with both.

HTH,

MVidas, thanks for the detailed reply, not all machines have Outlook (as some are laptops!) is there a way Excel can collect the machine name?

Ken i understood what you are getting at, the network drive that the workbook is stored on is E:, none of our machines here have CD Rom or floppy drives (so we cant load any software!) but we do have USB's for using our companies data keys (data keys issued by the company dont work at home and vice versa), the problem of specifying a drive is that when managers or upper level staff connect their laptops the drive letter to them is no longer E: as they are fitted with all usual peripherals.

Regards,
Simon

Hi Simon,

Okay, how about this then... drop another file in the same directory as the workbook on your network. Just don't tell anyone about it.

You can then check for the file using:
[vba]If FileOrFolderExists(Thisworkbook.Path & Application.PathSeparator & "auth.txt","txt") Then
'the file exists, so it is on the company drive
Else
'the file does not exist, so they must be working remotely
End If[/vba]

If you felt really tricky, you could check if the file was in the directory that it is supposed to be in, or even create a file in a directory that is... say...

E:\Excel Files\TheReport.xls
E:\Confidential\MyKey.txt

By using Thisworkbook.Path, you are looking at the path where the workbook is stored. So long as you come up with something the users don't know they need to replicate, you should be fine.

Ken.....amazingly fast response!, as you will be aware we are networked and as such only have access to certain folders on certain drives, the workbook is stored in E:\Analyst\PublicFile\Raw, on this drive this is the only path i have access to as my work deals with manipulating the raw figures that users deposit, can i still use your suggestion here?, the user would be able to copy the folder with the .txt file in it.

Regards,
Simon

I can create a text file like this:

Code:

---

Sub TextFileCreate()
    Close #1
    Open Application.Text(Now(), "dd mm yyyy") & ".TXT" For Output As #1
    Close #1
End Sub

---

i would just need to add the path to save it to, but i fear i can only save it to the path the folder is in on that drive!

Regards,
Simon

Simon,

As for getting a list of computer names, it is possible, but depending on the size of your company it could be a HUGE list (when I just ran it I got 4874 names)
But to get it:

Code:

---

Sub GetComputerNames()
    Dim vFF As Long, cNames() As String, Cnt As Long, tStr As String
    Shell "Net view >c:\compNames.txt"
    vFF = FreeFile
    Open "C:\compnames.txt" For Input As #vFF
    Do Until EOF(vFF)
        Line Input #vFF, tStr
        If Left(tStr, 2) = "\\" Then
            ReDim Preserve cNames(Cnt)
            cNames(Cnt) = Mid(tStr, 3, InStr(tStr, " ") - 2)
            Cnt = Cnt + 1
        End If
    Loop
    Close #vFF
    ' cNames now holds an array of all the computer names on the network for display purposes
    Range("A1").Resize(Cnt, 1).Value = Application.Transpose(cNames)
End Sub

---

Matt

Matt, nice!

Simon, the setup you have is still workable for my suggestion as long as everyone is accessing that file from the same drive. The problem you're going to deal with is that some people are going to have an old copy floating around out there.

No need to create this by code, just make a plain old text file called "auth.txt". Drop it in the same directory as your workbook.

Go into your workbook and put this in the ThisWorkbook module:

Code:

---

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If FileOrFolderExists(ThisWorkbook.Path & Application.PathSeparator & "auth.txt", "txt") Then
        ' The file exists, so it is on the company drive
    Else
        ' The file does not exist, so they must be working remotely
        MsgBox "Sorry, but you are working away from the office. " & vbNewLine & _
        "To prevent loss of other users work, this workbook" & vbNewLine & _
        "has been restricted for use only while attached" & vbNewLine & _
        "to our corporate network.", vbCritical + vbOKOnly, "Remote Access Error"
        Cancel = True
        Saved = True
    End If
End Sub

---

If you want to test it, rename the auth.txt file to something else, open the workbook, put some data in, and try to save it.

PS, don't forget that you'll also need to copy in the routine that I linked to on my website earlier in the thread. ;)

HTH,

Functions to retrieve username and computername. Two subs to demonstrate. Instead of a msgbox you could write the info to a sheet thats hidden so the people don't know why you want this. Everyone must open the sheet once (via workbook open you could automate the collecting of your requested info). After a week you take the spreadsheet from the network drives and hopefully everyone has granted your wishes. ---Thinking : is it possible to send an automated message to you. I know there exists something as a send command through cmd-window. Maybe by shell-command ---

Code:

---

Option Explicit

Private Declare Function GetComputerName Lib "kernel32" _
    Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function NameOfComputer()
    '  Returns the name of the computer
    Dim ComputerName As String
    Dim ComputerNameLen As Long
    Dim Result As Long
    ComputerNameLen = 256
    ComputerName = Space(ComputerNameLen)
    Result = GetComputerName(ComputerName, ComputerNameLen)
    If Result <> 0 Then
        NameOfComputer = Left(ComputerName, ComputerNameLen)
    Else
        NameOfComputer = "Unknown"
    End If
End Function

Function UserName() As String
    '  Returns the name of the logged-in user
    Dim Buffer As String * 100
    Dim BuffLen As Long
    BuffLen = 100
    GetUserName Buffer, BuffLen
    UserName = Left(Buffer, BuffLen - 1)
End Function

Sub show_computername()
    Dim pc_name As String
    pc_name = UCase(NameOfComputer)
    MsgBox ("This computer his name : " & pc_name)
End Sub

Sub show_username()
    Dim inlogname As String
    inlogname = UCase(UserName)
    MsgBox ("Inlogname user : " & inlogname)
End Sub

---

Charlize

Good point, Charlize! Might get a little annoying at times for Simon though :)

Code:

---

Sub SendPCName()
    Shell "Net send slloyd " & Environ("computername")
End Sub

---

Replace your network username with slloyd

Guys!........fantastic responses as ever, i have now added the code to my workbook that charlize gave (i have test driven the other code suggestions), however i like the idea of colleccting the names on a worksheet, MVIDAS the SendPCName sub you gave how do i use that? my networkname is "vrtSzL04" so would i substitute slloyd for that?

Regards,
Simon

Yep
You can test it yourself if you wanted, go to start, then run, and in the run dialog enter
net send vrtSzL04 hi

You should see what it looks like there
Matt

Matt, Charlize and Ken, I know ive marked this thread solved but just wanted to give feedback. Matt your code for collecting machine names gave an error first until i created compNames.txt in C: but then when run did not populate the .txt file or excel and i dont pretend to understand how you are polling the machines, Charlize your functions do what i would like only everytime a user logs on it records the information again, after 2 days i have multiple entries from 23 people, Ken your code is great for keeping the work locked down to only being edited on the network (as was my original problem) but collecting the names gives me greater control over who accesses the file i.e joe bloggs cant give Fred bloggs his login to access the file unless its on the same machine.

The "Net Send" command is kinda cool but is there a way of capturing the required data off it or do i have to go through cancelling 172 (yes i counted 'em) net messages again?

Still great and thorough responses.....as ever!, i'm at present trying to incorporate Kens code along with capturing Username and Machine name but still at present getting multiple hits of the same name.

Once again thanks guys!

Regards,
Simon

Quote:

---

Originally Posted by Simon Lloyd

Matt your code for collecting machine names gave an error first until i created compNames.txt in C: but then when run did not populate the .txt file or excel and i dont pretend to understand how you are polling the machines

---

The file should be created using the line[vba]Shell "Net view >c:\compNames.txt"[/vba]The 'shell' part of that more or less tells excel to run that on a command line.. the "Net view" is a command to see the network (like 'net send' is used to send a message in the network), and the ">" tells excel to send the results to that file.
Do me a favor, go to Start, then Run, and once your dos window is open just type "net view" and press enter. I'm curious to see what comes up for you.

Quote:

---

Originally Posted by Simon Lloyd

The "Net Send" command is kinda cool but is there a way of capturing the required data off it or do i have to go through cancelling 172 (yes i counted 'em) net messages again?

---

There is no way of capturing that message window (not an easy way), but what you just said begs the question:
Do you really want 172 computer names hard coded into your code? Seems to me it would be easier to use Ken's method.
But as I like to give different examples, are you on an exchange server and do you know your SMTP server name?
If you think you are on exchange (many companies are) but don't know the server, and go to Tools / Services, on the services tab there should be a listbox with one option of "Microsoft Exchange Server". Click that, and go to Properties. Write down the server listed there, and you could try using CDO to send a message (if its installed on the machine):
[Code]Sub SendSimonComputerName()
Dim objCDO As Object
On Error Resume Next
Set objCDO = CreateObject("CDO.Message")
On Error GoTo 0
If objCDO Is Nothing Then 'cdo must not be installed on the machine
Exit Sub
End If
With objCDO
.Subject = "Computer name"
.From = "ComputerNames@domain.com"
.To = "simon.lloyd@company.com"
.TextBody = "Username: " & Environ("username") & vbCrLf & _
"Computer name: " & Environ("computername")
With .Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = SmtpServer
.Item("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
.Update
End With
.Send
End With
Set objCDO = Nothing
End Sub[/vba]

Replace your real email address into the .To line, and replace "SmtpServer" with your server.

In the end, I'd say if you still want to hardcode the computer names, then try using the 'net view' again. If it doesn't work, consider Ken's method :) The above is just a workaround, you'd still have to gather the data from each email you received.

Another though, instead of the email...

Create a database in the same directory as the excel file. Add the message to check if the database is there. If it isn't, they get the message that they are working away from the office.

Add some code to write the values you want to the database. (I have code examples of doing this kind of thing with Access.) If the database is there, write the user's login ID and computer name to the database.

After a certain amount of time, you cut out the routine that adds credentials, and start to check the computer name and login ID against what you've logged in the database to see if they should get access. If the username is there, but the computer name doesn't match, you can tell them to go back to their desk. If the computer name is a match, but the username isn't, you can tell them that they are not authorized.

The nice thing about this route is that you don't have to deal with 172 separate users once you write the code. You let them start adding themselves for a while.

Once you cut off the add functionality, though, you could also add a routine that, if access is denied, offers to email you their credentials for inclusion. (via net send or whatever.)

Just a thought. :)

Matt, i dont have the Cmd facility on my machine (admin only) and i have no idea how our servers are set up, i did try sending "Net view" to my machines desktop but to no avail, nice suggestion with the cdo but there is that problem of manually inputting the captured data into a file.

Ken, your suggestion sounds great, thats the mix of the both things i wanted....only able to use the workbook in its network location, and only the users specified at the specified machines, is this how i would do that?

Code:

---

Sub CollectNames()
    Dim CN, UN
    Dim nRow, nRow1 As Long
    nRow = Sheets("Sheet1").Range("A65336").End(xlUp).Row + 1
    nRow1 = Sheets("Sheet1").Range("B65336").End(xlUp).Row + 1
    Set CN = Environ("ComputerName")
    Set UN = Environ("UserName")
    Workbooks.Open ("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    nRow = UN
    nRow1 = CN
    Workbook.Save
    Workbooks("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls").Close
End Sub

---

i get a type mismatch error with Set Cn...etc could you explain why?. As i have said previously im still learning along the way and sometimes its a little difficult to decipher what you guys put together...but as ever i'm trying!

"Very" my mum says!

Regards,
Simon

Hi Simon,

Your code is close. CN and UN are both variant strings in your case, and you're trying to set them as objects. If you want to run your code, you'd dim them as strings and just assign the values with them. No set statements:

Code:

---

Sub CollectNames()
    Dim CN As String, UN As String
    Dim nRow As Long, nRow1 As Long
    nRow = Sheets("Sheet1").Range("A65336").End(xlUp).Row + 1
    nRow1 = Sheets("Sheet1").Range("B65336").End(xlUp).Row + 1
    CN = Environ("ComputerName")
    UN = Environ("UserName")
    Workbooks.Open ("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    Workbooks("Names Test.xls").Worksheets("Sheet1").Range("A" & nRow) = UN
    Workbooks("Names Test.xls").Worksheets("Sheet1").Range("A" & nRow1) = CN
    Workbook.Save
    Workbooks("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls").Close
End Sub

---

I didn't test the above, but it shoudl work. Personally, though I'd go with something like this. Same functionality, but looks a little more elegant. :)

Code:

---

Sub CollectNames()
    Dim wbDB As Workbook
    Set wbDB = Workbooks.Open("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    With wbDB
        With .Worksheets("Sheet1")
            With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Value = Environ("ComputerName")
                .Offset(0, 1).Value = Environ("Username")
            End With
        End With
    .Close savechanges:=True
    End With
End Sub

---

HTH,

Thanks Ken, very neat and a lot easier to read than mine!, i have put the code in to a workbook on the network that users dump raw data into, the beauty of this is only the real users would use this (so i can capture them), my analytical workbook is the one i want to protect to only use on the network and only by people on a list, what i intend to do is put all the names captured on a worksheet in the analytical workbook so that the code will Vlookup or something like that the name of the user and either allow or deny working access to the workbook. Did that make sense? i currently have your code for checking for authr.txt in the workbook but it is in the workbook folder, if i use thisworkbook.path and the user copies the folder for use at home will it defeat the security your code adds ? i.e ThisWorkBook.Path would then be C:\.

Thanks for your continued support i this Ken, i do understand that it is probably getting annoying for you now, because i know i get annoyed at work at people who dont "get" what i see as simple!

Regards,
Simon

Quote:

---

Originally Posted by Simon Lloyd

... i do understand that it is probably getting annoying for you now, because i know i get annoyed at work at people who dont "get" what i see as simple!

---

Oh, hardly. This is interesting, and I think you're doing fine with it. :)

Quote:

---

Originally Posted by Simon Lloyd

...what i intend to do is put all the names captured on a worksheet in the analytical workbook so that the code will Vlookup or something like that the name of the user and either allow or deny working access to the workbook. Did that make sense?

---

Yes, but you may want to consider using ADO to connect to the workbook and just run an SQL (Select) query to check the current computer/user name. Means that you don't have to open the workbook in the Excel UI to levearge the vlookup. We can help you with that too.

Quote:

---

Originally Posted by Simon Lloyd

i currently have your code for checking for authr.txt in the workbook but it is in the workbook folder, if i use thisworkbook.path and the user copies the folder for use at home will it defeat the security your code adds ?

---

Yes, it would. That's why I recommended using a different directory that the user wouldn't necessarily see. Now... if you are also checking their Machine name, though, then the probably would get denied, as their machine name wouldn't necessarily match.

Basically, what I'm looking at is a 3 part test. If they meet all of the criteria (Username and Computername match and the workbook is there), then let them access the file. Make sure you keep a backup of what you have, too, as you don't want someone overwriting it with an old version. (You may want to create a timestamped backup at every opening or something so you can restore if this happens.)

Firstly Ken is there anyway you can mark this thread active rather than solved because it seems that i have perpetuated the lines of questioning?

I have never explored ADO or CDO or any other "DO"! and i thought SQL was SQL Server which was something different. Right now i (or should i say we!) check for the existence of the folder, the existence of the file, the authr.txt and with the collection code collect all the machine names and usernames.........Question, is it possible that when collecting 'names' we check for entry already made if it exists then dont collect the info?, it's probably me being highly lazy and not wanting to filter all the captures manually.

For my checking of a list i would probably have done

Code:

---


For Each Mycell in rng
    If Mycell.value = CN AND Mycell.OffSet(0,1)= UN Then
        Exit Sub
    Else
        Call CollectNames
    End If
Next

---

, I have also moved authr.txt to my own folder which is password protected (on the same drive) so i have trouble opening it from Excel because of this, it would have been nice to have it in a location they cant enter but i have moved it again to our Bin folder (for old files that we store but dont use!).

What i thought would be a small coding problem and insignificant is turning in to a monster but im enjoying learnin!, its strange the more i learn the more code i try to write!

Regards,
Simon

P.S Congrats on your new status.....one of the elite!

Here's all the code i have at the moment (sorry for the length!)

Code:

---


Public Sub TestFolderExistence()
    ' Author : Ken Puls (www.excelguru.ca)
    ' Macro Purpose: Test if directory exists
    If FileFolderExists("C:\Documents and Settings\vrtSzL04\Desktop\Test", "fldr") Then
        MsgBox "Folder exists!"
    Else
      MsgBox "Folder does not exist!"
    End If
End Sub
 
Public Sub TestFileExistence()
    ' Author : Ken Puls (www.excelguru.ca)
    ' Macro Purpose: Test if directory exists
    If FileFolderExists("C:\Documents and Settings\vrtSzL04\Desktop\Test\Names Test.xls", "xls") Then
        MsgBox "File exists!"
        ' the file exists, so it is on the company drive
    Else
        MsgBox "You Can Not Work On This File Away From Work, No Changes Will Be Saved!"
        ' the file does not exist, so they must be working remotely
    End If
    Save = False
    Application.Quit
End Sub
 
Sub CollectNames()
    Dim wbDB As Workbook
    Set wbDB = Workbooks.Open("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    With wbDB
        With .Worksheets("Sheet1")
            With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Value = Environ("ComputerName")
                .Offset(0, 1).Value = Environ("Username")
            End With
        End With
        .Close savechanges:=True
    End With
End Sub

---

This is the thisworkbook module

Code:

---


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If FileFolderExists(ThisWorkbook.Path & Application.PathSeparator & "auth.txt", "txt") Then
        ' The file exists, so it is on the company drive
    Else
        ' The file does not exist, so they must be working remotely
        MsgBox "Sorry, but you are working away from the office. " & vbNewLine & _
        "To prevent loss of other users work, this workbook" & vbNewLine & _
        "has been restricted for use only while attached" & vbNewLine & _
        "to our corporate network.", vbCritical + vbOKOnly, "Remote Access Error"
        Cancel = True
        Saved = True
    End If
End Sub
 
Private Sub Workbook_Open()
    Dim MyCell
    Dim Rng As Range
    Application.ScreenUpdating = False
    Workbooks.Open (ThisWorkbook.Path & "\" & "Name File.xls")
    Set Rng = Sheets("Sheet1").Range("A1:B300")
    For Each MyCell In Rng
        If MyCell.Value = CN And MyCell.Offset(0, 1) = UN Then
            Exit Sub
        Else
            Call CollectNames
        End If
    Next
    ActiveWorkbook.Close ("Name File.xls")
    Application.ScreenUpdating = True
End Sub

---

Regards,
Simon

Hi Simon,
Unsolved as requested.

Can you use the VBA button on your code, rather than the Code tabs in order for the formatting to be set.
Regards
MD

Hi Simon,

I unfortunately don't have time to look at this today. I'll try to have a peek tomorrow, if that's okay.

(And thank you. :) )

Thanks a lot Ken!, Matt sorry was just a slip as i was typing, and thanks for unsolving!
Regards,
Simon

Matt's the other one.
Regards
Malcolm

Try this code as part of a Workbook_Open event. This is a simple way to ensure it only runs on the company computers.

Code:

---


Sub Test_Enviroment
    Dim TheDomain
    TheDomain = Environ("UserDomain")
    If TheDomain = "YOUR_Active_Directory or Workgroup" Then
        GoTo ?Your label here?
    Else
        Call KillMe
    End If
End Sub
 
Sub KillMe()
    With ThisWorkbook
        .Saved = True
        .ChangeFileAccess Mode:=xlReadOnly
        Kill .FullName
        .Close False
    End With
End Sub

---

Unfortunately wouldn't work for me, Switchman. We don't have workgroups or domains set up at my office as we use a Novell server to manage our directory services.

Hi all, if i collect the machine names and the usernames in another workbook how would i check to see if both the names match current user and machine, ive cobbled something together but it just skips straight to msgbox "Goodbye!" even when i have stepped thru and watched it find both matches!, anyway here's my code also can i use the same code for only recording one instance of the names in my collection book instead of a For, If, Then, Next loop

Code:

---


Sub readclosedWB()
    Dim wb As Workbook
    Dim rng As Range
    Dim r, r1
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)
    With Sheets("Sheet1")
        r = Cells.Find(What:=Environ("computername"), After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        r1 = Cells.Find(What:=Environ("username"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        If r = Environ("computername") & r1 = Environ("username") Then
            MsgBox "Hello!"
            Exit Sub
        Else: MsgBox "Goodbye!"
            wb.Close False
            Set wb = Nothing
            Application.ScreenUpdating = True
        End If
    End With
End Sub

---

Regards,
Simon

I have amended the code as below, the only trouble is when it comes across the first correct machine name if it checks the offset if incorrect username goes to the close procedure, what i mean is it doesn't carry on searching through the list looking for both criteria to match, any ideas?

Code:

---

Sub readclosedWB()
    Dim wb As Workbook
    Dim rng As Range
    Dim r
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)
    With Sheets("Sheet1")
        r = Cells.Find(What:=Environ("computername"), After:=Range("A1"), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
        With ActiveWorkbook
            If ActiveCell = Environ("computername") And ActiveCell.Offset(0, 1) = Environ("username") Then
                MsgBox "Hello!" & ActiveCell.Address
                Exit Sub
            Else: MsgBox "Goodbye!"
                wb.Close False
                Set wb = Nothing
                Application.ScreenUpdating = True
            End If
        End With
    End With
End Sub

---

Regards,
Simon

Hi Simon,

I've mocked up something a little different for you. Use it or not, it's totally cool with me, but this is generally the approach that I would take.

What I'm doing here is storing the username and computername in a database, not an Excel file. We access it via ActiveX Data Objects (or ADO as it's commonly known.) You can actually use ADO to access Excel files, but I'm used to going the database route.

I've attached a sample database and and Excel file to demonstrate the usage. You'll need to do a couple of things with it though...

1) Unzip and place them somewhere. Just remember the path to "auth.mdb".
2) Open the Excel file. If you enable macros, you'll get a nice message saying you aren't authorized to use the file.
3) Go into the VBE and open up Module1. Near the top, look for the following line and modify the path to where your saved the database:

Code:

---

Public Const dbPath = "F:\Excel Projects in Process\auth.mdb"

---

4) Go to the ThisWorkbook module and uncomment the line that reads:

Code:

---

'        ThisWorkbook.Close savechanges:=False

---

5) Save the workbook and reopen it.

If you have Access, you should now be able to open the database and see your name and computer name in the tblAuth table.

Now, rename the database, and re-run the ThisWorkbook code. You should get kicked out.

Remember that the database does not need to be in the same directory as the workbook, but everyone will need access to it. The cool thing about this route is that most people won't think you are logging Excel data to a database file.

Now, a couple of important things... there is no error handling in these routines at all. You'll want to fix that before you release this in the wild. We can help you do that, but I wanted to give you something to get you started.

The workbook is currently set to log names (user and computer) to the database, so people could log multiple workstations. If that really is an issue, we'll have to modify something.

If you want to port this code to your workbook, I'd recommend exporting the class module and importing it into your existing workbook, as well as Module1. (you should probably rename that first though.) You will also need to set a reference in the VBE to the Microsoft ActiveX Data Object 2.x library as well.

Have a play and ask some questions. I'm sure you'll have a few. :)

Maybe change :

Code:

---

With Sheets("Sheet1")

---

with

Code:

---

With wb.sheets("Sheet1")

---

Not sure but maybe you do a check on the wrong workbook

Charlize

ps.

Code:

---

Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True

---

must it be

Code:

---

Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)

---

a ) at the end ?

Thanks for the response Charlize, the line you mentioned does have a ")" at the end you just needed to scroll right!, i changed the line as you suggested but it didnt make any difference to what i was trying to achieve.

Regards,
Simon

Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments (which helped a great deal) a couple of questions to start with (some may seem inane to you)
Questions:
1. whats the difference between a standard and class module?
2. what if the user doesn't have Access installed?
3. what does SQL stand for?
4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing
5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)
6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?
7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?

Once again thanks for your continued support!

Regards,
Simon

Quote:

---

Originally Posted by Simon Lloyd

Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments...

---

ADO essentially it can act as an interface to your database so that you can read the records, change them, etc... It actually allows you to do more, in my experience, to Access than you can through the Access user interface alone. The best way to learn is doing exactly what you're doing though. :)

Quote:

---

Originally Posted by Simon Lloyd

Questions:
1. whats the difference between a standard and class module?

---

A class module essentially acts as a blueprint for a new object. You can define your own objects, give them properties, methods, events, etc... Honestly, this doesn't actually need to be in a class module, but once you have a class module built, you can easily export it and reuse it in other projects. What you've got is a copy of one of mine, culled down to only use the specific methods and properties we need for this.

Quote:

---

Originally Posted by Simon Lloyd

2. what if the user doesn't have Access installed?

---

That's the beauty of ADO. It doesn't matter. I even have an example on my site of how to create a new Access database via ADO which will work if you don't have access on your PC. :)

Quote:

---

Originally Posted by Simon Lloyd

3. what does SQL stand for?

---

Structured Query Language. It's an extremely powerful language that allows you to return recordsets, create/manipulate database tables and more. It's used extensively in database applications.

Quote:

---

Originally Posted by Simon Lloyd

4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing

---

Excellent! That is exactly what is happening. We request a recordset from the database, convert it to an array, and then inspect it. The array conversion is not strictly necessary, but I do it to make sure I can just dump it into a worksheet should I want to do that. If an error occured right now (with no error handling), the error message would show, but you'd never see the data in Excel. (It will only show up in a worksheet if you tell it to go there.)

Quote:

---

Originally Posted by Simon Lloyd

5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)

---

We'll need to adjust the code a bit to do that. I'll see what I can do for you there. ;)

Quote:

---

Originally Posted by Simon Lloyd

6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?

---

Sot of correct. You could open the workbook with macros disabled, comment the Close line in the code, save it and reopen it. This might be a good reason to lock your VB Project if you have any other VBA'ers in your organization. For reference, the same is also true if you were using an Excel workbook as yoru database.

Quote:

---

Originally Posted by Simon Lloyd

7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?

---

It won't cause any issues with the code, as it is querying the database for info. While the database can be configured for exclusive use, that is extra work that is not necessary, and therefore has not been done.

Quote:

---

Originally Posted by Simon Lloyd

Once again thanks for your continued support!

---

Happy to. I'll get back to you with a mod for the user checking.

Ken your title is well deserved - clear - concise explanations and responses! one question i missed out (sorry!) as we are networked (in excess of 800 machines) naturally none of us have administrator rights, therefore if a machine does not have ActiveX (as sometimes when using the web you are asked to allow ActiveX) and we are trying to use that to manipulate the db will we recieve an error or would the code just hang?, also if ActiveX is installed on the machine but is set to "ask to allow ActiveX" would we have similar problems? i had the urge to write an If statement to try to capture this but on viewing the code in the class module it looks like it would be the ADODB Connection that would have to be handled if this was the case, i wasn't sure of the language around that is it vb, vba, sql..etc.

There are only 2 other VBA'ers here that i know of and i hate to admit it but i (up until now) felt that i was the more experienced of us!

Regards,
Simon

Quote:

---

Originally Posted by Simon Lloyd

Ken your title is well deserved - clear - concise explanations and responses!

---

LOL! Well thanks. :)

Quote:

---

Originally Posted by Simon Lloyd

one question i missed out (sorry!) as we are networked (in excess of 800 machines) naturally none of us have administrator rights, therefore if a machine does not have ActiveX (as sometimes when using the web you are asked to allow ActiveX) and we are trying to use that to manipulate the db will we recieve an error or would the code just hang?

---

Good question. I don't believe that there should be an issue here, as I've never been presented with an ActiveX prompt when doing this. The only way to know for sure, though, is to test it out.

Quote:

---

Originally Posted by Simon Lloyd

i had the urge to write an If statement to try to capture this but on viewing the code in the class module it looks like it would be the ADODB Connection that would have to be handled if this was the case, i wasn't sure of the language around that is it vb, vba, sql..etc.

---

Well, if there was an issue, it would be around the ADODB connection yes. Don't overthink the languages in use here, though.

SQL is a very small portion of what we're doing. It's simply the query to extract data from the database. A one liner that is sent to the database via the ADO connection.

The ADO is just a class that can be leveraged by VB, VBA or other languages. While it makes use of arguments that need to be provided correctly, it's not really a languae per se.

As for the VB vs VBA, the two intermix. VBA is an application specific flavour of VB. When we're programming, we constantly intermix VB and VBA. If you look up items in the help files, you'll see some marked "Visual Basic".

Hi....again!, ive just been reading about QI queryinterface and the pro's and cons of calling it Early Bound or Late Bound, where it explains that if we use Early binding we pass the object to the compiler first where excel is able to work out all the pointers from the class module first and so speeding things up a little and late bound seems to be the opposite, i know you haven't specified any binding properties so im confident that we dont need them, i was just thinking of the scenario of when my workbook is around 5meg and my database in excess of 2000 entries what the amount of time would be for the code execution and whether specifying Early binding would make a difference, or am i barking up the wrong tree?

Code:

---


' LATE binding
Dim XLObj As Object
Set XLObj = CreateObject("Excel.Application")
' same thing with GetObject instead of CreateObject
' EARLY binding
Dim XLObj As Excel.Application
Set XLObj = New Excel.Application

---

regards,
Simon
P.S this thread is becoming monsterous! lol

Hi Simon,

The process of setting a reference to the library is actually early binding. You'll notice in the code that's in the file I gave you that we have:

Code:

---

Dim conn as ADODB.Connection

---

How long it will take, I'm not actually sure. It will slow down a bit, for sure, but 2000 entries shouldn't take a noticeable amount of time to search through, I wouldn't think. 20,000 maybe. :)