Excel VBA not showing userform

Question

I have a VBA macro that opens by asking the user a series of questions (asking them to say which of the open workbooks performs which function). I have a series of userform.show commands as below:

  UserForm2.Show  ' select cost data file
  Set piersBook = ActiveWorkbook

  UserForm5.Show  ' select IRR file
  Set irrBook = ActiveWorkbook

  UserForm6.Show  ' select BC summary file
  Set bcSummary = ActiveWorkbook

(now, after the event, I realise it would have been more simple to put these into one userform).

The net effect is for the last one not to display.
After some research I changed the code to:

  UserForm2.Show  ' select cost data file
  Set piersBook = ActiveWorkbook

  UserForm5.Show  ' select IRR file
  Set irrBook = ActiveWorkbook

  DoEvents

  UserForm6.Show  ' select BC summary file
  Set bcSummary = ActiveWorkbook

This worked for about 5 or 6 iterations, before it reverted to the original problem.

I put breakpoints in the userform initialize code. They were all called and the userforms all worked (until I removed the breakpoints again).

Finally I started removing the offending userform: the problem transfered itself to the next one back. And again, when that was removed, to the one before.

The userforms' code is identical:

Private Sub ListBox1_Click()
  Workbooks(ListBox1.Value).Activate
  Unload Me
End Sub

Private Sub UserForm_Initialize()

  Dim wb As Workbook
  For Each wb In Workbooks
    ListBox1.AddItem wb.Name
  Next wb
End Sub

Any thoughts? At the moment I am hardcoding the inputs which is not ideal. Many thanks.

Answer 1

use only UserForm2, then:

• change your UserForm2 code as follows

  Private Sub ListBox1_Click()
      With Me
          If ListBox1.ListIndex <> -1 Then
              .Tag = .ListBox1.Value
              .Hide
          Else
              MsgBox "You must select a workbook"
          End If
      End With
  End Sub
  
  Private Sub UserForm_Initialize()
    Dim wb As Workbook
    For Each wb In Workbooks
      ListBox1.AddItem wb.Name
    Next wb
  End Sub
  

• change your "main" code as follows

  Dim piersBook As Workbook, irrBook As Workbook, bcSummary As Workbook
  
  With UserForm2
      .Caption = "select cost data file"
      .Show  ' select cost data file
      Set piersBook = Workbooks(.Tag)
  
      .Caption = "select IRR file"
      .Show  ' select cost data file
      Set irrBook = Workbooks(.Tag)
  
      .Caption = "select BC summary file"
      .Show  ' select BC summary file
      Set bcSummary = Workbooks(.Tag)
  End With
  Unload UserForm2