Insert User Name in cell after entry into another cell

[Livin404]

Greetings, I have a VBA that will insert the User's name with a title. This title & user name needs to be insert into Column G after the previous adjecent cell has data. So if data is entered into Cell F5 then the title and user name will be entered into Cell G5. This is process repeats it's everytime another entry is made in Column F. I'm fairly certain the basic formula I have works (not sure about the last couple of line). I just need it to be activated once data is entered in the previous column on the same row.
Thank you , so much

Sub Insert_Sir_Name()
Dim LastRow As Long
Dim wSurame As String, Title As String
Dim x As Variant
LastRow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
wSurname = UCase(Split(wName, ",")(0))
For Each x In Array("Cpt ", "Cpl ", "Mr ", "Mrs ", "Esq ", "Ms")
If InStr(wName, x) > 0 Then
Title = UCase(x)
Exit For
End If
 End Sub

 

[mumps]

In your code, you use the variable "wName" but you don't define what that variable represents. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[sykes]

...also, I wasn't going to mention it, but as @mumps has started things off...

I think there's a typo in one of your declarations:

Dim wSurame As String, Title As String
Dim x As Variant
LastRow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
wSurname = UCase(Split(wName, ",")(0))

You use "wSurname" in your code body, but you've declared it as "wSurame." Not a deaqlbreaker, unless you have "Option explicit" declared, but it wouldn't hurt to correct it.

 

[Livin404]

In your code, you use the variable "wName" but you don't define what that variable represents. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thank you, Here is the original code, I just tried to trim it a little. This does work. I just need to have it work in a way like I described in my original message.
Thank you!

Sub find_last_plus_3()
Dim LastRow As Long
Dim wName As String, wSurame As String, Title As String
Dim x As Variant
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
 wName = Replace(Application.UserName, "WG-07", "MR")
wSurname = UCase(Split(wName, ",")(0))
For Each x In Array("MR ", "Cpt ", "Cpl ", "Mr ", "Mrs ", "Ms ", "Esq")
    If InStr(wName, x) > 0 Then
        Title = UCase(x)
        Exit For
    End If
Next x

End Sub

 

[mumps]

I'm not sure if I interpret your code properly. If the UserName will always have the text "WG-07", then the variable wName will always contain "MR". Is this correct? Can you post some sample data?

 

[Livin404]

I'm not sure if I interpret your code properly. If the UserName will always have the text "WG-07", then the variable wName will always contain "MR". Is this correct? Can you post some sample data?

Thanks for getting back so quickly. Actually the reason why I shorted my first post is because there will be no civilian working in this department. I just included it on there in the second time because the code works perfect. So the ranks will be " Cpt ", "Cpl and a few others which I can add. The title of the indiviual is included in their email address. My basic code is correct, I just neet it to drop the WG-07 and references to Mr., Mr. Esq. etc. then have it all trigard by data input in Column F. Thank you

 

[mumps]

I'm afraid that without some sample data, it is difficult to follow. What you want to do can be achieved by using a Worksheet_Change event triggered by a change in column F. If you could post some sample data with a detailed explanation referring to specific cells, rows, columns and sheets using a few examples from your data , I could perhaps suggest a solution.

 

[Livin404]

I'm afraid that without some sample data, it is difficult to follow. What you want to do can be achieved by using a Worksheet_Change event triggered by a change in column F. If you could post some sample data with a detailed explanation referring to specific cells, rows, columns and sheets using a few examples from your data , I could perhaps suggest a solution.

I've been working on it, and what I have now is fairly close. the one issue I have is I have another worksheet_Change. I think I'm on the right path, I combined the two by adding another application.Enablevents. It works fine until I delete any row or cells then it doesn't work. Any suggestions, I'm sure it's just a little tweak. Thank you,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Columns.Autofit
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "G").Value = UCase(Split(Application.UserName, ",")(0))
Application.EnableEvents = True
End Sub

 

[mumps]

You cannot have more than one Worksheet_Change event in any sheet. Please post a copy of your other Worksheet_Change macro. The macro below should do what you want as long as it is the only Worksheet_Change macro.
Private Sub Worksheet_Change(ByVal Target As Range)
Columns.AutoFit
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
Target.Offset(, 1) = UCase(Split(Application.UserName, ",")(0))
End Sub

 

[Livin404]

Thank you I did get it to work, but I will have to work on getting the titles in later, but for now it will have to do. However I would liker to chage Ucase to change WHITESIDE to Whiteside.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Columns.AutoFit
If Target.Cells.Count = 1 And Target.Column = 6 Then
Cells(Target.Row, "G").Value = UCase(Split(Application.UserName, ",")(0))
End If
Application.EnableEvents = True
End Sub