二、窗体
1. 使用窗体
窗体的常用属性和方法(UserForm):
窗体常用属性
- Enabled 可用性
- Caption 标题
- Visible 可见性
- ShowModal 显示模式
窗体常用方法
- Show 显示
- Hide 隐藏
窗体常用事件
- UserForm_Activate 激活
- UserForm_QueryClose 退出
2. 文本框和复选按钮 |
3. 组合框和列表框 |
文本框控件TextBox文本框常用属性
复选框控件CheckBox复选框常用属性
|
复合框控件ComboBoxComboBox常用属性
ComboBox常用方法
列表框控件ListBox列表框常用属性
列表框常用方法
|
4. 课程小结及课后练习
本节演示案例
1)制作登录窗口
Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheet2.Visible = xlSheetVeryHidden Sheet3.Visible = xlSheetVeryHidden Sheet2.Protect "test" Sheet3.Protect "test" End Sub Private Sub Workbook_Open() UserForm1.Show End Sub |
Private Sub CommandButton1_Click() If UserForm1.TextBox1.Value = "张三" And UserForm1.TextBox2.Value = "123" Then Sheet2.Unprotect Password:="test" Sheet2.Visible = xlSheetVisible Me.Hide ElseIf UserForm1.TextBox1.Value = "李四" And UserForm1.TextBox2.Value = "0000" Then Sheet3.Unprotect Password:="test" Sheet3.Visible = xlSheetVisible Me.Hide End If End Sub |
2)制作程序界面
3)联想输入功能
Private Sub TextBox1_Change() If Len(Me.TextBox1.Value) >= 4 Then Me.ListBox1.Clear For i = 2 To 8 If InStr(Sheet1.Range("i" & i), Me.TextBox1.Value) > 0 Then Me.ListBox1.AddItem Sheet1.Range("i" & i) End If Next If Me.ListBox1.ListCount > 0 Then Me.ListBox1.Visible = True Else Me.ListBox1.Visible = False End If Else Me.ListBox1.Visible = False End If End Sub |
需要理解并记住写法的概念
UserForm1.Show .Hide
需要理解的概念
UserForm_Activate 事件 UserForm_QueryClose 事件
UserForm1.Show .Hide
TextBox(文本框) CheckBox(复选框) ComboBox(下拉框) ListBox (列表框)
联想输入的原理
课后练习
会员信息查询
问题:
完成会员信息查询系统的查询功能。
Private Sub CommandButton1_Click() Dim rng As Range '用find方法做 Set rng = Sheet1.Range("i1:i1000").Find(Me.TextBox1.Value) If rng Is Nothing Then MsgBox "无该用户" Else Me.Label3.Caption = rng.Offset(0, -6) Me.Label4.Caption = rng.Offset(0, -5) Me.Label6.Caption = rng.Offset(0, -4) Me.Label8.Caption = rng Me.Label10.Caption = rng.Offset(0, -3) Me.Label12.Caption = rng.Offset(0, -2) Me.Label13.Caption = rng.Offset(0, -1) End If End Sub Private Sub ListBox1_Click() Me.TextBox1 = Me.ListBox1.Value Me.ListBox1.Visible = False End Sub Private Sub TextBox1_Change() Dim arr() If Len(TextBox1.Value) >= 4 Then Me.ListBox1.Clear arr = Sheet1.Range("i2:i" & Sheet1.Range("a65536").End(xlUp).Row) For i = LBound(arr) To UBound(arr) If InStr(arr(i, 1), Me.TextBox1.Value) > 0 Then Me.ListBox1.AddItem arr(i, 1) End If Next If Me.ListBox1.ListCount > 0 Then Me.ListBox1.Visible = True End If Else Me.ListBox1.Clear Me.ListBox1.Visible = False End If End Sub Private Sub UserForm_Activate() End Sub |
续: