Member Avatar for shabadoo

I am using a collection of Workbooks that are supposed to work in sync.

When the main "System" Workbook is opened it initializes showing a sequence of userforms which get information from the user to determine how to proceed. One of the options is to "check inventory", when chosen it opens and activates the "Inventory" workbook.

I would like to have the "Inventory" workbook initialize with a userform asking the user if they would like to perform a vlookup or to adjust "quantity on hand".

I have tried to enter code in the "Inventory" workbook's "ThisWorkbook" telling it to show the userform when the workbook opens but it doesn't work when activated through the "System" workbook.

However, if I open the "Inventory" workbook outside of the main "system" workbook (by selecting it from windows file manager) it opens the way I would like it to.

This is what I have used so far

Private Sub Workbook_Open()

    Application.EnableEvents = False
    
    Call Auto_OpenInv
    
End Sub
Sub Auto_OpenInv()
    
    Application.Visible = False 'Hides application while userform is shown
    frmInventoryHome.Show vbModal
    
End Sub

Any Ideas?

  • 2 Contributors
  • 3 Replies
  • 96 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

The behavior described by is a common VBA misunderstanding: Workbook_Open runs only when Excel actually opens that file; it does not fire when another workbook simply activates an already-open workbook. Also, workbook events will not fire if Application.EnableEvents is False. ’s pointer to the other thread is on target — the interaction depends on how the System workbook opens/activates the Inventory file.

Two reliable approaches:

  1. Handle activation inside the Inventory file
    Put the startup logic in the Inventory workbook’s Workbook_Activate (ThisWorkbook) so the form appears whenever that workbook becomes active. Example (place in Inventory ThisWorkbook):

    Private Sub Workbook_Activate()
 InventoryHomeForm.Show vbModal
End Sub

  2. Have the System workbook call Inventory explicitly
    Expose a public sub in Inventory that shows the form, and have System call it immediately after opening/activating the Inventory workbook using Application.Run. This is explicit and avoids relying on events:

    
' In Inventory (standard module)
Public Sub ShowInventoryStartup()
 InventoryHomeForm.Show vbModal
End Sub

' In System, after opening Inventory
Application.Run "'" & wb.Name & "'!ShowInventoryStartup"



Quick troubleshooting checklist
- Confirm whether the System code uses Workbooks.Open (fires Open) or just .Activate (does not fire Open).  
- Ensure Application.EnableEvents is True before opening; if you must toggle it, always restore it in an error handler. See the docs on [Application.EnableEvents](https://learn.microsoft.com/en-us/office/vba/api/excel.application.enableevents).  
- Consider not hiding the whole Excel window while showing a modal form; that can confuse focus and user interaction.  
- For event semantics see [Workbook.Open](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.open) and [Workbook.Activate](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.activate).  

Either moving to Workbook_Activate or calling a public routine from System will make the Inventory form appear reliably when the System workbook requests it.

Recommended Answers

All 3 Replies

Member Avatar for AndreRet

This is quite confusing.

Firstly, if you say workbook, do you mean that you are using VBA in excel?

I would like to have the "Inventory" workbook initialize with a userform asking the user if they would like to perform a vlookup or to adjust "quantity on hand".

I have tried to enter code in the "Inventory" workbook's "ThisWorkbook" telling it to show the userform when the workbook opens but it doesn't work when activated through the "System" workbook.

Are these all different workbooks/applications?

Member Avatar for shabadoo

Sorry!

Yes I am using vba in excel. And yes they are different workbooks in the same workbook collection within the application.

Member Avatar for AndreRet

Have a look at THIS link. It covers opening, interaction and closing of workbooks outside your application.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.