Return Userform after MsgBox

[Arie Bos]

I have a userform whit optionbuttons 'New' and 'Existing'. Depending on the selection, userform 'New' or userform 'Existing should follow. This works OK.
I 'd like to avoid a default value for one of them, so the user must choose. So, I have a MsgBox popping up if btnNext1 is pressed without a selection.
So far so good. But then, the userform disappears after clicking OK on the MsgBox, and an 'Out of Memory' message appears.
I am not sure if these two issues are related.

Any ExcelMaster has any idea how to solve this?

private sub btnNext1_click()
' .......

If ((Me.OptNew.Value = 0) * (Me.OptExisting.Value = 0)) Then
  MsgBox "Please select between Existing installation or New installation"
End If

Select Case True
   Case OptNew: frm30_NewInstall.Show vbModal
   Case OptExisting: frm20_ExistingInstall.Show vbModal
End Select

Unload Me
End Sub

 

[HaHoBe]

Hi Arie Bos,

why not avoid the MsgBox and disable the Button in UserForm_Initialize and enable it when a choice with the Option Buttons is made?

Private Sub btnNext1_Click()

  Select Case True
   Case OptNew: Debug.Print "New"
   Case OptExisting: Debug.Print "Existing"
  End Select
  
  Unload Me

End Sub

Private Sub OptExisting_Click()

  btnNext1.Enabled = True

End Sub

Private Sub OptNew_Click()

  btnNext1.Enabled = True

End Sub


Private Sub UserForm_Initialize()
  
  btnNext1.Enabled = False

End Sub

Ciao,
Holger

 

[Arie Bos]

Hi Holger,
Thanks a lot... Never thought about this much more elegant solution. Works like a charm!
Thank you again,
Arie

 

[Arie Bos]

Holger, One other question... is there a way to integrate the following in your code?

Select Case True
   Case OptNew: frm30_SSDInstall.Show vbModal
   Case OptExisting: frm20_ExistingInstall.Show vbModal
End Select

 

[HaHoBe]

Hi Arie,

I left it with the btnNext1_Click - whereelse do you think it should be placed?

Ciao,
Holger

 

[Arie Bos]

Hi Holger,

There is a long piece of code in the btnNext1_Click before ending with this item.
If I end as follows:

Select Case True
   Case OptNew: Debug.Print ("New"); frm30_SSDInstall.Show; vbModal
   Case OptExisting: Debug.Print ("Existing"); frm20_ExistingInstall.Show; vbModal
End Select
  
  Unload Me
End Sub
Private Sub OptNew_change()
  btnNext1.Enabled = True
End Sub

Private Sub OptExisting_Change()
  btnNext1.Enabled = True
End Sub

I get a Type Mismatch on .Show if I click btnNext1, and when clicking the btnExisting, immediately a message pops up saying that only comments may appear after End, End Function or End Property.

 

[HaHoBe]

Hi Arie,

as I did not create the additional userforms I used Debug.print to see the action of the code. I doubt the semicolon to be the reason for the error/mismatch. Go back to the original code you posted without the MsgBox and try

        private sub btnNext1_click()
' .......

Select Case True
Case OptNew: frm30_NewInstall.Show vbModal
Case OptExisting: frm20_ExistingInstall.Show vbModal
End Select

Unload Me
End Sub

Ciao,
Holger

 

[Arie Bos]

Oh, I see... makes sense...
But now a 424 error says there is an object required, as if the two forms do not exist.

Select Case True
   Case OptNew: frm30_NewInstall.Show vbModal
   Case OptExisting: frm20_ExistingInstall.Show vbModal
End Select
  
  Unload Me
End Sub
Private Sub OptNew_change()
  btnNext1.Enabled = True
End Sub

Private Sub OptExisting_Change()
  btnNext1.Enabled = True
End Sub

 

[HaHoBe]

Hi Arie,

a small change to the code:

Private Sub btnNext1_Click()

  If OptNew Then
    frm30_NewInstall.Show vbModal
  ElseIf OptExisting Then
    frm20_ExistingInstall.Show vbModal
  End If
  
  Unload Me

End Sub

Ciao,
Holger