VBA menu的制作
来源:互联网 发布:js防水涂料2mm厚 编辑:程序博客网 时间:2024/05/16 15:55
- Sub 创建菜单项()
- Dim MenuObject As CommandBarPopup
- Dim MenuItem As Object
- Call 删除菜单
- Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, before:=11, temporary:=True)
- MenuObject.Caption = "泰星账务(&X)"
- Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
- MenuItem.Caption = "刷新菜单"
- MenuItem.OnAction = "刷新"
- Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
- MenuItem.Caption = "8月份生产日报表"
- MenuItem.OnAction = "打开8月份生产日报表"
- Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
- MenuItem.Caption = "9月份生产日报表"
- MenuItem.OnAction = "打开9月份生产日报表"
- Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
- MenuItem.Caption = "10月份生产日报表"
- MenuItem.OnAction = "打开10月份生产日报表"
- Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
- MenuItem.Caption = "11月份生产日报表"
- MenuItem.OnAction = "打开11月份生产日报表"
- Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
- MenuItem.Caption = "12月份生产日报表"
- MenuItem.OnAction = "打开12月份生产日报表"
- Set Menu = MenuObject.Controls.Add(Type:=msoControlPopup)
- Menu.Caption = "2011年账务"
- Set obj = Menu.Controls.Add(Type:=msoControlButton)
- obj.Caption = "1月份日报表"
- obj.OnAction = "打开11年1月份日报表"
- Set obj = Menu.Controls.Add(Type:=msoControlButton)
- obj.Caption = "2月份日报表"
- obj.OnAction = "打开11年2月份日报表"
- Set obj = Menu.Controls.Add(Type:=msoControlButton)
- obj.Caption = "3月份日报表"
- obj.OnAction = "打开11年3月份日报表"
- Set obj = Menu.Controls.Add(Type:=msoControlButton)
- obj.Caption = "4月份日报表"
- obj.OnAction = "打开11年4月份日报表"
- Set obj = Menu.Controls.Add(Type:=msoControlButton)
- obj.Caption = "5月份日报表"
- obj.OnAction = "打开11年5月份日报表"
- Set obj = Menu.Controls.Add(Type:=msoControlPopup)
- obj.Caption = "6月份日报表"
- Set MenuItem = obj.Controls.Add(Type:=msoControlButton)
- With MenuItem
- .Caption = "查询(&F)..."
- .FaceId = 1849
- .OnAction = "打开查询"
- End With
- Set MenuItem = obj.Controls.Add(Type:=msoControlButton)
- With MenuItem
- .Caption = "合并"
- .FaceId = 1826
- .OnAction = "合并"
- End With
- Set MenuItem = obj.Controls.Add(Type:=msoControlButton)
- With MenuItem
- .Caption = "生成工资表"
- .FaceId = 1742
- .OnAction = "生成工资表"
- End With
- Set Menu = Nothing
- Set MenuItem = Nothing
- Set MenuObject = Nothing
- End Sub
- Sub 打开查询()
- Sheets("工资明细表查询").Select
- 删除快捷菜单
- UserForm1.Show
- End Sub
- Sub 删除菜单()
- On Error Resume Next
- Application.CommandBars(1).Controls("泰星账务(&X)").Delete
- On Error GoTo 0
- End Sub
- Sub 生成工资表()
- Dim x As Integer
- For x = 1 To Sheets.Count
- If Sheets(x).Name = "工资表" Then
- GoTo 100
- End If
- Next x
- Set NewSheet = Worksheets.Add
- NewSheet.Name = "工资表"
- 100:
- With Sheets("工资表")
- .Move After:=Sheets(Sheets.Count)
- .Cells.ClearContents
- .[a1] = "姓名": .[b1] = "数量": .[c1] = "金额"
- End With
- Dim objcn As New ADODB.Connection
- objcn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source= " & ThisWorkbook.FullName
- sql1 = "select 姓名,数量,金额 from [下料车间$]"
- sql2 = "select 姓名,数量,金额 from [五金车间$]"
- sql3 = "select 姓名,数量,金额 from [针车车间$]"
- sql4 = "select 姓名,数量,金额 from [油边车间$]"
- sql5 = "select 姓名,数量,金额 from [组装车间$]"
- Sql = sql1 & " union all " & sql2 & " union all " & sql3 & " union all " & sql4 & " union all " & sql5
- sql6 = "select 姓名,sum(数量),sum(金额) from (" & Sql & ") group by 姓名 order by 姓名"
- Sheets("工资表").[a2].CopyFromRecordset objcn.Execute(sql6)
- objcn.Close
- Set objcn = Nothing
- End Sub
- Sub 合并()
- Sheets("工资明细表查询").Select
- Dim objcn As New ADODB.Connection
- Dim hs As Integer
- Application.ScreenUpdating = False
- Cells.ClearContents
- [a1] = "日期": [b1] = "订单号": [c1] = "货号"
- [d1] = "工序": [e1] = "单价": [f1] = "数量": [g1] = "金额"
- [h1] = "姓名": [i1] = "备注"
- objcn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source= " & ThisWorkbook.FullName
- Sql = "select * from [下料车间$] union all select * from [五金车间$] union all select * from [针车车间$] union all select * from [组装车间$] union all select * from [油边车间$]"
- [a2].CopyFromRecordset objcn.Execute(Sql)
- objcn.Close
- Set objcn = Nothing
- hs = [a65536].End(xlUp).Row + 1
- Cells(hs, 1) = "合计"
- Cells(hs, 6).Formula = "=subtotal(9,f2:f" & hs - 1 & ")"
- Cells(hs, 7).Formula = "=SUBTOTAL(9,G2:G" & hs - 1 & ")"
- Application.ScreenUpdating = True
- End Sub
- Sub 添加快捷菜单()
- On Error Resume Next
- Application.CommandBars("cell").Controls("工资查询").Delete
- Application.CommandBars("cell").Controls("生成工资表").Delete
- Dim CB As CommandBarControl
- Dim CC As CommandBarControl
- Dim CA As Long
- CA = Application.CommandBars("cell").Controls("剪切(&T)").Index
- Set CB = Application.CommandBars("cell").Controls.Add(before:=CA, temporary:=True)
- CB.Caption = "工资查询"
- CB.FaceId = 1849
- CB.OnAction = "打开查询"
- Set CC = Application.CommandBars("cell").Controls.Add(before:=CA, temporary:=True)
- CC.Caption = "生成工资表"
- CC.FaceId = 1742
- CC.OnAction = "生成工资表"
- End Sub
- Sub 删除快捷菜单()
- On Error Resume Next
- Application.CommandBars("cell").Controls("工资查询").Delete
- Application.CommandBars("cell").Controls("生成工资表").Delete
- End Sub
- Sub 刷新()
- Application.Run "创建菜单.xla!创建菜单"
- End Sub
- VBA menu的制作
- 一个弹出式menu的制作
- VBA 控制 Excel 制作表格的例子
- android-利用Animation制作的Menu
- Multiview+menu制作
- 教师平时分统计软件----Excel----VBA制作的。
- 利用VBA编程制作互动效果的PPT
- 使用Office2003 VBA制作有倒计时功能的PPT
- 利用Word VBA制作选择题
- VBA加载宏制作攻略
- VBA CAD 菜单制作攻略
- 用VBA完成报表制作
- 使用Menu制作弹出菜单
- 利用ASP.NET的menu和SQL数据库制作无限级别菜单
- 简易电子章制作小模块(VBA)
- 用CSS制作苹果Dock Menu
- menu控件制作下拉式导航菜单
- 用css3制作一个Music Player Menu
- c#获取当前日期时间
- eclipse中SVN更改账号的方法
- Android APK反编译详解(附图)
- 点击单选按钮 实现表格的隔行变色
- Linux设备模型之input子系统详解
- VBA menu的制作
- 建立内核模块编译环境
- 使用valgrind检测Android native程序的内存
- Solaris 10 安装 RAID1+ RAID5 +热备池
- Bean 工厂(AOP)
- How to hijack domain name in windows - 如何实现 Windows 本地域名劫持
- 选中checkbox框 实现表格的隔行变色(二)
- 谈谈关于网页设计中的色彩理论
- Android组件 多项选择(CheckBox)