VBA Userforms Show the Same Userform again and again

Question

currently i am programming a excel macro. The macro shows a Userform. In the Userform the User can Select something. After the User has selected something i call Userform.Hide to Hide the Userform and to read the Selection from the Form. After the selection was read i call Unload Userform. Now the Code interacts with the selection. I want to do this in a loop but when the Code trys to show the Userform the second time. I get a exception that the Form is already displayed. I cant understand it, because i called Unload Userform. When i do it in debug mode everthing works as it should.

Userform Code

Private Sub Image1_Click()
      SelectCard 1
End Sub

Private Sub Image2_Click()
      SelectCard 2
End Sub


Private Sub SelectCard(number As Integer)
    SelectedNumber = number
    Me.Hide
End Sub


Public Sub CardSelector_Activate(Cards As Cards)
  Dim c As card
  For Each Key In Cards.CardDictionary.Keys
      Set c = Cards.CardDictionary.Items(Key - 1)

      If c.value = 1 And c.played Then
         Image1.Enabled = False
      End If

      If c.value = 2 And c.played Then
         Image2.Enabled = False
      End If
  Next Key
  number = SelectedNumber
  CardSelector.Show
End Sub

Code in the ClassModule i call this in a loop

   Sub Costum(Spalte As Integer, Zeile As Integer, SpalteBeginn As Integer,   Cards As Cards, CardsOpponent As Cards)
        CardSelector.CardSelector_Activate Cards
        Dim c As card
        Dim number As Integer
        number = CardSelector.SelectedNumber
        Set c = Cards.CardDictionary.Items(CardSelector.SelectedNumber - 1)
        SetCardAsPlaced c, Zeile, Spalte, SpalteBeginn
        Unload CardSelector
    End Sub

Can someone help me here ?

you wrote "I call Unload Userform", this means that the userform you want to unload is actually named after "Userform"? — user3598756
– user3598756, Commented May 23, 2016 at 9:37
Since you say that it works perfectly in debug mode, maybe the code runs through too quickly? Try inserting Sleep or Wait before you call the userform in your loop again, just for testing purposes. — Sun
– Sun, Commented May 23, 2016 at 9:40

Andrew Truckle · Accepted Answer · 2016-05-23 10:05:23Z

1

I am not sure if I fully understand your issue, but this is how I invoke a form using VBA. This is assuming you have a Cancel and OK button:

In the form:

Option Explicit

Private m_ResultCode As VbMsgBoxResult

Private Sub btnCancel_Click()
    Call CloseWithResult(vbCancel)
End Sub

Private Sub btnOK_Click()
    ' Store form control values to member variables here. Then ...

    Call CloseWithResult(vbOK)
End Sub

Private Sub CloseWithResult(Value As VbMsgBoxResult)
    m_ResultCode = Value
    Me.Hide
End Sub

Public Function ShowMe(Optional bNewLayerOptions As Boolean = True) As VbMsgBoxResult
    ' Set Default to Cancel
    m_ResultCode = vbCancel
    ' Execution will pause here until the form is Closed or Unloaded
    Call Me.Show(vbModal)
    ' Return Result
    ShowMe = m_ResultCode
End Function

Then, to call it (please note that frmLayers is my own VBA form object - you would use yours):

Dim dlgLayers As New frmLayers

If (dlgLayers.ShowMe(False) = vbOK) Then
  ' Proceeed
End If

Does this help you with your issue? I am sorry if I have misunderstood, and I will remove my answer if needed.

Things like xxxxx_Activate etc. are event handlers called by the framework. So, for example, there is an event for activate and an event for initialize. You don't normally have to directly call these yourself if you set your code up correctly. See https://support.microsoft.com/en-us/kb/138819.

edited May 23, 2016 at 10:05

answered May 23, 2016 at 9:32

Andrew Truckle

19.4k17 gold badges93 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3598756 Over a year ago

is frmLayers a class? and is the code you said being "In the form" actually in frmLayers class code pane? Should answers be both "yes" then Me.Hide wouldn't work since there's no Hide() method in the class nor would Me.Show since there's no Show() method neither. Is your code in VB instead of VBA?

Andrew Truckle Over a year ago

@user3598756 frmLayers is just my own form class. The original poster will change that to an instance of his form. For me, the above code works on all of my forms that I write. VBA. There is a Show method, it is called by ShowMe.

Andrew Truckle Over a year ago

@user3598756 There is a Hide method. It is called by CloseWithResult.

Collectives™ on Stack Overflow

VBA Userforms Show the Same Userform again and again

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related