Results 1 to 4 of 4

Macro has errors if Visual Basic Editor is not open

Threaded View

  1. #1
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Macro has errors if Visual Basic Editor is not open

    I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".

    I have got the whole thing figured out and running to satisfaction, except...

    The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"

    Any ideas what could be casing this? The errors occur when attempting to add sheets.

    Here's my "UpdateAll" macro that is having the issues:

    Sub UpdateAll()
        
        Dim SheetsAddRename As Range
        Dim shts As Worksheet
        Dim SheetTest As String
        Dim RowsDelete As Range
        
        For Each SheetsAddRename In Sheets("Main Sheet").Range("C2:C22")
            SheetTest = False
            If SheetsAddRename = "" Then
                Sheets("Main Sheet").Activate
                Call DeleteRows
                Call Resync
                Exit Sub
    
            Else
                
                For Each shts In ThisWorkbook.Worksheets
                    If shts.CodeName = SheetsAddRename.Offset(0, -1) And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
                        shts.Name = SheetsAddRename
                        SheetTest = True
                    ElseIf shts.CodeName = SheetsAddRename.Offset(0, -1) And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value <> "" Then
                        Sheets(SheetsAddRename.Value).Delete
                        SheetTest = True
                    End If
                Next shts
                
                If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetsAddRename.Value
                    SheetsAddRename.Offset(0, -1) = ActiveSheet.CodeName
                End If
                
            End If
            
        Next SheetsAddRename
    
    End Sub
    It seems to break-down here:
    If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetsAddRename.Value
                    SheetsAddRename.Offset(0, -1) = ActiveSheet.CodeName
                End If
    When it goes to update the Main Sheet with the new worksheet's codename, ActiveSheet.CodeName seems to be blank.

    Again, this only happens if the Visual Basic Editor is not open. It is as if excel cannot get the codename of a sheet if the editor is not open.

    Any help would be much appreciated.
    Attached Files Attached Files
    Last edited by Whizbang; 11-12-2009 at 02:48 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1