Where to check authorisation of computer and username

[Smiley]

Good afternoon,

Is it possible to have a checking on the computer name and user name when
certain worksheets are being accessed. i.e. I only want certain work only
been done by certain person and on certain machine.

If that is possible where can I place this security check and how. Some
coding would be very helpful as I am totally new to programming.

Regards,

Smiley

 

[Bob Phillips]

Environ("Username")

Environ("Computername")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Insert the following part of code into a general code module:

==============
Public Declare Function gethostname Lib "wsock32.dll" (ByVal Name As String,
ByVal namelen As Long) As Long
==============

and insert this part into the module of any worksheet, this is just an
example:

==============
Private Sub Worksheet_Activate()
Dim LocalHostName As String ' the computer's domain name
Dim UserName As String ' the name of a user
Dim WorkSheetName As String ' the name of a sheet

LocalHostName = Space(256)
gethostname LocalHostName, 256
LocalHostName = Left(LocalHostName, InStr(LocalHostName, vbNullChar) - 1)

UserName = Application.UserName
WorkSheetName = ThisWorkbook.ActiveSheet.Name

MsgBox "Computer name is:" & vbLf & vbTab & """" & LocalHostName & """"
& vbLf & vbLf _
& "User name is:" & vbLf & vbTab & """" & UserName & """" & vbLf &
vbLf _
& "You are trying to access the worksheet:" & vbLf & vbTab & """" &
WorkSheetName & """"

End Sub
==============

Now, if you try to activate the worksheet with the code an appropriate
message appears. This is an example with all necessary functions, which help
you to check on the computer name and user name.

Regards, Vladimir

 

[Smiley]

Hi there,

I must be doing something very silly. I just cannot get it works.

The following is what I have done.

Open a new workbook and on sheet 1 then I go to Tools --> Macro --> Visual
Basic Editor
Then on Insert --> Module, I copied
Public Declare Function gethostname Lib "wsock32.dll" (ByVal Name As String,
ByVal namelen As Long) As Long

Then again on Module, I copied the Private Sub Worksheet_activate

Close the workbook and reopen it, I got a popup which gave me options of
disable Marco, Enable Marco and More Info. I clicked on the enable Marco but
nothing happened.

Am I miles out ?

When I opened up the workbook, I cannot find the public declare function or
the private sub. Where can I found them. Also shall I using Class module ? I
am very confuse on the module, class modules etc. Would you nudge me in the
right direction. Many thanks.

Smiley

 

[Guest]

Good Morning!

You have done almost everything correct. I must have written not enough
clear. The second part of the code must have been inserted into a sheet’s
module; they are generally called in VBA Editor as “Sheet1(Sheet1)†and so
on.
Moreover, Bob Phillips has given a witty solution, which is shorter; so
there is a new example with use of a part of his tip.
Put this code into the worksheet’s module “Sheet1(Sheet1)â€:

Private Sub Worksheet_Activate()
Dim LocalHostName As String ' the computer's domain name
Dim UserName As String ' the name of a user
Dim WorkSheetName As String ' the name of a sheet

LocalHostName = Environ("Computername")

UserName = Application.UserName
WorkSheetName = ThisWorkbook.ActiveSheet.Name

MsgBox "Computer name is:" & vbLf & vbTab & """" & LocalHostName & """"
& vbLf & vbLf _
& "User name is:" & vbLf & vbTab & """" & UserName & """" & vbLf &
vbLf _
& "You are trying to access the worksheet:" & vbLf & vbTab & """" &
WorkSheetName & """"

End Sub

Here is an exaple to download:
http://www.vbapro.ru/downloads/CheckOnNameAndHost.xls

Vladimir

 

[NickHK]

IIRC, Application.Username need not be the same as Bob's suggestion.

Application.Username is set when you install Office and can be anything. It
may well be the person who installed the software, rather than the current
user.
Instead, use:
Environ("Username")

Whilst this can also be changed by someone playing with the Environment
variables, it is less likely.
There's always the API route also:
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal
lpBuffer As String, nSize As Long) As Long

Similar arguments apply to "Computername".

NickHK

 

[Guest]

Thank you NickHK, very reasonable arguments.

2Smiley
please find a new version as well as the updated example

declarations in a "simple" Module
==================
Declare Function GetComputerName Lib "kernel32.dll" Alias "GetComputerNameA"
(ByVal lpBuffer As String, nSize As Long) As Long
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal
lpBuffer As String, nSize As Long) As Long
==================


code in the "Sheet1" module
==================
Private Sub Worksheet_Activate()
Dim CompName As String ' the name of a computer
Dim UserName As String ' the name of a user
Dim WorkSheetName As String ' the name of a sheet


CompName = Space(255)
GetComputerName CompName, 255
CompName = Left(CompName, InStr(CompName, vbNullChar) - 1)


UserName = Space(255)
GetUserName UserName, 255
UserName = Left(UserName, InStr(UserName, vbNullChar) - 1)


WorkSheetName = ThisWorkbook.ActiveSheet.Name


MsgBox "Computer name is:" & vbLf & vbTab & """" & CompName & """" &
vbLf & vbLf _
& "User name is:" & vbLf & vbTab & """" & UserName & """" & vbLf &
vbLf _
& "You are trying to access the worksheet:" & vbLf & vbTab & """" &
WorkSheetName & """"

End Sub
==================

for testing just click on "sheet2" tag, then on on "sheet1"

 

[Smiley]

Morning Vladimir,

Your instructions were clear but I just don't know my way in excel
programming and don't know where 'things' are.

I am not able to locate where the public declaration and the private sub
which I have created. How do I find them please ? I managed to find the
View --> browse object but were lost, i.e. expect to find public declaration
and the private sub but cannot locate them or may be there were default
names associated with them and I don't know what they were since I never
save them with any names.

With regard to the worksheet's module, yesterday, I did tried to do
something like that but just didn't know how to. How can I specify a module
is associate with a particular worksheet and a module is associate with the
whole workbook please ? When I got this sorted, I might be able to find my
way a bit better.

TIA for your patience and help.

Smiley

 

[Guest]

Hallo Smiley,

Sorry for the delay in my reply.
All your VBA code is situated in modules of some workbook. It could be
either Personal.xls book, which automatically opens every time Excel starts,
or some your custom books.
Every workbook contains VBA projects with modules which can be of the
following types:
A) Worksheet module – module intrinsically associated with appropriate
sheets of the workbook
B) ThisWorkbook - associated with the workbook
Modules of these types allow working easily with the events of their
holders: workbooks and worksheets. These modules are always available.

C) Modules – general codes modules
D) Forms – your user forms
E) Class modules
Modules of these three types you have to create by yourself, they do not
exist by default.

You can see all the modules in a tree view in the Project Explorer window
(Ctrl+R). And this is the best way to navigate your projects and to specify
with which worksheet and workbook particular module is associated.

Regards
Vladimir

 

[Guest]

Hallo Smiley,

Sorry for the delay in my reply.
All your VBA code is situated in modules of some workbook. It could be
either Personal.xls book, which automatically opens every time Excel starts,
or some your custom books.
Every workbook contains VBA projects with modules which can be of the
following types:
A) Worksheet module – module intrinsically associated with appropriate
sheets of the workbook
B) ThisWorkbook - associated with the workbook
Modules of these types allow working easily with the events of their
holders: workbooks and worksheets. These modules are always available.

C) Modules – general codes modules
D) Forms – your user forms
E) Class modules
Modules of these three types you have to create by yourself, they do not
exist by default.

You can see all the modules in a tree view in the Project Explorer window
(Ctrl+R). And this is the best way to navigate your projects and to specify
with which worksheet and workbook particular module is associated.

Please take a look at the example I have made for you
http://www.vbapro.ru/downloads/CheckOnNameAndHost.xls

Regards
Vladimir

 

[Smiley]

Hi everyone,

Thank you so much for your help. Managed to finger it out at weekend.
Especially with Vladimir's latest email on explaining how to get to
different type of modules and using the project explorer.

Many thanks again,

Have a good week.

Smiley