X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Rename A Sheet On Error, Office 2010    
post May 6 2019, 09:40 PM

Posts: 1,299
Joined: 17-November 13
From: Orewa New Zealand

I have the below code but if I try and do a sheet with the same name it gives me an error name already found/used
I would like to have a pop up where I can rename it
Dim xRngAddress As String
Dim xName As String
xRngAddress = ActiveSheet.Range("A2").Address
For Each xWs In Application.ActiveWorkbook.Sheets
    xName = xWs.Range(xRngAddress).Value
    If xName <> "" Then
        xWs.Name = xName
    End If
Go to the top of the page
post May 7 2019, 12:51 AM

Posts: 597
Joined: 25-January 16

The error message I get is "Run-time error 1004: Application-defined or object-defined error". Quick fix:

    If xName <> "" Then
        On Error Resume Next
        xWs.Name = xName
        If Err.Number = 1004 Then
            xWs.Name = InputBox("Enter sheet name", "Enter Sheet Name")
        End If
    End If

Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post May 7 2019, 02:00 PM

Utter Access VIP
Posts: 6,283
Joined: 22-June 04
From: North Carolina

I found a copy of this wheel at: https://stackoverflow.com/questions/6688131...if-sheet-exists.


One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    17th June 2019 - 05:45 PM