je m'interese à la frappe automatique, mais je ne sais pas si c'est la bonne
solution pour mon idée :
j'ai un tableau excel sur un serveur pour que chaque chef d'atelier entre
des données variables.
j'aimerai que chaque nom soit associé à un matricule.
si quelqu'un tape "durand" je voudrais par le biais de la frappe automatique
que soit automatiquement rajouté : 15001520 (matricule associé à durand).
Avez vous une idée ?
Merci d'avance pour vos solutions.
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
JB
Bonjour,
http://cjoint.com/?bhhjymJSX0
A B C D E 1 aaa jboisgon 07/01/2006 07:06 2 bbb jboisgon 07/01/2006 07:06 3 ccc jboisgon 07/01/2006 07:06
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Count = 1 Then Application.EnableEvents = False Cells(Target.Row, 4) = GetUserName() Cells(Target.Row, 5) = Now Application.EnableEvents = True End If End Sub
Dans un module:
Option Explicit Declare Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function GetUserName() As Variant Dim strUserName As String, lngLength As Long, lngResult As Long strUserName = String$(255, 0) lngLength = 255 lngResult = wu_GetUserName(strUserName, lngLength) GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1) End Function
Cordialement JB
Bonjour,
http://cjoint.com/?bhhjymJSX0
A B C D E
1 aaa jboisgon 07/01/2006
07:06
2 bbb jboisgon 07/01/2006
07:06
3 ccc jboisgon 07/01/2006
07:06
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
Application.EnableEvents = False
Cells(Target.Row, 4) = GetUserName()
Cells(Target.Row, 5) = Now
Application.EnableEvents = True
End If
End Sub
Dans un module:
Option Explicit
Declare Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long
Function GetUserName() As Variant
Dim strUserName As String, lngLength As Long, lngResult As Long
strUserName = String$(255, 0)
lngLength = 255
lngResult = wu_GetUserName(strUserName, lngLength)
GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1)
End Function
A B C D E 1 aaa jboisgon 07/01/2006 07:06 2 bbb jboisgon 07/01/2006 07:06 3 ccc jboisgon 07/01/2006 07:06
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Count = 1 Then Application.EnableEvents = False Cells(Target.Row, 4) = GetUserName() Cells(Target.Row, 5) = Now Application.EnableEvents = True End If End Sub
Dans un module:
Option Explicit Declare Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function GetUserName() As Variant Dim strUserName As String, lngLength As Long, lngResult As Long strUserName = String$(255, 0) lngLength = 255 lngResult = wu_GetUserName(strUserName, lngLength) GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1) End Function
Cordialement JB
JB
http://cjoint.com/?bhhGKgGzuX
Variante:
Une table contient la correspondance User/Matricule
User Matricule jboisgon 12345 pdupont 23456
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Count = 1 Then Application.EnableEvents = False temp = GetUserName() On Error Resume Next Cells(Target.Row, 4) = Application.VLookup(temp, Sheets("utilisateur").Range("utilisateur"), 2, False) Cells(Target.Row, 5) = Now Application.EnableEvents = True End If End Sub
Dans un module:
Option Explicit Declare Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function GetUserName() As Variant Dim strUserName As String, lngLength As Long, lngResult As Long strUserName = String$(255, 0) lngLength = 255 lngResult = wu_GetUserName(strUserName, lngLength) GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1) End Function
JB
http://cjoint.com/?bhhGKgGzuX
Variante:
Une table contient la correspondance User/Matricule
User Matricule
jboisgon 12345
pdupont 23456
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
Application.EnableEvents = False
temp = GetUserName()
On Error Resume Next
Cells(Target.Row, 4) = Application.VLookup(temp,
Sheets("utilisateur").Range("utilisateur"), 2, False)
Cells(Target.Row, 5) = Now
Application.EnableEvents = True
End If
End Sub
Dans un module:
Option Explicit
Declare Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long
Function GetUserName() As Variant
Dim strUserName As String, lngLength As Long, lngResult As Long
strUserName = String$(255, 0)
lngLength = 255
lngResult = wu_GetUserName(strUserName, lngLength)
GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1)
End Function
Une table contient la correspondance User/Matricule
User Matricule jboisgon 12345 pdupont 23456
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Count = 1 Then Application.EnableEvents = False temp = GetUserName() On Error Resume Next Cells(Target.Row, 4) = Application.VLookup(temp, Sheets("utilisateur").Range("utilisateur"), 2, False) Cells(Target.Row, 5) = Now Application.EnableEvents = True End If End Sub
Dans un module:
Option Explicit Declare Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function GetUserName() As Variant Dim strUserName As String, lngLength As Long, lngResult As Long strUserName = String$(255, 0) lngLength = 255 lngResult = wu_GetUserName(strUserName, lngLength) GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1) End Function
