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

Environ("Username")

Environ("Computername")

--
HTH

Bob

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

"Smiley" <(E-Mail Removed)> wrote in message
news:f4143v$2qd$1$(E-Mail Removed)...
> 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
>

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" wrote:

> 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
>
>
>

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


"vbapro" <(E-Mail Removed)> wrote in message
news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>>

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

"Smiley" wrote:

> 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
>
>
> "vbapro" <(E-Mail Removed)> wrote in message
> news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>

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

"vbapro" <(E-Mail Removed)> wrote in message
news:457B7607-F9E2-4C05-81F0-(E-Mail Removed)...
> 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
>
> "Smiley" wrote:
>
> > 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
> >
> >
> > "vbapro" <(E-Mail Removed)> wrote in message
> > news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >>
> > >>
> >
> >
> >

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"


"NickHK" wrote:

> 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
>
> "vbapro" <(E-Mail Removed)> wrote in message
> news:457B7607-F9E2-4C05-81F0-(E-Mail Removed)...
> > 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
> >
> > "Smiley" wrote:
> >
> > > 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
> > >
> > >
> > > "vbapro" <(E-Mail Removed)> wrote in message
> > > news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
> > > > 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" wrote:
> > > >
> > > >> 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
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>

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



"vbapro" <(E-Mail Removed)> wrote in message
news:457B7607-F9E2-4C05-81F0-(E-Mail Removed)...
> 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
>
> "Smiley" wrote:
>
>> 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
>>
>>
>> "vbapro" <(E-Mail Removed)> wrote in message
>> news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>>

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

"Smiley" wrote:

> 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
>
>
>
> "vbapro" <(E-Mail Removed)> wrote in message
> news:457B7607-F9E2-4C05-81F0-(E-Mail Removed)...
> > 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
> >
> > "Smiley" wrote:
> >
> >> 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
> >>
> >>
> >> "vbapro" <(E-Mail Removed)> wrote in message
> >> news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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" wrote:

> 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
>
>
>
> "vbapro" <(E-Mail Removed)> wrote in message
> news:457B7607-F9E2-4C05-81F0-(E-Mail Removed)...
> > 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
> >
> > "Smiley" wrote:
> >
> >> 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
> >>
> >>
> >> "vbapro" <(E-Mail Removed)> wrote in message
> >> news:74F4AC55-80DC-46ED-A751-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>