Excel: replace value in current cell

[dh42891]

I'm busy making a simple spreadsheet complicated for my own learning experience, so thanks for the patience...

I have a data validation drop down menu, but my users are very resistant to change. Previously, we entered our initials in certain cells. The drop down menu is great, but I suspect a lot of people are going to hate it just because it's new. So, I'd like to establish some code that looks at the current cell and replaces the initals entered with the the full name. For example: John Joe Smith is lazy, and doesn't like his mouse so he just wants to type "jjs" in the cell. I want Excel to recognize "jjs" and replace it with "John Joe Smith". Ideally, I could keep my data validation menu for people who prefer to select their name. I can think of 'round about ways using helper cells to do that, but it would be cumbersome. Is there an easier way to accompish this? Thanks again,

dylan

 

[dh42891]

Here's what I have working:

For Each Cell In Target
        With Cell
            If Cells(.Row, "B").Value = "jjs" Then
                Cells(.Row, "B").Value = "John Joe Smith"
            End If
        End With
    Next Cell

Only problem is, I have to repeat this for each column for each user (15 columns * 12 users = 180 chunks of code). Seems like a lot and that there may be a more efficient way to write this? Thanks for any help or pointers...

dylan

 

[DirkStruan]

Suggest you use a couple of arrays.

Private Sub Worksheet_Change(ByVal Target As Range)

longname = Array("John Joe Smith", "Rob Robertson", "Karl Karllson")
shortname = Array("jjs", "rr", "kk")

For Each Cell In Target
        With Cell
            For x = 0 To 2
                If Cells(.Row, "B").Value = shortname(x) Then
                    Cells(.Row, "B").Value = longname(x)
                End If
            Next x
        End With
    Next Cell
End Sub

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[Loomah]

Hi
Another option:-

I'd suggest holding all your names and initials in a list on a separate sheet. A seperate benefit of this is that you could use the list for your data validation too. The list will have 2 columns:- Initials, Full Name.

I've imaginatively named the list "NameList"! You can then use the VLookup funcion to get the names

I've assumed that users will enter names anywhere into column C and nowhere else. This may need to be adapted to suit specific needs.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
On Error GoTo hndlr

If Not Intersect(Target, Columns(3)) Is Nothing Then
    Target = WorksheetFunction.VLookup(Trim(Target), Worksheets("Lists").Range("NameList"), 2, False)
End If

CleanExit:
Application.EnableEvents = True
Exit Sub

hndlr:
If Err.Description = "Unable to get the VLookup property of" & _
        " the WorksheetFunction class" Then
    MsgBox "The Initials you have entered are not valid", vbExclamation + vbOKOnly, "ERROR!"
    Target.ClearContents
    Target.Activate
End If
Resume CleanExit
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​