none
Macro won't run unless code window has been previously opened RRS feed

  • Question

  • This pertains to Excel 2007/2010.  

    Also note that the macro content is enabled when the Excel file is opened.

    I have a public subroutine written in VBA called MultiMerge() which opens a form that works just fine.  The problem is that although this Sub shows up in the Macro dialog (View --> Macros --> View Macros), the "Run" button is disabled. 

    The weird thing is that as long as I have opened the code browser at least one time (Alt + F11), then the run button is enabled, and it runs properly.   This works even if the code window is subsequently closed.  It will then work until the Excel file is closed and then re-opened, and then the problem will return.

    Has anybody ever had this problem before?  Any ideas?

    Thanks,

    Karl

    Friday, September 9, 2011 10:02 PM

Answers

  • If you want to send me a copy of the workbook, I will take a look at it
    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by K Wenz Tuesday, September 13, 2011 8:26 PM
    Saturday, September 10, 2011 12:21 AM

All replies

  • If you want to send me a copy of the workbook, I will take a look at it
    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by K Wenz Tuesday, September 13, 2011 8:26 PM
    Saturday, September 10, 2011 12:21 AM
  • Is your Macro security set to "Disable all macros except dgitally signed macros" or "Enable all macros"?

    Is the macro in the workbook or is it in nother workbook?

    Saturday, September 10, 2011 7:54 PM
  • Hi Treker03, I have tested this with all of the different macro security settings in the trust center (including "enable all macros"), and I still get the same bug.

    Also, this macro is in the same workbook.

    Monday, September 12, 2011 8:59 PM
  • Thanks for your response Doug.  I have emailed a copy of the workbook to you.
    Monday, September 12, 2011 9:02 PM
  • OK, the problem was that the VBA code was located in the ThisWorkbook Excel object, rather than one of the Sheets.  Once the VBA code was moved to Sheet1, the Run button in the View Macros dialog acted properly.

    Doug Robbins figured this out for me in an email conversation, so I'm posting the answer here but marking his reply as the answer.

    Thanks!


    • Edited by K Wenz Tuesday, September 13, 2011 8:33 PM grammar
    Tuesday, September 13, 2011 8:26 PM
  • hi, i also encountered this problem. but i did check all the codes. the procedure which is declared as public exist on a module (not ThisWorkbook object) and this is called from a button in MAIN sheet. for some reason, the part that deletes old worksheets won't work unless i leave the IDE window open. The display alerts were properly set. It will run once after I reopen the IDE and close, but if I click the button again then this procedure is skipped. This is driving me nuts hope you can help me :(
    Monday, December 19, 2011 4:57 PM
  • This is the the procedure that won't run unless I open the IDE:

    Public Sub DeleteNonDefaultSheets()
        Dim ws As Worksheet
        Dim DEF_SHEETS As String
        
        DEF_SHEETS = "MAIN+PIVOT_PRCNT+PIVOT_SL+PO_SO+WRK_SHT+XAC_BAO"
        
    On Error GoTo errTrap
        For Each ws In ThisWorkbook.Worksheets
            If InStr(1, DEF_SHEETS, ws.CodeName, vbTextCompare) = 0 Then
                ThisWorkbook.Application.DisplayAlerts = False
                ws.Delete
                ThisWorkbook.Application.DisplayAlerts = True
            End If
            MAIN.Activate
        Next ws
        
        Exit Sub
        
    errTrap:
        MsgBox "Error deleting sheets.", vbCritical, "ERROR ENCOUNTERED"
        End
        
    End Sub

    I noticed that if ws.name is not within DEF_SHEETS, then instr value still returns 1 instead of zero, reason why the sheets are not deleted. But if I run the macro, while IDE is open, then the instr value returns to zero if ws.name is not within DEF_SHEETS. Any ideas please?

    Monday, December 19, 2011 5:59 PM
  • now i modified the code into this:

    Public Sub DeleteNonDefaultSheets()
        Dim ws As Worksheet
        
    On Error GoTo errTrap
        For Each ws In ThisWorkbook.Worksheets
            If ws.CodeName Like "Sheet*" Then
                ThisWorkbook.Application.DisplayAlerts = False
                ws.Delete
                ThisWorkbook.Application.DisplayAlerts = True
            End If
            MAIN.Activate
        Next ws
        
        Exit Sub
        
    errTrap:
        MsgBox "Error deleting sheets.", vbCritical, "ERROR ENCOUNTERED"
        End
        
    End Sub
    

    and still the ws.delete part line was not executed unless i run this code with the IDE open. Aggghhhh! what's wrong?! :(


    Koshie_Me
    Monday, December 19, 2011 6:14 PM
  • I think I already know the reason why. The worksheet codename is not properly "identified" unless you have the VBA IDE open. When I changed my code by comparing the ws.name instead of ws.codename, the worksheet delete worked. :)
    Koshie_Me
    • Proposed as answer by koshie_me Tuesday, December 20, 2011 2:51 AM
    Tuesday, December 20, 2011 2:51 AM