none
VBA Excel code-module behavior RRS feed

  • Question

  • I have a situation where the code-module keeps popping up and hiding the worksheet.

    I have a worksheet with a button in the toolbar that performs the following actions:

    1. Copy a data-entry section (a fixed sized number of columns wide and rows deep) to an area just below the original in the worksheet (and any additional areas below that one, etc.)
    2. Copy a ‘calculate’ button from beside the first data-entry area to beside the new data-entry area in the worksheet (for each new data-entry area, etc.)
    3. Create a new button-click event for the new ‘calculate’ button (in the code-module of this same worksheet as the buttons)

    This is all working well…except for the irritating fact that, when the new event is created the code module is brought to the foreground and hides the data-entry worksheet.

    Is there some way to prevent this display of the code-module?

    Monday, February 23, 2015 8:36 PM

Answers

    1. Create a new button-click event for the new ‘calculate’ button (in the code-module of this same worksheet as the buttons)

    This is all working well…except for the irritating fact that, when the new event is created the code module is brought to the foreground and hides the data-entry worksheet.

    Is there some way to prevent this display of the code-module?

    This happens when you use CreateEventProc. Use AddFromString instead.

    Andreas.

    Wednesday, February 25, 2015 3:06 PM

All replies

  • Hi Michael,

    This is the default behavior if you use VBA code to create the click event handler of the button. The VBE window will be activated. One solution is to hide the VBE window and activate the workbook window, for example:

    Sub Test()
        'Activate the workbook
        Workbooks("D:\test.xlsm").Activate
        'Hide the VBE window
        ThisWorkbook.VBProject.VBE.MainWindow.Visible = False
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 25, 2015 6:26 AM
    Moderator
    1. Create a new button-click event for the new ‘calculate’ button (in the code-module of this same worksheet as the buttons)

    This is all working well…except for the irritating fact that, when the new event is created the code module is brought to the foreground and hides the data-entry worksheet.

    Is there some way to prevent this display of the code-module?

    This happens when you use CreateEventProc. Use AddFromString instead.

    Andreas.

    Wednesday, February 25, 2015 3:06 PM
  • Thanks to all...I couldn't get the HIDE VBE WINDOW to work, but I'll try to remember the possibility for later.

    But Andreas' suggestion to use AddFromString did work...although it required me to structure the entire procedure as one long sentence(!)  I'm happy and life is good.

    MAJ

    Wednesday, February 25, 2015 6:33 PM