Which display mode should one use for a VBA UserForm in order to display data, controll it and confirm it is ok?

Question

I have the following procedure planed:

1. User selects data using userforms

2. User reviews data using usual Excel

3. User confirms the data is ok (for example with a userform)

4. data gets saved

All in the same VBA program.

Here my prototype:

The Module:

Sub ControlDataUI()
Dim Ui As New UserForm1
Dim Confirmend as Boolean
Debug.Print "dostuff"

With Ui
    .Show (False) 'or .Show(True)
    While Not .IsHiden
    Wend
    Confirmed=.Confirmed
End With

Debug.Print "Do some more stuff!"

If Confirmed then Call SaveStuff 

Debug.Print "I will die!!"
End Sub

The Userform1:

Private Type TView
    IsCancelled As Boolean
    Confirmed As Boolean
    IsHiden As Boolean
End Type

Private this As TView

Public Property Get Confirmed() As Boolean
    Confirmed = this.Confirmed
End Property
Public Property Get IsHiden() As Boolean
    IsHiden = this.IsHiden
End Property

Private Sub CommandButton1_Click()
Debug.Print "YES!!!!"
this.Confirmed = True
this.IsHiden = True
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Debug.Print "NO?!?!"
this.Confirmed = False
this.IsHiden = True
Me.Hide
End Sub

Private Sub UserForm_Terminate()
Debug.Print "Murder! I was killed!"
End Sub

Now the issue:

Modeless

When using modeless the userform is displayed but the code keeps running until done. I tried to stop the code from running through with a While Not .IsHiden construction. The code freezes Excel because it's an infinite loop. So that is ruled out. Is there a way to realize this with modeless display?

Modal

If I use a modal display, the use of excel is blocked and only the userform is shown.

In conclusion: Both Modal and Modeless display do not enable the user to control data in a sheet. Is there a solution to this problem?

`Modelss` allows you to interact with the sheet. I'm not quite sure what your issue is — Tom, Jan 24 '19 at 13:16
@tom The code keeps running until done. This is not what needs to happen. The code needs to wait until the user confirms the data is ok. Otherwise he doesn't know if it should be saved or not. — Lucas Raphael Pianegonda, Jan 24 '19 at 13:19
It seems over engineered approach. Why don't you just show user a sheet where they need to input the data and then have a button (i.e. Submit button). Once user clicks the button, you can validate the data? Using a form for data entry, then asking the user to confirm on a sheet and then click a button on a form.. just seems confusing — Zac, Jan 24 '19 at 13:37
@Zac I have thought of that, but my previousely collected data is in a pretty complex data structure that isn't so easy to save. I could do it but it be a pain, so I first look into a "stop the code, user controls the data, confirms and code contiues"-approach instead of comming up with a solution to save all the collected data from before. — Lucas Raphael Pianegonda, Jan 24 '19 at 13:44
@Zac The button could work.. But only if I could get the code to stop and wait for the `ButtonClick_event` to be triggered. Is there a way to do this? — Lucas Raphael Pianegonda, Jan 24 '19 at 13:49
Loving the last `Debug.Print` :).. From what I can see, the will go into a loop because you are setting the value of `.IsHidden` in the form and then checking the value once the form is closed. So that value will never get update and you have an `Endless loop`. Make the form `Modeless` as @Tom suggested. Have 2 buttons in the form. One to submit data to sheet and other to confirm once user is happy. Only then close your form — Zac, Jan 24 '19 at 13:54

Lucas Raphael Pianegonda · Accepted Answer · 2019-01-28T15:41:24.260

I got it!

The solution to the problem is the following: DoEvents

The Module

Sub ControlDataUI()
Dim Ui As New UserForm1
Dim IsConfirmed As Boolean

Debug.Print "dostuff"

Application.ScreenUpdating=True
With Ui
    .Show (0)
    While Not .IsHiden
        DoEvents
    Wend
    If .IsCancelled Then Exit Sub
    IsConfirmed = .Confirmed
End With

Debug.Print "Do some more stuff!"

If IsConfirmed Then
    Debug.Print "SaveStuff"
End If

Debug.Print "I will die!!"
End Sub

The UserForm

Private Type TView
    IsCancelled As Boolean
    Confirmed As Boolean
    IsHiden As Boolean
End Type

Private this As TView
Public Property Get IsCancelled() As Boolean
    IsCancelled = this.IsCancelled
End Property
Public Property Get Confirmed() As Boolean
    Confirmed = this.Confirmed
End Property
Public Property Get IsHiden() As Boolean
    IsHiden = this.IsHiden
End Property

Private Sub CommandButton1_Click()
Debug.Print "YES!!!!"
this.Confirmed = True
this.IsHiden = True
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Debug.Print "NO?!?!"
this.Confirmed = False
this.IsHiden = True
Me.Hide
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
this.IsCancelled = True
Me.Hide
End Sub

Private Sub UserForm_Terminate()
Debug.Print "Murder! I was killed!"
End Sub

So the idea was in the right direction but the while loop had to contain a DoEvents in order to process the events comming from the user.

Summary:

1. Modeless Userform

2. While loop with DoEvents until user confirmes

This allows for the user controll of the sheet and a confirmation at any time. The code will run after the user confirms the data is ok.

Thanks to @Tom and @Zac

FYI - you might be interested in the following links: [Destroy a modeless Userform instance properly](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly), [UserForm1.Show?](https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/) and [The perfect Userform](https://www.vitoshacademy.com/vba-the-perfect-userform-in-vba/) — T.M., Jan 31 '19 at 16:43

Which display mode should one use for a VBA UserForm in order to display data, controll it and confirm it is ok?

1 Answers1