Results 1 to 4 of 4

Thread: Change names

  1. #1
    Join Date
    Mar 2010
    Posts
    27

    Unanswered: Change names

    I have a workbook that has MANY external links to other workbooks on 8 different worksheets.

    All of the other workbooks are of the same name format: John Stats , Bill Stats , etc.

    Right now, all links work by looking for 'John Stats'A1 etc. However, if John leaves, and is replaced by Mary... it's a big nightmare.

    Any ideas for a macro button I could put in that would allow me to 'change names' really easily?

    In a perfect world, the macro would be started and say 'who do you want to replace?' and pull up a list of the current people. Choose one of them and then 'what name do you want to replace XXXX with?'. Type the new name (Mary), and now Mary's stats are tracked on the workbook called Mary Stats and John Stats is not tracked at all.

    I hope that makes sense, and if that's extremely difficult, I'm open to suggestions.

    Thanks in advance!!!!

  2. #2
    Join Date
    Mar 2010
    Posts
    27
    Ok, I've figured this one out (quite easy after all!).

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Out of interest, what did you do?

  4. #4
    Join Date
    Mar 2010
    Posts
    27
    Nothing all that fancy:


    Code:
    Sub Name_Change()
    '
    ' Name_Change Macro
    '
    
    '
        Dim OldName As String
        Dim NewName As String
        OldName = InputBox("What name do you want to replace?", _
            "Who do you want to replace?", "Enter Name")
    
        If OldName = "Enter Name" Or _
        OldName = "" Then
        Exit Sub
        End If
        
        NewName = InputBox("What is the new name?", _
            "Who is the new person?", "Enter Name")
        If NewName = "Enter Name" Or _
        NewName = "" Then
        Exit Sub
        End If
        
        i = MsgBox("Replace " & OldName & " with " & NewName & "?", vbOK)
        Select Case i
            Case vbOK
                j = MsgBox("Are you sure?", vbOKCancel)
                Case vbCancel
                    Exit Sub
            Case vbCancel
                Exit Sub
        End Select
        
        Sheets(Array("Monday Totals", "Tuesday Totals", "Wednesday Totals", _
            "Thursday Totals", "Friday Totals", "Saturday Totals", "Weekly Totals")).Select
        Sheets("Monday Totals").Activate
        Cells.Select
        Selection.Replace What:=OldName, Replacement:=NewName, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Sheets("Weekly Totals").Select
        Range("A1").Select
    End Sub

    But it works and is effective.

    If you've got a better/tidier suggestion, fire away.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •