one VBA macro used to work. Suddenly Gives error

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
hello experts,

I used this macro for a long time in many excel files , now all of a sudden , it stopped working and after I click the button it gives path/file access error (with a changin .tmp file name every time) and then after I click ok it gives another error runtime error 2147352565 (8002000b) - can't copy this sheet
and all of this happened out of the blue, 100/100 on the desperate meter , is there any clue for what the problem is ?

Essentially the code copy-paste a template sheet and renaming it to the inputbox the user've entered.
Sheets(strName).Select near the end of the code in this line is where I get the yellow mark of error

thanks for any help ,
Netanel
VBA Code:
 Option Explicit

Private Sub CommandButton1_Click()


    Dim ws As Excel.Worksheet
    Dim strName As String
 
    On Error GoTo ErrHandler
 
    strName = Application.InputBox("Please enter the  description...", "New Item", Type:=2)
 
    If strName <> vbNullString Then
        strName = ValidSheetName(strName)
    Else
        MsgBox "Cancelled...", vbInformation
        Exit Sub
    End If
 
    If SheetExists(strName) Then
        MsgBox "You already have a description called " & strName, vbExclamation, "Error"
        Exit Sub
    End If
     
    '// Turn off screen updating...
    Application.ScreenUpdating = False
 


    With Sheets("template")


        .Visible = xlSheetVisible


        .Copy after:=Sheets(Sheets.Count)


    End With
 
 
    Set ws = ActiveSheet
 
    With ws
        .Name = strName
 
        .Visible = xlSheetVisible


        .Range("StrategyName").Value = strName
    End With
 
    '// Add a row to the TOC table
    Dim oNewRow As ListRow
    Set oNewRow = ListObjects("StrategyTOC").ListRows.Add(AlwaysInsert:=True)
 
    With oNewRow
        .Range.Cells(1, 2).Value = strName
    End With


ErrHandler:
 
    Sheets("sheet for Contents").Activate


    Application.ScreenUpdating = True
                   
    Sheets(strName).Select         'in this line I get the yellow mark of error
 
 
End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,625
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
you must investigate first in debug step-by-step mode hitting F8 key to find out which codeline raises the error​
or this thread as it is seems to be just a guessing challenge for some mind readers forum …​
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
thanks Marc ,
the error inside the code is on the line " Sheets(strName).Select " near the end in the code

but I did what you said pressed F8 it also sent me to 2 functions I completely forgot about , which the code uses.

VBA Code:
Option Explicit
Option Compare Text

Public Function ValidSheetName(sSheetName As String) As String
    
    Const csInvalidChars As String = ":\/?*[]"
    
    Dim lThisChar As Long
    
    
    ValidSheetName = sSheetName
    For lThisChar = 1 To Len(csInvalidChars)
        ValidSheetName = Replace$(ValidSheetName, Mid(csInvalidChars, lThisChar, 1), "")
    Next
    
    ValidSheetName = Left$(ValidSheetName, 31)
    
End Function

Public Function SheetExists(strName As String) As Boolean

    Dim ws As Excel.Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(strName)
    
    SheetExists = (Err = 0)
    Set ws = Nothing
End Function
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,625
Office Version
  1. 2010
Platform
  1. Windows

According to the codine where the error occurs :​
the sheet can't be selected as its workbook is not active or the sheet name is not valid or the sheet does not exist …​
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
168

ADVERTISEMENT

wow, it used to work just fine in multiple excel files :(
breaking and scratching my mind right now..

I'm thinking maybe I used other macro on other file that ruined it ,
for example something like
Application.ScreenUpdating =false
but something else, that've ruin this specific code.
reviewing my last macros on other files right now

I think the fault is in ".Visible = xlSheetVisible"
something there not right or something like this. trying hoping to guess it right

I think something not visible that's why it isn't find it maybe..
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,536
Office Version
  1. 365
Platform
  1. Windows
What is the value of "strName" when you get the error?
Are you sure that a sheet by that name actually exists in your active workbook.
Note that is must match EXACTLY.
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
168

ADVERTISEMENT

Thanks for the help Joe!

I'm a beginner so I'll try to VBA talk with you experts,
StrName is based on what the user type in the MsgBox, I have no idea why the error occure, maybe something relate to hidden sheet ?
I just trying to figure things out
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,536
Office Version
  1. 365
Platform
  1. Windows
I'm a beginner so I'll try to VBA talk with you experts,
StrName is based on what the user type in the MsgBox, I have no idea why the error occure, maybe something relate to hidden sheet ?
I just trying to figure things out
Yes, I am asking what exact value are they entering in for "strName" that causes the error?
Maybe they are entering in a value that contains illegal characters to be used in a sheet name.
But we cannot know if you don't tell us what is being entered in when this error occurs!
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
sure, well it isn't anything illegal , I've tested something as simple as GG or dia or try . everything gives the same error.
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
pls, it's not something wrong about the code for sure, I've this code in 10 different excel files,
suddenly everything stopped working.
the cause is somethign more generic .
 

Forum statistics

Threads
1,144,629
Messages
5,725,381
Members
422,622
Latest member
Paranthem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top