Can this be done more efficiently?
The code does the following:
- Do Stuff
- Show UserForm and ask user to confirm the shown data is ok
- User can manipulate and controll excel as usual
- As soon as user clicks the "yes" button "do some other stuff" is called
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
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.
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.