|
楼主 |
发表于 2009-7-19 11:31
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
第11部分 其他应用
技巧197 收据系统
步骤3,将Sheet3工作表名称重命名为“存档”,设置成如图所示的格式,用来保存已填写收据的数据。
步骤4,“存档”表只是用来保存已开具收据的数据,而收据系统的日常操作都在“主界面”窗体中进行,在VBE窗口中单击菜单“插入”→“插入窗体”,在窗体中添加一个多页(MultiPage)控件和一个状态栏(StatusBar)控件。在MultiPage控件的Page1页上添加一个Image控件和三个标签控件,在第一个标签的属性窗口中将其BackStyle属性设置为0,使标签背景为透明,在其他两个标签的Caption属性中写上版本信息及作者和邮箱,在Image控件的属性窗口将其Picture属性设置为合适的图片并调整好控件的大小与位置。
双击主界面窗体,在打开的代码窗口写入下面的代码:- #001 Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
- #002 Private Declare Function SetMenu Lib "user32" (ByVal hwnd As Long, ByVal hMenu As Long) As Long
- #003 Private Declare Function CreateMenu Lib "user32" () As Long
- #004 Private Declare Function AppendMenu Lib "user32" Alias "AppendMenuA" (ByVal hMenu As Long, ByVal wFlags As Long, ByVal wIDNewItem As Long, ByVal lpNewItem As Any) As Long
- #005 Private Declare Function DestroyMenu Lib "user32" (ByVal hMenu As Long) As Long
- #006 Private Declare Function CreatePopupMenu Lib "user32" () As Long
- #007 Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
- #008 Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
- #009 Private Const GWL_WNDPROC = (-4)
- #010 Private Const MF_STRING = &H0&
- #011 Private Const MF_POPUP = &H10&
- #012 Private Const MF_SEPARATOR = &H800&
- #013 Dim MenuWnd As Long, Dump As Long, PopupMenuID As Long, PopupMenuWnd As Long, MenuID As Long
- #014 Private Sub UserForm_Initialize()
- #015 Dim i As Integer
- #016 Dim str As String
- #017 Dim arr As Variant
- #018 If Val(Application.Version) < 9 Then
- #019 hwnd = FindWindow("ThunderXFrame", Me.Caption)
- #020 Else
- #021 hwnd = FindWindow("ThunderDFrame", Me.Caption)
- #022 End If
- #023 MenuWnd = CreateMenu()
- #024 PopupMenuID = CreatePopupMenu()
- #025 Dump = AppendMenu(MenuWnd, MF_STRING + MF_POPUP, PopupMenuID, "系统(&S)")
- #026 Dump = AppendMenu(PopupMenuID, MF_STRING, 100, "单位设置(&C)")
- #027 Dump = AppendMenu(PopupMenuID, MF_STRING, 101, "用户设置(&U)")
- #028 Dump = AppendMenu(PopupMenuID, MF_STRING, 102, "切换用户(&S)")
- #029 Dump = AppendMenu(PopupMenuID, MF_STRING, 103, "密码修改(&P)")
- #030 Dump = AppendMenu(PopupMenuID, MF_STRING, 104, "打开Excel(&O)")
- #031 Dump = AppendMenu(PopupMenuID, MF_STRING, 105, "退出系统(&Q)")
- #032 PopupMenuID = CreatePopupMenu()
- #033 Dump = AppendMenu(MenuWnd, MF_STRING + MF_POPUP, PopupMenuID, "编辑(&E)")
- #034 Dump = AppendMenu(PopupMenuID, MF_STRING, 110, "收款人(&R)")
- #035 Dump = AppendMenu(PopupMenuID, MF_STRING, 111, "交款人(&P)")
- #036 Dump = AppendMenu(PopupMenuID, MF_STRING, 112, "交款单位(&U)")
- #037 Dump = AppendMenu(PopupMenuID, MF_STRING, 113, "常用事由(&S)")
- #038 Dump = AppendMenu(PopupMenuID, MF_STRING, 114, "常用备注(&R)")
- #039 PopupMenuID = CreatePopupMenu()
- #040 Dump = AppendMenu(MenuWnd, MF_STRING + MF_POPUP, PopupMenuID, "操作(&O)")
- #041 Dump = AppendMenu(PopupMenuID, MF_STRING, 120, "增加(&I)")
- #042 Dump = AppendMenu(PopupMenuID, MF_STRING, 121, "查询(&Q)")
- #043 PopupMenuID = CreatePopupMenu()
- #044 Dump = AppendMenu(MenuWnd, MF_STRING + MF_POPUP, PopupMenuID, "帮助(&H)")
- #045 Dump = AppendMenu(PopupMenuID, MF_STRING, 130, "帮助(&H)")
- #046 Dump = AppendMenu(PopupMenuID, MF_STRING, 131, "关于(&R)")
- #047 Dump = SetMenu(hwnd, MenuWnd)
- #048 PreWinProc = GetWindowLong(hwnd, GWL_WNDPROC)
- #049 SetWindowLong hwnd, GWL_WNDPROC, AddressOf MsgProcess
- #050 Me.MultiPage1.Value = 0
- #051 For i = 2 To Sheet2.Range("A65536").End(xlUp).Row
- #052 If Sheet2.Cells(i, 3) = "√" Then
- #053 str = Sheet2.Cells(i, 1)
- #054 End If
- #055 Next
- #056 arr = Array(168, 100, 100, 80)
- #057 With Me.StatusBar1
- #058 For i = 1 To 4
- #059 .Panels.Add(i, , "").Style = 0
- #060 .Panels(i).Width = arr(i - 1)
- #061 Next
- #062 .Panels(1).Text = Sheet2.Cells(2, 4) & "收据系统"
- #063 .Panels(2).Text = "当前用户:" & str
- #064 .Panels(3).Text = "今天是" & Format(Date, "yyyy年m月d日")
- #065 .Panels(4).Text = Time
- #066 End With
- #067 Me.Caption = Sheet2.Cells(2, 4) & "收据系统"
- #068 Me.Label12.Caption = Sheet2.Cells(2, 4) & "收据系统"
- #069 End Sub
- #070 Private Sub UserForm_Terminate()
- #071 DestroyMenu MenuWnd
- #072 DestroyMenu PopupMenuID
- #073 DestroyMenu PopupMenuWnd
- #074 SetWindowLong hwnd, GWL_WNDPROC, PreWinProc
- #075 End Sub
复制代码 代码解析:
主界面窗体的Initialize事件过程,窗体显示时使用API函数添加菜单及状态栏信息。
第1行到第13行代码,API函数声明。
第18行到第49行代码,使用API函数添加菜单。请参阅技巧148 。
第50行代码,窗体显示时选择MultiPage控件的第一页。
第51行到第55行代码,将当前用户名赋给字符串变量str。
第56行到第66行代码,在状态栏控件中添加四个窗格及显示的信息。请参阅技巧152 。
第67、67行代码,设置主界面窗体的标题栏及标签显示的内容。
步骤5,为了使用主界面窗体中添加的菜单,在VBE窗口中单击菜单“插入”→“模块”,在打开的代码窗口写入下面的代码:- #001 Public PreWinProc As Long, hwnd As Long
- #002 Public Declare Function CheckMenuRadioItem Lib "user32" (ByVal hMenu As Long, ByVal un1 As Long, ByVal un2 As Long, ByVal un3 As Long, ByVal un4 As Long) As Long
- #003 Public Declare Function CheckMenuItem Lib "user32" (ByVal hMenu As Long, ByVal wIDCheckItem As Long, ByVal wCheck As Long) As Long
- #004 Public Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long
- #005 Public Const MF_UNCHECKED = &H0&
- #006 Public Const MF_CHECKED = &H8&
- #007 Public Const MF_DISABLED = &H2&
- #008 Public Const MF_GRAYED = &H1&
- #009 Public Const MF_ENABLED = &H0&
- #010 Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
- #011 Private Declare Function GetMenu Lib "user32" (ByVal hwnd As Long) As Long
- #012 Private Declare Function GetSubMenu Lib "user32" (ByVal hMenu As Long, ByVal nPos As Long) As Long
- #013 Private Const MF_BYCOMMAND = &H0&
- #014 Public Function MsgProcess(ByVal hwnd As Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
- #015 Dim SubMenu_hWnd As Long
- #016 Select Case wParam
- #017 Case 100
- #018 If Sheet2.Cells(2, 3) <> "√" Then
- #019 MsgBox "请使用系统管理员登陆系统!", 64, "提示"
- #020 Else
- #021 设置使用单位.Show
- #022 End If
- #023 Case 101
- #024 If Sheet2.Cells(2, 3) <> "√" Then
- #025 MsgBox "请使用系统管理员登陆系统!", 64, "提示"
- #026 Else
- #027 用户设置.Show
- #028 End If
- #029 Case 102
- #030 Unload 主界面
- #031 登陆.Show
- #032 Case 103
- #033 修改密码.Show
- #034 Case 104
- #035 If Sheet2.Cells(2, 3) <> "√" Then
- #036 MsgBox "请使用系统管理员登陆系统!", 64, "提示"
- #037 Else
- #038 Unload 主界面
- #039 Application.Visible = True
- #040 End If
- #041 Case 105
- #042 If MsgBox("确定要退出收据系统吗?", 64 + vbYesNo, "提示") = vbYes Then
- #043 Unload 主界面
- #044 ThisWorkbook.Save
- #045 Application.Quit
- #046 End If
- #047 Case 110
- #048 主界面.MultiPage1.Value = 0
- #049 编辑收款人.Show
- #050 Case 111
- #051 主界面.MultiPage1.Value = 0
- #052 编辑交款人.Show
- #053 Case 112
- #054 主界面.MultiPage1.Value = 0
- #055 设置交款单位.Show
- #056 Case 113
- #057 主界面.MultiPage1.Value = 0
- #058 编辑常用事由.Show
- #059 Case 114
- #060 主界面.MultiPage1.Value = 0
- #061 编辑常用备注.Show
- #062 Case 120
- #063 主界面.MultiPage1.Value = 1
- #064 Case 121
- #065 主界面.MultiPage1.Value = 2
- #066 Case 130
- #067 帮助.Show
- #068 Case 131
- #069 关于.Show
- #070 Case Else
- #071 MsgProcess = CallWindowProc(PreWinProc, hwnd, Msg, wParam, lParam)
- #072 End Select
- #073 End Function
复制代码 代码解析:
根据主界面窗体中添加菜单的ID设置其各项功能。
第1行到第13行代码,API函数声明。
第17行到第22行代码,“系统”菜单中的“单位设置”菜单功能,显示“设置使用单位”窗体设置收据系统的使用单位名称。其中第18、19行代码判断当前用户是否是系统管理员,只有系统管理员才能设置收据系统的使用单位名称。设置单位名称需使用“设置使用单位”窗体,在VBE窗口中单击菜单“插入”→“插入窗体”,在窗体中添加一个框架控件、一个文本框控件及两个按钮按件,如图所示。
双击窗体中的“确定”按钮,写入下面的代码:- #001 Private Sub CommandButton1_Click()
- #002 If Sheet2.Cells(2, 4) <> "" Then
- #003 If MsgBox("是否重新设置单位名称?", 36, "提示") = vbNo Then
- #004 Unload Me
- #005 Exit Sub
- #006 End If
- #007 End If
- #008 If Trim(Me.Frame1.TextBox1.Value) = "" Then
- #009 MsgBox "单位名称不能为空!", 64, "提示"
- #010 Me.Frame1.TextBox1.SetFocus
- #011 Exit Sub
- #012 End If
- #013 Sheet2.Cells(2, 4) = Trim(Me.Frame1.TextBox1.Value)
- #014 主界面.Caption = Sheet2.Cells(2, 4) & "收据系统"
- #015 主界面.Label12.Caption = Sheet2.Cells(2, 4) & "收据系统"
- #016 主界面.StatusBar1.Panels(1).Text = Sheet2.Cells(2, 4) & "收据系统"
- #017 Unload Me
- #018 End Sub
复制代码 设置收据系统的使用单位名称并更新“主界面”窗体的标题、标签及状态栏中显示的信息。
第23行到第28行代码,“系统”菜单中的“用户设置”菜单功能,显示“用户设置”窗体设置收据系统的用户名称。其中第24、25行代码判断当前用户是否是系统管理员,只有系统管理员才能设置收据系统的用户名称。设置用户名称需使用“用户设置”窗体,在VBE窗口中单击菜单“插入”→“插入窗体”,在窗体中添加两个框架控件、一个列表框控件、一个文本框控件及三个按钮按件,如图所示。
双击“用户设置”窗体写入下面的代码:- #001 Private Sub UserForm_Initialize()
- #002 Dim r As Integer
- #003 Dim i As Integer
- #004 r = Sheet2.Range("A65536").End(xlUp).Row
- #005 For i = 3 To r
- #006 Me.Frame1.ListBox1.AddItem Sheet2.Cells(i, 1).Value
- #007 Next
- #008 Me.Frame2.TextBox1.SetFocus
- #009 End Sub
- #010 Private Sub CommandButton1_Click()
- #011 Dim r As Integer
- #012 Dim rng As Range
- #013 If Me.Frame1.ListBox1.ListIndex < 0 Then
- #014 MsgBox "请选择需删除的用户姓名!", 64, "提示"
- #015 Exit Sub
- #016 End If
- #017 r = Sheet2.Range("A65536").End(xlUp).Row
- #018 With Sheet2.Range("A3:F" & r)
- #019 Set rng = .Find(What:=Me.Frame1.ListBox1.Value, _
- #020 After:=.Cells(.Cells.Count), _
- #021 LookIn:=xlValues, _
- #022 LookAt:=xlWhole, _
- #023 SearchOrder:=xlByRows, _
- #024 SearchDirection:=xlNext, _
- #025 MatchCase:=False)
- #026 If Not rng Is Nothing Then
- #027 rng.Offset(0, 1).Delete Shift:=xlUp
- #028 rng.Delete Shift:=xlUp
- #029 Me.Frame1.ListBox1.RemoveItem (ListBox1.ListIndex)
- #030 MsgBox "已经成功删除!", 64, "提示"
- #031 End If
- #032 End With
- #033 End Sub
- #034 Private Sub CommandButton2_Click()
- #035 Dim r As Integer
- #036 With Me.Frame2.TextBox1
- #037 r = Sheet2.Range("A65536").End(xlUp).Row
- #038 If Trim(.Value) = "" Then
- #039 MsgBox "用户姓名不能为空!", 64, "提示"
- #040 .SetFocus
- #041 Exit Sub
- #042 End If
- #043 If WorksheetFunction.CountIf(Sheet2.Range("A3:A" & r), Trim(.Text)) > 0 Then
- #044 MsgBox "用户已经存在!", 64, "提示"
- #045 .Value = ""
- #046 Exit Sub
- #047 End If
- #048 Sheet2.Range("A" & r + 1) = Trim(.Text)
- #049 Me.Frame1.ListBox1.AddItem Trim(.Text)
- #050 .Value = ""
- #051 .SetFocus
- #052 End With
- #053 MsgBox "用户成功增加!", 64, "提示"
- #054 End Sub
- #055 Private Sub CommandButton3_Click()
- #056 Unload Me
- #057 End Sub
复制代码 用户设置窗体显示时列表框控件显示系统中已有用户的名称,因为系统管理员是不能删除的,所以并不显示。单击“删除”按钮可以删除列表框中选中的用户。单击“增加”按钮可以新增用户,新增用户的初始密码为空,登陆系统后可以重新设置密码。
第29行到第31行代码,“系统”菜单中的“切换用户”菜单功能,关闭主界面窗体,显示系统登陆窗体。
第32、33行代码,“系统”菜单中的“密码修改”菜单功能,显示“密码修改”窗体重新设置当前用户的密码,设置用户密码需使用“密码修改”窗体,在VBE窗口中单击菜单“插入”→“插入窗体”,在窗体中添加一个框架控件、三个文本框控件及两个按钮按件,如图所示。
双击“密码修改”窗体写入下面的代码:- #001 Private Sub CommandButton1_Click()
- #002 Dim r As Integer
- #003 Dim i As Integer
- #004 Dim p As Integer
- #005 Dim Password As String
- #006 With Sheet2
- #007 r = .Range("A65536").End(xlUp).Row
- #008 For i = 2 To r
- #009 If .Cells(i, 3) = "√" Then
- #010 Password = .Cells(i, 2).Text
- #011 p = i
- #012 Exit For
- #013 End If
- #014 Next
- #015 If Me.TextBox1.Text <> Password Then
- #016 MsgBox "对不起,密码不正确,你无权修改!", 64, "提示"
- #017 Me.TextBox1 = ""
- #018 Me.TextBox1.SetFocus
- #019 Exit Sub
- #020 End If
- #021 If Me.TextBox2.Text <> Me.TextBox3.Text Then
- #022 MsgBox "确认密码不一致,请重新输入!", 64, "提示"
- #023 Me.TextBox3 = ""
- #024 Me.TextBox3.SetFocus
- #025 Exit Sub
- #026 End If
- #027 .Cells(p, 2) = Me.TextBox3.Text
- #028 End With
- #029 Unload Me
- #030 End Sub
复制代码 使用“密码修改”窗体可以重新设置当前用户的密码。第15行到第20行代码判断所输入的旧密码是否正确,第21行到第26行代码判断两次输入的新密码是否一致,第27行代码,将新密码写入到工作表中。
第34行到第40行代码,“系统”菜单中的“打开Excel”菜单功能,收据系统日常使用时只能在主界面窗体中操作,只有系统管理员才能打开工作表对其进行修改。
第41行到第46行代码,“系统”菜单中的“退出系统”菜单功能,关闭Excel程序。
第47行第61行代码,“编辑”菜单的各项功能,对使用收据系统所需要的一些信息进行添加和删除。其中第47、48行代码显示“编辑收款人”窗体对常用收款人进行编辑,在VBE窗口中单击菜单“插入”→“插入窗体”,在窗体中添加两个框架控件、一个列表框控件、一个文本框控件及三个按钮按件,如图所示。
其他窗体与“编辑收款人”窗体类似,窗体中的代码请参考“用户设置”窗体中的代码。
第62、63行代码,“操作”菜单中的“增加”菜单功能,选择主界面窗体中多页控件的Page2页,可以填写、打印收据。
第64、65行代码,“操作”菜单中的“查询”菜单功能,选择主界面窗体中多页控件的Page3页,可以对已填写收据进行查询、打印。
第66、67行代码,“帮助”菜单中的“关于”菜单功能,显示“关于”窗体。
第68、69行代码,“帮助”菜单中的“帮助”菜单功能,显示“帮助”窗体。 |
|