工作表中插入Shape对象控件1
With Sheets(1).AddFormControl(x,Left,Top,Width,Height)
x为:
xlButtonControl
xlCheckBox
xlDropDown
xlLabel
xlListBox
xlOptionButton
xlScrollBar
xlSpinner
End With
工作表中插入Shape对象控件2
Sheets(1).x.Add(Left,Top,Width,Height)
x为:
Buttons
CheckBoxes
DropDowns
Labels
ListBoxes
OptionButtons
ScrollBars
Spinners
Shape对象按钮控件设置
With Sheets(1).Shapes(1)
.TextFrame.Characters.Text=”***”
.Width=n
.Height=n
.Top=n
.Left=n
With .TextFrame.Characters
.Text=”技巧7_017″
.Font.Size=26
.Font.ColorIndex=3
End With
End With
Shape对象复选框与选项按钮控件设置
With Sheets(1).Shapes(1)
.Delete
.ControlFormat.Value=1
.ControlFormat.Value=-4146
End With
Shape对象复合框与列表框控件设置
With Sheets(1).Shapes(1)
.ControlFormat.ListFillRange=Sheets(1).Name&!A1:A2
.ControlFormat.AddItemCells(i,1).value
.List=Cells(i,1).value
.ControlFormat.LinkedCell=ws.Name&!A1
Sheets(1).[a1]=.ControlFormat.List(.ControlFormat.Value)
.OnAction=”宏名”
End With
工作表中插入控件
SetmyShape=Sheets(1).OLEObjects.Add(ClassType:=”x”,Left:=n,Top:=n,Width:=n,Height:=n)
x为:
Forms.TextBox.1
Forms.CommandButton.1
Forms.CheckBox.1
Forms.ComboBox.1
Forms.Label.1
Forms.ListBox.1
Forms.OptionButton.1
Forms.ScrollBar.1
Forms.SpinButton.1
Forms.ToggleButton.1
删除工作表中控件
Sheets(1).OLEObjects(1).Delete
按钮控件设置
With Sheets(1).CommandButton1
.Caption=”***”
.Font.Name=”字形名称”
.Font.Bold=True
.ForeColor=RGB(红,绿,蓝)/x
.BackColor=RGB(红,绿,蓝)/x
EndWith
x为:
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite
文本框控件设置
With Sheets(1).TextBox1
.Value=”***”
.Value=””
End With
复合框与列表框控件设置
With Sheets(1).ListBox1/ComboBox1
.ListFillRange=Sheets(1).Name&!A1:A2
.Object.AddItemCells(i,1).value
.Object.List=Cells(i,1).value
Sheets(1).[a1]=.Object.Value
End With
复选框与选项按钮控件设置
With Sheets(1).OptionButton1/CheckBox1
.Object.Value=True
.Object.Value=False
End With
微调项滚动条控件设置
With Sheets(1).SpinButton1/ScrollBar1
.LinkedCell=”A1″
.Object.Min=0
.Object.Max=1000
.Value=[A1].Value
End With
标签控件设置
With Sheets(1).Label1
.Caption=”***”
.TextAlign=x
End With
x为:
fmTextAlignLeft
fmTextAlignCenter
fmTextAlignRight
框架控件设置
With Sheets(1).Frame1
.Caption=”***”
.Caption=””
.Font.Name=”字形名称”
End With
图象控件设置
With Sheets(1).Image1
.Picture=LoadPicture(“路径”)
.Picture=Nothing
End With
用户窗体操作
Load UserForm1
UserForm1.Show
UserForm1.Hide
UnloadUserForm1/Me
用户窗体显示模式
With UserForm1
.ShowvbModeless/0
.ShowvbModal/1
End With
用户窗体显示位置1
With UserForm1
.StartUpPosition=0
.StartUpPosition=1
.StartUpPosition=2
.StartUpPosition=3
End With
用户窗体显示位置2
With UserForm1
.StartUpPosition=0
.Left=n
.Top=n
End With
用户窗体属性设置 UserForm1.Caption=”***”
[X]关闭按钮操作
a=FindWindow(“ThunderDFrame”,Me.Caption)
b=GetWindowLong(a,GWL_STYLE)
c=SetWindowLong(a,GWL_STYLE,bAnd(NotWS_SYSMENU))
c=DrawMenuBar(a)
a=FindWindow(“ThunderDFrame”,Me.Caption)
b=GetWindowLong(a,GWL_STYLE)
c=SetWindowLong(a,GWL_STYLE,bOrWS_SYSMENU)
c=DrawMenuBar(a)
a=FindWindow(“ThunderDFrame”,Me.Caption)
b=GetSystemMenu(a,0&)
c=DeleteMenu(b,SC_CLOSE,MF_BYCOMMAND)
c=DrawMenuBar(a)
a=FindWindow(“ThunderDFrame”,Me.Caption)
b=GetSystemMenu(a,1&)
c=DrawMenuBar(a)
最大/小化按钮操作
a=FindWindow(“ThunderDFrame”,Me.Caption)
b=GetWindowLong(a,GWL_STYLE)
c=SetWindowLong(a,GWL_STYLE,bOrWS_THICKFRAMEOrWS_MAXIMIZEBOXOrWS_MINIMIZEBOX)
c=DrawMenuBar(a)
a=FindWindow(“ThunderDFrame”,Me.Caption)
b=GetWindowLong(a,GWL_STYLE)
c=SetWindowLong(a,GWL_STYLE,bAnd(Not(WS_THICKFRAMEOrWS_MAXIMIZEBOXOrWS_MINIMIZEBOX)))
c=DrawMenuBar(a)
返回用户窗体控件数
UserForm1/Me.Controls.Count
选定用户窗体控件属性
With 用户窗体控件
.Name
.Object.Value
.Height
.Width
.Left
.Top
.TabIndex
End With
用户窗体中添加/删除控件
For i=1 To n
With Me.Controls.Add(bstrprogid:=”x”,Name:=”TextBok”&i,Visible:=True
x为:
Forms.TextBox.1
Forms.CommandButton.1
Forms.CheckBox.1
Forms.ComboBox.1
Forms.Label.1
Forms.ListBox.1
Forms.OptionButton.1
Forms.ScrollBar.1
Forms.SpinButton.1
Forms.ToggleButton.1
.Width=n
.Height=n
.Top=n
.Left=n
.RemoveTextBox&i
End With
Next i
用户窗体文本框设置
With TextBox1
.ControlTipText=”文本”
.MousePointer=x
x为:
fmMousePointerDefault0
fmMousePointerArrow1
fmMousePointerCross2
fmMousePointerIBeam3
fmMousePointerSizeNESW6
fmMousePointerSizeNS7
fmMousePointerSizeNWSE8
fmMousePointerSizeWE9
fmMousePointerUpArrow10
fmMousePointerHourglass11
fmMousePointerNoDrop12
fmMousePointerAppStarting13
fmMousePointerHelp14
fmMousePointerSizeAll15
fmMousePointerCustom99
.SetFocus
.TextAlign=x
x为:
fmTextAlignLeft
fmTextAlignCenter
fmTextAlignRight
.MaxLength=n
(KeyAscii>=Asc(“0”)OrKeyAscii<=Asc("9"))AndKeyAscii=Asc(".")
(KeyAscii>=Asc(“a”)OrKeyAscii=Asc(“A”)OrKeyAscii<=Asc("Z"))
.MultiLine=False/True
End With
用户窗体按钮设置
With CommandButton1
.Picture=LoadPicture路径/文件名
.PicturePosition=fmPicturePositionLeftCenter
.Default=True
用户窗体组合框设置
With ComboBox1
.RowSource=Sheets(1).Name&!a1:b2
.ColumnCount=n
.ColumnHeads=True
End With
With ComboBox1
.AddItema
.AddItemb
End With
a=Sheets(1).Range(“A1:B2”).Value
With ComboBox1
.List=a
.ColumnCount=n
.ColumnHeads=True
End With
a=Sheets(1).Range(“A1:B2”).Value
a=WorksheetFunction.Transpose(a)
With ComboBox1
.Column=a
.ColumnCount=n
.ColumnHeads=True
End With
With ComboBox1
.Clear
.RemoveItemn
.ListIndex=-1
.MatchFound=False/True
End With
用户窗体列表框设置
With ListBox1
.RowSource=Sheets(1).Name&!a1:b2
.ColumnCount=n
.ColumnHeads=True
End With
With ListBox1
.AddItema
.AddItemb
End With
a=Sheets(1).Range(“A1:B2”).Value
With ListBox1
.List=a
.ColumnCount=n
.ColumnHeads=True
End With
a=Sheets(1).Range(“A1:B2”).Value
a=WorksheetFunction.Transpose(a)
With ListBox1
.Column=a
.ColumnCount=n
.ColumnHeads=True
End With
With ListBox1
.Clear
.RemoveItem.ListIndex
.ListIndex=-1
.MultiSelect=fmMultiSelectSingle
.MultiSelect=fmMultiSelectMulti
用户窗体微调项/滚动条设置
With SpinButton1
.Max=n
.Min=m
.SmallChange=a
.Value=b
End With
With ScrollBar1
.Max=n
.Min=m
.SmallChange=a
.LargeChange=b
.Value=ActiveWindow.VisibleRange.Cells(1).Row
End With
用户窗体DTPicker设置
With DTPicker1
.Format=x
x为:
dtpShortDate
dtpLongDate
dtpTime
.Value=Date/Now
End With
用户窗体进度条设置
With ProgressBar1
.Min=1
.Max=n
For i=1 To n
.Value=i
DoEvents
Next i
用户窗体ListView设置
With ListView1
.ColumnHeaders.Clear
.ListItems.Clear
.View=lvwReport
.FullRowSelect=True/False
.Gridlines=True/False
.ColumnHeaders.Add,,列标名,列宽,lvwColumnCenter
End With
For i=1To x
ListView1.ListItems.Add,,Cells(i+1,1)
ListView1.ListItems(i).SubItems(1)=Cells(i+1,2)
ListView1.ListItems(i).SubItems(2)=Cells(i+1,3)
Next i
ListView1.SelectedItem.ListSubItems(n).Text
用户窗体TreeView设置
With TreeView1
.Nodes.Clear
.LineStyle=tvwRootLines/tvwTreeLines
.Style=tvwTreelinesPlusMinusText
.LabelEdit=tvwManual/tvwAutomatic
End With
Setnodx=TreeView1.Nodes.Add(,,目录,目录)
Setnodx=TreeView1.Nodes.Add(“目录”,tvwChild,一级目录,一级目录)
Setnodx=TreeView1.Nodes.Add(“一级目录”,tvwChild,二级目录,二级目录)
Setnodx=TreeView1.Nodes.Add(“二级目录”,tvwChild,三级目录,三级目录)
With TreeView1
.SelectedItem.Text
If .SelectedItem.Index2 Then
.Nodes.RemoveTreeView1.SelectedItem.Index
End If
For i=1 To .Nodes.Count
.Nodes(i).Expanded=True/False
Next i
.Sorted=True
Setnodx=.Nodes.Add(TreeView1.SelectedItem.Text,tvwChild,内容,内容)
End With