CODEWITHSUNDEEP
excelvbauserform
Lukas
Lukas

Reputation: 11

VBA Userforms Show the Same Userform again and again

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 ?

Upvotes: 1

Views: 1780

Answers (1)

Andrew Truckle
Andrew Truckle

Reputation: 19107

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.

Upvotes: 1

Related Questions