How do you Open Excel without activating an Auto Run Macro?-VBForums
Results 1 to 6 of 6

Thread: How do you Open Excel without activating an Auto Run Macro?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    365

    How do you Open Excel without activating an Auto Run Macro?

    I am using Excel 2007.

    I have some macros in place that run automatically when the file is opened.

    However, I would still like to be able to open my excel file without it running the macros automatically. I thought this could be done by holding down SHIFT when opening the file, but it doesn't seem to work.

    Is this a bug (if so, know where the fix is?) or is there an option in excel I need to activate first?

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: How do you Open Excel without activating an Auto Run Macro?

    Simply take them out of the workbook open event...

    Also if you want to permanently stop them, you can do this by setting the Macro Security level to high
    Last edited by Siddharth Rout; Feb 22nd, 2010 at 12:37 PM.
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    365

    Re: How do you Open Excel without activating an Auto Run Macro?

    There is no other way around it?

    This excel sheet is a template so when the user opens it the file runs the macros automatically to collect the data off the internet and setup the report for them. However, I wanted to be able to open it myself for adjustments without actually kicking off the macros.

    I remember being able to do this in 2003, but it just doesn't want to work in 2007.

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: How do you Open Excel without activating an Auto Run Macro?

    One more way but before that can you post the code which is there in the open event?

    Code:
    Private Sub Workbook_Open()
        Dim strInput As String
        
        strInput = MsgBox("Do you want to run the macro?", vbQuestion + vbYesNo)
        
        If  strInput = vbNo Then
            '~~> Code for No button Press
            MsgBox "Macros will not be run"
        Else
            '~~> Your rest of the code to run the macro
        End If
    End Sub
    Last edited by Siddharth Rout; Feb 22nd, 2010 at 12:44 PM.
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    365

    Re: How do you Open Excel without activating an Auto Run Macro?

    Code:
    Private Sub Workbook_Open()
    
    ActiveWorkbook.RefreshAll
    Sheets("Date Check").Visible = True
    Sheets("Date Check").Activate
    Range("A2").Select
    If ActiveCell.Value <> ActiveCell.Offset(0, 2).Value Then
        ActiveCell.Offset(0, 2).Value = ActiveCell.Value
        Run "Data_Manipulation"
    End If
    Sheets("Date Check").Visible = False
    Sheets(1).Select
    
    End Sub

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: How do you Open Excel without activating an Auto Run Macro?

    Try this

    Code:
    Private Sub Workbook_Open()
        Dim strInput As String
        
        strInput = MsgBox("Do you want to run the macro?", vbQuestion + vbYesNo)
        
        If strInput = vbNo Then
            '~~> Code for No button Press
            MsgBox "Macros will not be run"
        Else
            '~~> Your Macro
            ActiveWorkbook.RefreshAll
            Sheets("Date Check").Visible = True
            Sheets("Date Check").Activate
            Range("A2").Select
            If ActiveCell.Value <> ActiveCell.Offset(0, 2).Value Then
                ActiveCell.Offset(0, 2).Value = ActiveCell.Value
                Run "Data_Manipulation"
            End If
            Sheets("Date Check").Visible = False
            Sheets(1).Select
        End If
    End Sub
    BTW to answer your other question... I don't have access to Office 2007 so I am not sure if "Shift" works or not
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.