Results 1 to 8 of 8

Thread: [RESOLVED] Rename sheets in Excel using UDF

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2009
    Location
    Nijmegen, Netherlands
    Posts
    64

    Resolved [RESOLVED] Rename sheets in Excel using UDF

    Hi,
    Simple question (I guess). Googled and found a lot of info, but still can't figure this out.

    I have an Excel sheet with:
    Name:  2-14-2024 4-51-53 PM.jpg
Views: 112
Size:  32.1 KB

    And in VBA I have:
    Name:  2-14-2024 4-54-43 PM.jpg
Views: 111
Size:  25.9 KB

    The idea is that if I change a name in column B, then column C is updated, but also the sheet name should change. (In this example simply to the username)

    The problem is that "sheets(nr+1).name=username" doesn't seem to execute. However if I type is the same command in direct mode, or in a macro it works fine. Does it mean that UDF's can't execute this kind of commands? Or should the UDF placed elsewhere? Or what?

    _Wim_

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: Rename sheets in Excel using UDF

    Possibly useful link:

    https://www.edureka.co/community/212...-the-worksheet

    It looks like VBA may what you will need to rename a sheet.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2009
    Location
    Nijmegen, Netherlands
    Posts
    64

    Resolved Re: Rename sheets in Excel using UDF

    Ok, summarizing:
    A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel.

    ...including renaming a sheet name. I'll have to think of something else. Thanks for clarifying.
    Last edited by _Wim_; Feb 15th, 2024 at 04:26 AM.

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: Rename sheets in Excel using UDF

    Goes into your "Main"-Sheet-Code-Module

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim r As Long
    Dim c As Long
        r = Target.Row
        c = Target.Column
        If c > 1 Then
            Set ws = Worksheets(Me.Cells(r, c - 1))
            If ws.Name <> Target.Value Then
                ws.Name = Me.Cells(r, c)
            End If
        End If
    End Sub
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Oct 2009
    Location
    Nijmegen, Netherlands
    Posts
    64

    Thumbs up Re: Rename sheets in Excel using UDF

    Ahhh!! Many thanks. So basically, a UDF (=Function) cannot change the sheet names, but a macro (=Sub routine) can. Using the "Worksheet_change" sub and checking the range does it all for me! I've added an error routine, since I had names like "Marc v/d Berg", where the "/" is illegal..

    My code is now, and works like charm. Again thanks Zvoni and jdc2000!

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim x As Long
    Dim ShtName As String
    
    ' Only act if cells B3:B52 are changed.
      If Not Application.Intersect(Range("B3:B52"), Range(Target.Address)) Is Nothing Then
        With Target
          x = Sheets.Count - 50 + Cells(.Row, .Column - 1) ' last 50 sheets are of interest
          On Error GoTo ErrorHandler  ' trap names with invalid characters
          ShtName = "Kas " & Cells(.Row, .Column - 1) & " " & Cells(.Row, .Column)
          Sheets(x).Name = ShtName
        End With
      End If
    Exit Sub
    ErrorHandler:
      MsgBox ShtName & " bevat ongeldige characters"
      Resume Next
    End Sub
    Last edited by _Wim_; Feb 15th, 2024 at 04:58 AM.

  7. #7

    Re: [RESOLVED] Rename sheets in Excel using UDF

    What considerations or precautions should be taken into account when using the "Worksheet_Change" sub for renaming sheets, especially when dealing with characters that might be invalid for sheet names, as mentioned in the provided VBA code?

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: [RESOLVED] Rename sheets in Excel using UDF

    Quote Originally Posted by Dabihrarry View Post
    What considerations or precautions should be taken into account when using the "Worksheet_Change" sub for renaming sheets, especially when dealing with characters that might be invalid for sheet names, as mentioned in the provided VBA code?
    None. Illegal characters are exactly that.

    either reject the entry completely, or strip out illegal characters
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width