• 1

• 145.5k
• 22
• 190
• 478

How to work with Handling dialog closure in a new instance every time - part 2VBA user form

• 329
• 5
• 21

I already have a question, concerning working with new instance of a form every time here - Working with a new form instance every time

As far as I have decided, that the most important part of the code is to work with the instance of the form and to learn how to handle them properly, I have remade the code (roughly using the code from @Mat's Mug) in a way that I am looking for a type of a boilerplate for forms, that I will probably use later.

Thus, let's start. I have a form, that looks like this: enter image description here

Buttons are called btnStart, btnExit and lblInfo.

This is the code behind the frmMain:

Option Explicit
Public Event OnRunReport()
Public Event OnExit()
Private Sub btnRun_Click()
 RaiseEvent OnRunReport
End Sub
Private Sub btnExit_Click()
 RaiseEvent OnExit
End Sub
Private Sub UserForm_QueryClose(CloseMode As Integer, Cancel As Integer)
 
 End
 
End Sub

This is the code behind the modMain:

Option Explicit
Private objPresenter As clsSummaryPresenter
Public Sub MainGenerateReport()
 
 Call objPresenter.ChangeLabelAndCaption("Starting and running...", "Running...")
 GenerateNumbers
 
End Sub
Public Sub GenerateNumbers()
 
 Dim lngLong As Long
 Dim lngLong2 As Long
 
 tblMain.Cells.Clear
 
 For lngLong = 1 To 3000
 For lngLong2 = 1 To 10
 tblMain.Cells(lngLong, lngLong2) = lngLong * lngLong2
 Next lngLong2
 Next lngLong
End Sub
Public Sub ShowMainForm() 'CTRL+E
 If (objPresenter Is Nothing) Then Set objPresenter = New clsSummaryPresenter
 objPresenter.Show
End Sub

This is the code behind the clsSummaryPresenter:

 Option Explicit
 
 Private WithEvents objSummaryForm As frmMain
 
 Private Sub Class_Initialize()
 
 Set objSummaryForm = New frmMain
 
 End Sub
 
 Private Sub Class_Terminate()
 
 Set objSummaryForm = Nothing
 
 End Sub
 
 Public Sub Show()
 
 If Not objSummaryForm.Visible Then
 objSummaryForm.Show vbModeless
 objSummaryForm.lblInfo = "Press Run to Start"
 objSummaryForm.Caption = "Starting"
 End If
 
 End Sub
 
 Public Sub Hide()
 
 If objSummaryForm.Visible Then objSummaryForm.Hide
 
 End Sub
 Public Sub ChangeLabelAndCaption(strLabelInfo As String, strCaption As String)
 objSummaryForm.lblInfo = strLabelInfo
 objSummaryForm.Caption = strCaption
 objSummaryForm.Repaint
 
 End Sub
 Private Sub objSummaryForm_OnRunReport()
 MainGenerateReport
 Refresh
End Sub
Private Sub objSummaryForm_OnExit()
 
 Hide
End Sub
Public Sub Refresh()
 
 With objSummaryForm
 .lblInfo = "Ready"
 .Caption = "Task performed"
 End With
End Sub

What I am not sure (and I did not understand) was how to handle the red X in a way that there is no error after it. Thus, I have decided to use the End, as far as it works so far. If you are willing to take a look at the excel file - it is here - https://github.com/Vitosh/VBA_personal/blob/master/FormWithAnInstanceVBA/Form001.xlsb

Still, it is VBA w/o password, but make sure that you first disable all macros on your Office before opening a file from VBA file Excel file from Internet.