User name code problem...
Posted by Jack on December 11, 2001 11:28 PM
Help sought
In an Excel workbook I have the following codes:
Function User()
User = Application.UserName
End Function
=IF(ISBLANK(A2),"",User() &" "&TEXT(NOW(),"m/d/yyyy-hh:mm AM/PM"))
The function code is stored in a module and the IF Statement code is stored in cell B2:B50. The sheet contains a header row. The idea behind the code is that the user enters date into cell A2 for example and thereafter their name and date and time of entry pops up in cell B2. This scenario is repeated each time an entry is made in column A:A.
Though this works well there is a problem. The workbook is used by several users. When a new user enters data into a cell in A:A, not only does the next cell in B:B change to show their details but all the used cells in B:B change to show their details. I had hoped that the code would only be applicable to each row and user but it is not. Any assistance with code to re-work this problem would be welcomed.
jackjoyce@listenup.co.uk
Posted by Mike C on December 12, 2001 5:42 AM
Try this
One of the other people here can probably improve on this and we'll both learn something, but this does what you want.
Create a Worksheet Change event procedure defined as follows:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Chged As Range
For Each Chged In Target
If (Chged.Column = 1) Then
If (Chged.Row > 1) Then
If (Chged = "") Then
Chged.Offset(, 1) = ""
Else
Chged.Offset(, 1) = Application.UserName & " " & Format(Now(), "m/d/yyyy-hh:mm AM/PM")
End If
End If
End If
Next
End Sub
There is no need to put any formulas into any worksheet cell. Any change to cells A2:An will generate the desired entry in B2:Bn, but only for the An cells that were changed. This also handles the case where a range of cells is changed at one time.
Posted by Jack on December 14, 2001 9:58 AM
Cheers
I am trying to get a MIN value for a set of specific cells, (not a range). I am trying to get the MIN value of cells g3,k3,o3,s3,w3. Do not want the value of the cells in between those cells.
How can I do this? I need it urgently.
Any advice is greatly appreciated.
