March 31, 2022

What is Modeless UserForm

What is a modeless UserForm and how to create it automatically using a macro. Watch the video below:

Create modeless UserForm with VBA

UserForms have a property called ShowModal. The image below shows the default setting for the ShowModal property:

ShowModal default property is True
ShowModal default property is True

The ShowModal property can be changed to False and the UserForm becomes modeless. This means that the user can access the worksheet cells, Ribbon, etc. even while the UserForm is open.

We can automate the process of making the UserForm modeless quickly and easily by using the following codes:

Sub ShowUserForm()
UserForm1.Show vbModeless
End Sub

Sub ShowUserForm()
‘vbModeless=0

UserForm1.Show 0
End Sub

We can also attach the module ShowUserForm to a form button on the worksheet as shown in the training video.

The code for the ‘Transfer Data’ button is also given below:

Private Sub CommandButton1_Click()
Dim LastRow As Long
Range(“A:B”).Columns.AutoFit
LastRow = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
‘MsgBox LastRow
Range(“A” & LastRow).Offset(1, 0) = TextBox1.Text
Range(“B” & LastRow).Offset(1, 0) = TextBox2.Text
End Sub

Further Reading:

ShowModal property