Excel vba Workbook_Open() does not run

Question

I've defined a Workbook_Open subroutine but it never gets executed. Here's what I see:

I tried putting it in Module 3 and when that didn't work I tried it in Module 4. I see references to "ThisWorkbook" in some of the support documents that I've found--not specifically saying that it needs to be in a module of the name--but I'm wondering if that is what I need to do?

Thanks for any suggestions. SweetTasha

An afterthought, why not try inserting a module named "ThisWorkbook" and put the code there? I'll try it...Stay tuned.

YEAH. I put the code in ThisWorkbook. Not using insert module, but in the spreadsheet object. 

Answer 1

Hi SweetTasha

Let's say every time you open the workbook you want a pop-up message saying "Hello, SweetTasha"

So,

Here two methods

Method 1

To have the code directly on the workbook VBA event panel as shown in the picture below

Method 2

To have the code on a regular module, or create a module if necessary with the Subroutine code to be run when the workbook is open as shown in the picture below  i.e. Sub SayHello()

Then on the workbook VBA open event panel Call the subroutine. As shown in the picture below

Choose the method of your preference and replace the Message with your actual code.

I hope this helps you

Regards

Jeovany

Answer 2

@Jeovany CV

Thanks for your nicely documented reply. You have have posted before I added to my post that I had succeeded in put the code into ThisWorkbook. The code is running now, but it gets an error on

Range("VendorNameFirst", "VendorNameLast").Insert _

Shift:=xlShiftDown

Please see my post 

https://answers.microsoft.com/en-us/msoffice/forum/all/use-vba-rangeinsert-method-with-excel-defined-name/1ac37592-f1a3-4538-a9e1-cbf750939990

Private Sub Workbook_Open()

        Stop  'for debugging  '7-5-2020

    onerrorsw = 0

    On Error GoTo OnErrorRoutineInOpen

NORMAL_OPEN:

  Stop  '7-5-2020 uncommnent this for debugging

  ' click Run to continue in normal mode

  ' click Debug to start debug mode

'Stop 'for debugging

  openCount = openCount + 1

  Range("VendorNameFirst", "VendorNameLast").Insert _

    Shift:=xlShiftDown

OnErrorRoutineInOpen:

  Stop

  Debug.Print "In Open ERROR " & Err.Number & " SOURCE " & Err.Source

  On Error GoTo 0

  Stop

  Resume Next

  ' says "Resume without error"

  Stop

End Sub

It gets the error on 

Range("VendorNameFirst", "VendorNameLast").Insert _

Shift:=xlShiftDown

The error must be in the syntax, but how to find what's wrong with the syntax. 

The article at https://docs.microsoft.com/en-us/office/vba/api/excel.range.insert doesn't say how to use defined-names for the range.

Answer 3

@SweetTasha

Please, replace 

Range("VendorNameFirst", "VendorNameLast").Insert _

Shift:=xlShiftDown

with 

Range("VendorNameFirst", "VendorNameLast").Insert xlShiftDown

On the sample .gif below the cells in yellow are named ranges VendorNameFirst" and "VendorNameLast

respectively

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany