Insert User Name in cell after entry into another cell

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
774
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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).
 
Upvote 0
...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:

VBA Code:
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.
 
Upvote 0
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!

VBA Code:
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


 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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,

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top