Prevent VBE Open on Button Click

Question

I have some buttons that run some code, each time I click the button, the VBE opens and I need it to not. I've tried Application.VBE.MainWindow.Visible = False in several different locations (some not shown here), but it doesn't prevent anything.

Sub Clear_Sales_Button()
'
' Clear_Sales_Button Macro
'
    Application.VBE.MainWindow.Visible = False
    ActiveWorkbook.Sheets("Sales").Activate
    ActiveSheet.Buttons.Add(44.25, 4.5, 144.75, 19.5).Select
    Application.Goto Reference:="Clear_Sales_Button"
    Selection.Characters.Text = "Clear Sales"
    With Selection.Characters(Start:=1, Length:=11).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = 3
    End With
    Selection.OnAction = "Module1.Readd_Button"
    Range("A1").Select

End Sub
Sub Readd_Button()
'
' Readd_Button Macro
'
' Keyboard Shortcut: Ctrl+w
'
    Application.VBE.MainWindow.Visible = False
    Application.ScreenUpdating = False
'Delete all cells
    ActiveWorkbook.Sheets("Sales").Activate
    ActiveSheet.Cells.Delete
'Add button back to Sales sheet
    ActiveSheet.Buttons.Add(48, 15.75, 96, 29.25).Select
    Application.Goto Reference:="Readd_Button"
    Selection.Characters.Text = "Import Sales"
    With Selection.Characters(Start:=1, Length:=12).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = 1
    End With
    Selection.OnAction = "Button1_Click"
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

I simply don't want the VBE to open, but cannot find a way to prevent it

Answer 1

The guilty statement in your code is the Application.Goto statement. While it's normal use is to jump to a specific range, it is valid to provide a procedure name, see Application.Goto on Microsoft docu.

In your case, you provide the name of a sub in your code Reference:="Clear_Sales_Button". Although not explicitly stated in the MS page, this will "jump" to the specific routine in the VBE - with other words, show the VBA and scroll to the routine (not execute it).

Answer 2

This won't help you on the matter but will help you understanding how to add buttons without activating sheets or anything:

Option Explicit
Sub Add_Button(MyMacro As String, MyName As String)
'
' Readd_Button Macro
'
' Keyboard Shortcut: Ctrl+w
'
    Application.ScreenUpdating = False
    With ThisWorkbook.Sheets("Sales")
        'Add button back to Sales sheet
        Dim MyButton As Button
        Set MyButton = .Buttons.Add(48, 15.75, 96, 29.25)
    End With

    With MyButton
        .Characters.Text = MyName
        With .Characters(Start:=1, Length:=12).Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 12
            .ColorIndex = 1
        End With
        .OnAction = MyMacro
    End With

    Application.ScreenUpdating = True

End Sub
Sub Clear_sales_Button()

    Add_Button "Module1.Readd_Button", "Clear Sales"

End Sub
Sub Readd_Button()

    'Delete all cells
    ThisWorkbook.Sheets("Sales").Cells.Delete
    Add_Button "Button1_Click", "Import Sales"

End Sub

P.S: I have win10 64bits with office 32bits and my VBE doesn't open when running a macro.

Answer 3

Sounds like a ghost breakpoint. Breakpoints are opcodes inserted into the compiled code, to instruct the debugger to, well, break there.

You don't want to "hide the VBE window", that's working on the symptom, not the cause.

Remove the module from your project, and export the code file when prompted. Then re-import the exported code files into the project.

If the code is in a worksheet's code-behind (you can't really export/re-import worksheet modules), move it to a standard module and recompile.

This will essentially "clean up" the garbage leftover compiled p-code, and the code should stop hitting ghost breakpoints that aren't there anymore.