Worksheet Area 3

[mickeyb]

Hello !!

A couple of quick questions if I may relating to Excel 97

1) Does anyone know how to return the NT userid using Excel VBA?

2) I seem to remember that it is possible to restrict the area of a worksheet. So that you can not select outside the area you specify. Does anyone know the code/keyword for this action.

Thanks
Mike

 

[cactus13]

I think there is a much easier way for the problem you are trying to solve. From the questions you've put here I make up that you try to protect your worksheet depending on the user.

Excel has a very good option for that already in it's menu. Under the Tools menu you can protect the worksheet so values in the cells can not be changed using the option Protection. However it's possible to specify cells which can always be changed by specifying the cell to be not locked. To do so select the cell, right click your mouse. Choose Format cells and select the Protection sheet. Deselect the option Locked before protecting your worksheet.

If you want to grant certain users access to the worksheet give them the password whit which they can unlock the sheet.

Now the answers to your questions...

1) Does anyone know how to return the NT userid using Excel VBA?

I think the interface of VBA is not suited for that. You are working within the office set of programs and therefore no function exists to get information from the Windows (NT) enviroment.

What you could do is use the following line to detect who is working with Excel, because I suppose that you want to lock parts of the workbook depending on the person who logged in.

Application.Username

This returns the name which you specified as username on the Generalk sheet under the Options in the Tools menu.

2) I seem to remember that it is possible to restrict the area of a worksheet. So that you can not select outside the area you specify. Does anyone know the code/keyword for this action.

I don't know if you can disallow some one to select certain cells which you don't want to. However with the method described above you can lock cells. To prevent them from even selecting the files just hide the empty rows and colums that you don't want to use before protecting the worksheet.

Good luck!

Jonathan

 

[Mala]

If you wish to restrict the user to range A1:H25 in a sheet called 'MySheet', you'd use the following VBA line in the concerned module:

Sheets("MySheet&quot.Activate
Activesheet.ScrollArea="A1:H25"

Check out the command - I'm writing from memory.

 

[dsi]

You can get the user's NT login name using API. You can also get the computer's name as well.

Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Sub Main()
    Dim sUser As String
    Dim sComputer As String
    Dim lpBuff As String * 1024

    'Get the Login User Name
    GetUserName lpBuff, Len(lpBuff)
    sUser = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
    lpBuff = ""
    
    'Get the Computer Name
    GetComputerName lpBuff, Len(lpBuff)
    sComputer = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
    lpBuff = ""

    MsgBox "Login User: " & sUser & vbCrLf & _
           "Computer Name: " & sComputer
    
    End
End Sub

Hope this helps! DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[mhamed]

dsi:

I really found your reply very helpful. Do you know how to get the machine serial or ID number?

 

[dsi]

Sure do. The result is represented as a Long. One thing to note is that not all serial numbers are unique. We purchased 10 systems that all came in with the same serial number, which was probably due to drive ghosting.

Option Explicit
Declare Function GetVolumeInformation Lib "kernel32" _
                 Alias "GetVolumeInformationA" _
                 (ByVal lpRootPathName As String, _
                 ByVal lpVolumeNameBuffer As String, _
                 ByVal nVolumeNameSize As Long, _
                 lpVolumeSerialNumber As Long, _
                 lpMaximumComponentLength As Long, _
                 lpFileSystemFlags As Long, _
                 ByVal lpFileSystemNameBuffer As String, _
                 ByVal nFileSystemNameSize As Long) As Long

Sub Main()
    Dim lpRootPathName As String
    Dim lpVolumeNameBuffer As String
    Dim nVolumeNameSize As Long
    Dim lpVolumeSerialNumber As Long
    Dim lpMaximumComponentLength As Long
    Dim lpFileSystemFlags As Long
    Dim lpFileSystemNameBuffer As String
    Dim nFileSystemNameSize As Long
    Dim ReturnVal As Long
    lpRootPathName = "C:\"
    ReturnVal = GetVolumeInformation(lpRootPathName, _
                                    lpVolumeNameBuffer, _
                                    nVolumeNameSize, _
                                    lpVolumeSerialNumber, _
                                    lpMaximumComponentLength, _
                                    lpFileSystemFlags, _
                                    lpFileSystemNameBuffer, _
                                    nFileSystemNameSize)
    MsgBox "(" & lpVolumeSerialNumber & ")"
    End
End Sub

Hope this helps... DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[dsi]

P.S.
There is another way to get the login user, but it only works under NT/2000 because the username environment variable is not defined under 95/98. (Not sure about XP)

MsgBox Environ("USERNAME")

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[FaneDuru]

The answer for the second question:

Worksheets(2).ScrollArea = "A1:F10"

 

[FaneDuru]

Sorry, I forgot !!!
To eliminate the restriction you will use:
Worksheets(2).ScrollArea = ""