"The object invoked has disconnected from its clients."
I have a button that prompts for input and creates a new tab. The first run it works great, but subsequent runs throw this error:
Here is the code:
Sub btnAddProject() Dim template As Worksheet Dim newSheet As Worksheet Dim newName As String newName = Application.InputBox("Enter Project Name") 'get name Worksheets("Template").Visible = True 'Unhide template Set template = ActiveWorkbook.Sheets("Template") 'identify template template.Copy After:=Sheets(Sheets.Count) 'create copy Set newSheet = ActiveSheet 'identify new sheet newSheet.Name = newName 'rename sheet 'deleteNames (newSheet.Name) 'delete copied named ranges with worksheet scope newSheet.Range("D2").Value = newName 'Change header on new sheet Worksheets("Template").Visible = False 'Hide template Worksheets("Consolidated Grid").Activate 'switch back to dashboard updateProjectIndex (newName)
End SubThe error is thrown on newSheet.Name = newName and it leaves me with a sheet named "Template (2)"
What is causing this?
In case it's relevant, here is the updateProjectIndex sub which basically just adds a new line to the list of projects (in two places):
Sub updateProjectIndex(newName) Application.ScreenUpdating = False Sheets("Dashboard").Select ActiveSheet.Rows(12).Select Selection.Copy Selection.Insert Shift:=xlDown ActiveSheet.Range("B13").Value = newName Sheets("Consolidated Grid").Select ActiveSheet.Columns("F:F").Select Selection.Copy Selection.Insert Shift:=xlRight ActiveSheet.Range("G1").Value = newName Sheets("Dashboard").Select ActiveSheet.Range("B13").Select Application.ScreenUpdating = True
End Sub2 Answers
Your code works fine for me although I had to remove the additional sub calls (so I removed updateProjectIndex(newName) AND Worksheets("Consolidated Grid").Activate
To debug, try this code in a new Excel on your PC to see if the issue is workbook related to that workbook or Excel
The issue is in code in an event
Try commenting out those 2 lines of code and see if the issue persists or not. If no issue, uncomment one at a time until you see the bug, and eliminate that way.
I'd recommend you do this with a copy of your file so you can break it as much as you like!
I think I figured it out, though I'm not sure why. I had the following code in my Workbook_Open sub:
Worksheets("Dashboard").Activate If InStr(Application.ActiveWorkbook.Path, "sharepoint.com") Then Range("1:1").Select Selection.EntireRow.Hidden = True Else Range("1:1").Select Selection.EntireRow.Hidden = False End IfThe intent was to make sure people knew they were opening an offline version. I'm assuming the conflict was with the Worksheets.Activate here? Maybe someone can explain.
Either way, removing this seems to have fixed the problem 100%.
Update: Replaced all the code but the .Activate and it is still working. I'm guessing activating a workbook from its own Workbook_Open routine was causing some sort of loop?
