Thursday, December 5, 2019
https://www.excel-easy.com/vba/userform.html
1. Begin / End
・UserForm1.Show
Display userform
・UserForm1.Hide
Do not display userform, form's data are remain
・Unload UserForm1
Destroy userform (userform do not display and all form'data also not exist)
Option Explicit
Sub main()
With UserForm1
'Display form dialog
.Show
'Some code
Debug.Print "Some code"
End With
'Destroy form
Unload UserForm1
End Sub
2.UserForm component
2.1 ComandButton
Double click on button, source code of click event will be display as below:
Private Sub CommandButton_CANCEL_Click()
' Unload Me
Hide
End Sub
Private Sub CommandButton_OK_Click()
'some code
Hide
End Sub
2.2 Label
Label1 = "some string"
2.3 InputTextBox
TextBox1.Value = "some string"
2.4 ListBox
'Empty ListBox1
ListBox1.Clear
'Fill ListBox1
With ListBox1
.AddItem "San Francisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With
'get ListBox1
Cells(emptyRow, 3).Value = ListBox1.Value2.5 ComboBox
'Empty ComboBox1
ComboBox1.Clear
'Fill ComboBox1
With ComboBox1
.AddItem "San Francisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With
'set defaul item to 1st item (San Francisco)
Combobox1.ListIndex=0
'get ComboBox1 by value
Cells(emptyRow, 3).Value = ComboBox1.Value 'San Francisco because ListIndex=0
'get ComboBox1 by Index
Cells(emptyRow, 3).Value = ComboBox1.List(1) 'Oakland2.6 CheckBox, OptionButton(radio button)
ChekcBox and OptionButton are the same in set/get value
'set init
CheckBox1.Value = False
'some code
'...
'get setting
If CheckBox1.Value = True Then
'do something 1
Else
'do something 2
End If3. Progress dialog
Application.ScreenUpdating = True
progressCounter = progressCounter + 1
UserForm1.Label_progress.Caption = "test msg"
UserForm1.Label_filename.Caption = "test msg"
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
Application.ScreenUpdating = False
