Populating IP Address & Username of the user in Excel

[hdevadiga]

Hi Friend,

I have an query regards to populating the IP Address and username of the user who is logged in in Excel, i wrote code to display the cell changed,old value,new value,time of change,date of change.. I want the ip address and user id should get display on F1 and G1, by that i can track the user with user name and ip address... I wrote the code below:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
Dim vOldVal
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet3
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE")
End If
With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
.AddComment.Text Text:="OzGrid.com:" & Chr(10) & "" & Chr(10) & "Bold values are the results of formulas"
End If
.Value = Target
.Font.Bold = bBold
End With
.Offset(0, 3) = Time
.Offset(0, 4) = Date
End With
.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With
vOldVal = vbNullString
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
vOldVal = Target
End Sub

[shrivallabha]

For username, did you try:

Range("G1").value = Application.UserName

[Kenneth Hobs]

Please paste your vba code between vba code tags. Most forums use code code tags for such but the vba code tags here is one of the neat features.

Obviously, the Application.Username gets the username from the Excel application. I suspect that you would prefer the computer username. Use Environ("username") to get that environment variable.

You can use Environ("computername") to get the name of the local computer. Tracert can be used to get a string and parse out the IP. SNB can probably give you a one-liner to do that. Alternatively, I would use an API method such as the one here. http://www.source-code.biz/snippets/vbasic/8.htm

Don't be afraid to use API routines. While they look complicated, that does not mean that they are hard to use or not fast.

[snb]

@KH

Your fears turn into reality ;)
At least in Windows XP this code will show an IP-address.



Sub IPtest()
MsgBox Split(Join(Filter(Split(CreateObject("WScript.Shell").exec("cmd /c ipconfig").StdOut.readall, vbCrLf), "IP-")), ":")(1), , "snb IP-address"
End Sub


But to use API's can be much simpler than illustrated in the link KH provided:



Private Declare Function GetIpAddrTable_API Lib "IpHlpApi" Alias "GetIpAddrTable" (pIPAddrTable As Any, pdwSize As Long, ByVal bOrder As Long) As Long

Public Function snb()
Dim sn(511) As Byte
If GetIpAddrTable_API(sn(0), 512, 1) = 0 Then
For j = 1 To sn(0)
Y = 4 + 24 * (j - 1)
snb = snb & vbLf & Join(Array(sn(Y), sn(Y + 1), sn(Y + 2), sn(Y + 3)), ".")
Next
End If
End Function

Sub tst()
MsgBox snb
End Sub

[Kenneth Hobs]

Here is a modification of snb's code that works for me.


Sub snb()
MsgBox Split(Filter(Split(CreateObject("WScript.Shell").exec("cmd /c ipconfig").StdOut.readall, vbCrLf), "IP")(1), ":")(1), vbInformation, "snb"
End Sub
Since I think it a compliment to build on others code, I added some to snb's API modified code as I like to use Option Explicit.


Option Explicit

Private Declare Function GetIpAddrTable_API Lib "IpHlpApi" Alias "GetIpAddrTable" (pIPAddrTable As Any, pdwSize As Long, ByVal bOrder As Long) As Long

Public Function snb() As String
Dim sn(511) As Byte, j As Integer, y As Integer
If GetIpAddrTable_API(sn(0), 512, 1) = 0 Then
For j = 1 To sn(0)
y = 4 + 24 * (j - 1)
snb = snb & vbLf & Join(Array(sn(y), sn(y + 1), sn(y + 2), sn(y + 3)), ".")
Next
End If
End Function

Sub tst()
MsgBox snb
MsgBox Split(snb, vbLf)(2)
End Sub

[snb]

Declaring defaults ? :)
If you can't resist, I'd prefer:



Function snb() As String

[hdevadiga]

Hi All,

Thanks for reply, but my friends i want ip address should be come when i open work book and put user id and password authentication... that time in one of the sheet on cell ("F1") i should see the ip address of the computer on which the file is opened & edited....

I used the below code to get these information in that one of the sheet...


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
Dim vOldVal
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet3
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE")
End If
With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
.AddComment.Text Text:="OzGrid.com:" & Chr(10) & "" & Chr(10) & "Bold values are the results of formulas"
End If
.Value = Target
.Font.Bold = bBold
End With
.Offset(0, 3) = Time
.Offset(0, 4) = Date
End With
.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With
vOldVal = vbNullString
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
vOldVal = Target
End Sub

Regards,
Hari

[snb]

@KH

It's primarily dependent on how many IP-adresses your system contains.
My code assumed only 1 IP-address, your code assumed several, I think we better use assumption void code:



sub IP_snb()
MsgBox Join(Filter(Split(CreateObject("WScript.Shell").exec("cmd /c ipconfig").StdOut.readall, vbCrLf), "IP-a"), vbLf), 64, "IP-snb"
end sub

Thanks for the lesson :)

[Kenneth Hobs]

The "-a" in the filter string "IP-a" does not exist for me so your last function snb does not return anything. It may be a language deal.

This also works for me:

MsgBox Split(Join(Filter(Split(CreateObject("WScript.Shell").exec("cmd /c ipconfig").StdOut.readall, vbCrLf), "IP"), vbLf), ":")(1), _
vbInformation, "IP-snb"

Hdevadiga, you are much better off using Environ("computername") than adding an IP in my opinion. Try working up an example workbook and show what you want manually. There is no way to fully secure an Excel file. You can do a few things to make it more secure from the casual user.

[snb]

@KH

the lines that contain an IP-address the line begins with (in Dutch)

'IP-adres

so I supposed in your Englisch version to appear:

'IP-address

So I thought 'IP-a' would single out those lines.