|
1、插入一个用户窗体
 需要在用户窗体中加入按钮的，则需要打开“工具箱”   插入控件按钮 
2、代码编写
（1）、首先在sheet中添加一个按钮，来显示用户窗体，查看效果
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
鼠标单击CommandButton1出现如下窗口，这是未对窗口进行任何代码编写的效果。  （2）、在用户窗体中编写代码，打开编写界面  两个主要函数
Private Sub UserForm_Initialize()
code
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
code
End Sub
UserForm_Initialize是窗体初始化函数，对窗体的高度宽度等一系列初始化操作区域。只要使用UserForm1.Show就会调用这一函数。 UserForm_QueryClose是窗体点右上角那个x之后，将调用该函数。
（3）、实际应用 将实现的功能： 读取所有sheet的名字 显示在窗体上 通过在窗体上选择想要的sheet的名字，输出打印到sheet1的单元格A1中。 将按钮修改名称为“确定”，点击确定即为确定选择内容，点击右上角的x，即为放弃修改。 最终效果： 
Private Sub 确定_Click()
Sheet1.Range("A1:Z1").ClearContents
Call Process
Unload UserForm1
End Sub
Private Sub Process()
Dim TNum As Integer
Dim flag As Integer
flag = 0
TNum = 0
On Error Resume Next
For i = 0 To UserForm1.Controls.Count - 1
If UserForm1.Controls(i) = True Then
Sheet1.Cells(1, 1) = Sheet1.Cells(1, 1) & " " & UserForm1.Controls(i).Caption
End If
Next
End Sub
Private Sub UserForm_Initialize()
Dim sheetNameArr() As String
Dim sheetnum As Integer
For Each sht In Worksheets
sheetnum = sheetnum + 1
Next
ReDim sheetNameArr(0 To sheetnum)
sheetnum = 0
For Each sht In Worksheets
sheetNameArr(sheetnum) = sht.Name
sheetnum = sheetnum + 1
Next
sheetnum = UBound(sheetNameArr) + 1
'Fix是向下取整
counts = Fix(sheetnum / 10) + 1
'设置窗体高度和宽度
UserForm1.Width = 150 * counts '每增加1列宽度加150。
If sheetnum 10 Then UserForm1.Height = 250
'设置按钮的高度宽度以及相对窗体的位置
UserForm1.确定.Width = 100
UserForm1.确定.Height = 25
UserForm1.确定.Top = UserForm1.Height - 50
UserForm1.确定.Left = (UserForm1.Width - 100) / 2
For X = 1 To counts
Y = 10 * X '每列显示10个控件
If X = 1 Then '开始第1列显示
Start = 2
Ends = 11
Else '换列显示
Start = Ends + 1
Ends = Y + 1
End If
If Ends > sheetnum Then '防止溢出
Ends = sheetnum
End If
For i = Start To Ends '循环生成控件
Set ChkBox = UserForm1.Controls.Add("Forms.CheckBox.1", "Chk" & Str(i))
If i > 11 Then
tops = 2 + 20 * (i - Y + 8) '换列后的控件高度坐标
Else
tops = 2 + 20 * (i - 2)
End If
ChkBox.Top = tops
ChkBox.Height = 25 '控件的高度
ChkBox.Width = 200 '控件的宽度
ChkBox.Left = 20 + 150 * (X - 1) '220 换列后的横坐标
ChkBox.Caption = sheetNameArr(i - 2) '控件显示的标题
Set ChkBox = Nothing
Next
Next
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
End Sub
https://download.csdn.net/download/qq_39629993/12693907
|