I searched the *scripting* newsgroup for "Mac address" and found some code to
borrow. The script I found worked with my version of windows (XP home). I'm
not sure if nbtstat is available on all versions of windows.
Option Explicit
Public OKMACAddr As Boolean
Function GetMACAddress()
Dim Net As Object
Dim SH As Object
Dim FSO As Object
Dim TS As Object
Dim Data As String
Dim MACAddress As String
Set Net = CreateObject("wscript.network")
Set SH = CreateObject("wscript.shell")
SH.Run "%comspec% /c nbtstat -a " _
& Net.computername & " > c:\nbtstat.txt", 0, True
Set SH = Nothing
Set Net = Nothing
Set FSO = CreateObject("scripting.filesystemobject")
Set TS = FSO.opentextfile("c:\nbtstat.txt")
MACAddress = ""
Do While Not TS.AtEndOfStream
Data = UCase(Trim(TS.readline))
If InStr(Data, "MAC ADDRESS") Then
MACAddress = Trim(Split(Data, "=")(1))
Exit Do
End If
Loop
TS.Close
Set TS = Nothing
FSO.deletefile "c:\nbtstat.txt"
Set FSO = Nothing
GetMACAddress = MACAddress
End Function
Sub auto_open()
Dim myMACAddr As String
myMACAddr = Application.Clean(GetMACAddress)
Select Case UCase(myMACAddr)
Case Is = "##-##-##-##-##-##", "##-##-##-##-##-##"
'ok
OKMACAddr = True
Case Else
OKMACAddr = False
MsgBox "Not authorized to use this workbook!"
'uncomment this line, save your work before you test it.
'thisworkbook.Close savechanges:=false
End Select
End Sub
But if users open your workbook with macros disabled (or stop the auto_open from
firing), then this won't help.
One way around it is to create a userdefined function that checks to make sure
that they're valid users. Then you can sprinkle your UDF into formulas that
will blow up if they're not authorized.
A really basic example. Say you have some formulas that return numbers:
=sum(a1:a99)
Change it to look like:
=sum(a1:a99)+myfunc()
And put this a general module in that project.
Function myFunc()
If OKMACAddr Then
myFunc = 0
Else
myFunc = CVErr(xlErrRef)
End If
End Function
You could even make it so that that function actually does something important.
(You could have users who are smart enough to figure out what that function
"adds" to the formula.)
Ps. Even if you do all this, you'll have to protect the project.
Inside the VBE:
Tools|vbaproject properties|protection tab
But this protection is can be broken pretty easily--but it'll stop most.
==========
ps. if your version of windows doesn't have nbtstat, then may be able to use an
alternate method.
This also displayed that MAC address for me (winXP home):
Option Explicit
Sub testme01()
Dim strComputer As String
Dim objWMIService As Object
Dim colAdapters As Object
Dim objAdapter As Object
strComputer = "."
Set objWMIService = GetObject _
("winmgmts:" & "!\\" & strComputer & "\root\cimv2")
Set colAdapters = objWMIService.ExecQuery _
("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objAdapter In colAdapters
MsgBox "Physical address: " & objAdapter.MACAddress
Next objAdapter
End Sub
But I don't know when WMI became available.
====
You may want to post back to one of the Scripting newsgroups and explain what
you want. And include what version of Windows you're running. (Or even search
google!)
==========
You could even use the computer name (instead of MAC Address):
Option Explicit
Sub testme()
Dim WSH As Object
'Dim WSH As IWshRuntimeLibrary.WshNetwork
Set WSH = CreateObject("WScript.network")
'Set WSH = New IWshRuntimeLibrary.WshNetwork
MsgBox WSH.ComputerName
MsgBox WSH.UserName
End Sub
sparx wrote:
>
> Please can anybody help - I want to stop people running excel files of
> mine on their computers on my network - so i am asking if somebody
> knows of a VBA code that when they open my files, sheet1 will display
> their MAC address of their PC so I can make a formula that compares my
> MAC address with a password that will stop formula's working within my
> files if the MAC addresses dont match.
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=388084
--
Dave Peterson
Similar Threads
