PDA

View Full Version : Activiate function when another cell is triggered from a function such as doubleclick



valendj
11-11-2011, 08:25 AM
Hi,
Hi,
I am by far not a programmer. I ordered a VB book and hopefully will reduce my questions and be able to pay back all the help I received here. So before asking questions I searched VBax for similar questions and I found the code below. What I need is to be able to run this code when a certain cell is field out it will trigger this event. I will also attach the workbook I am working on. Inside the workbook Column M4:1000 is double click function that time stamps the sheet and saves the workbook and when this happens I would like column N and O be auto filed out via the function I included below. In workbook attached CG Number = Full Computer name(3) and Inspector = Username(2). I hope this makes sense. If not please let me know and I will try to explain better. Thanks again for any help!


Option Explicit

Function GetName(Optional NameType As String) As String
'Function purpose: To return the following names:
'Defaults to MS Office username if no parameter entered
'
'Formula should be entered as =GetName([param])
'
'For Name of Type Enter Text OR Enter #
'MS Office User Name "Office" 1 (or leave blank)
'Windows User Name "Windows" 2
'Computer Name "Computer" 3

'Force application to recalculate when necessary. If this
'function is only called from other VBA procedures, this
'section can be eliminated. (Req'd for cell use)
Application.Volatile

'Set value to Office if no parameter entered
If Len(NameType) = 0 Then NameType = "OFFICE"

'Identify parameter, assign result to GetName, and return
'error if invalid
Select Case UCase(NameType)
Case Is = "OFFICE", "1"
GetName = Application.UserName
Exit Function
Case Is = "WINDOWS", "2"
GetName = Environ("UserName")
Exit Function
Case Is = "COMPUTER", "3"
GetName = Environ("ComputerName")
Exit Function
Case Else
GetName = CVErr(xlErrValue)
End Select

End Function

Paul_Hossler
11-12-2011, 06:42 AM
Maybe something like this?


Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True

'i changed to Col M only for the DC, but it can easily be changed
If Intersect(Range("M4:M1000"), Target) Is Nothing Then Exit Sub

With Target
.Value = Now()
.NumberFormat = "dd/mm/yy hh:mm"

.Offset(0, 1).Value = Environ("ComputerName")
.Offset(0, 2).Value = Environ("UserName")
End With

ActiveWorkbook.Save

End Sub


Paul