[0009] 输入功能:开始工作,在PC机上打开软件进入应用程序主界面,用户申请开关验收的准备工作由该功能实现。输入的数据资料以用户提供的开关保护定值通知单为依据;如图1所示界面,点击“新增”按钮,软件会按编号顺序自动生成新的编号;若一个年度结束,新的年度开始,软件会根据电脑的日期自动进行判断,以四位数的新“年份+001”开始编号,因此要保证电脑日期正确;界面上的所有下拉菜单都可用下拉选择方式操作,其中“通流试验单位”下拉菜单增加了“模糊字查询”功能,可以在空白框中输入“通流试验单位”名称的关键字,如图2显示,可过滤显示只包含有关键字的所有“通流试验单位”条目的名称;若该条目名称不存在于下拉菜单条目中,只需要在空白框中输入新的“通流试验单位”名称的全称,按“保存”按钮后可自动保存,下次就可以正常选择使用了;“CT变比”下拉菜单中有一个“1/1”的条目,该条目是考虑用户定值单中无法确定现场开关的CT变比时进行选择的,相应的,在打印出的“验收单”的“CT变比”栏的位置就是空白,方便现场验收时手工填入实际比值;“二次值”复选框默认勾选,那么下面的“速断保护”、“过流保护”、“看门狗”的数值输入的就是二次(电流)值;反之,不选“二次值”复选框,那么输入的数值就是一次(电流)值。打印出来的“验收单”中会有一次值和二次值的对应排列(如图3红框所示);一次值=CT倍率×二次值;保存及打印功能:点击“保存”按钮后,软件自动在已有记录中搜索同名用户的记录并弹出一个窗口(如图4)供人工判断确认,有同名用户的开关验收是比较常见的,本功能是防止派工人员粗心将近期已派工的验收单又重复录入的情况出现;点击“保存”按钮后,“打印验收单”的按钮变为可用状态,即可进行打印;全部“验收单”打印出来后,就可以派工到现场进行开关验收的工作了;
查询功能:如图5所示,查询界面上有三种查询方法,分别是按工单编号、用户名称、所属线路名称。除按工单编号查询是需要完整准确输入七位有效的编号外,其它两种查询方法都可以使用“模糊字查询”功能,在查询输入框中,只需输入名称的关键字,即可查询到含有关键字的所有记录;单击选择记录后点“确定”按钮就可以查询到某用户的验收资料,查询界面为图1的界面;查询内容包括两部分:一部分是验收前准备好的数据资料,包括用户名称、供电地点、保护定值数据等;另一部分查询内容为验收后的开关资料,包括开关生产厂家、型号、编号等;若用户开关验收还未进行归档工作,那么查询出来的内容就是验收前的资料,资料的内容以图1所示的界面显示出来;若用户开关验收工作已经归档,会在图1界面红框所示的位置出现一个“开关资料查询”的按钮,点击该按钮会弹出如图6所示的界面显示验收后的开关资料;
浏览功能:单击图1主界面上的“浏览”按键,弹出如图7所示的“浏览”工单界面,在该界面下,所有申请验收的开关资料按记录条的形式全部显示出来;双击某条记录,则切换到查询功能的显示界面上(图1界面);
修改功能:验收前准备的数据资料需要修改的,需要先使用查询或浏览功能找到要修改的记录并显示出来,再点击“修改”按钮方可进行数据的修改;如果验收资料已经归档,则会弹出提示窗口不允许修改;
完工确认功能:完工确认功能由两个模块组成;一个是显示未完工确认记录的模块,一个是开关资料录入并归档模块;在主界面(图1)上点击“完工确认”按钮,就显示(如图8)的“完工确认”界面,该界面中的记录包括已验收未归档或还未验收的记录;在图8完工确认界面上单击要归档的记录,就显示图9所示的“验收资料录入及归档”界面;验收资料包括开关生产厂家、开关及控制器的型号和编号;一个开关生产厂家至少对应一种型号的开关和控制器,因此,模块里设计了关联项显示功能,当下拉菜单选择开关厂家后,自动选择对应的开关和控制器的型号,若实际有差别,可手动再行选择;“归档”复选框若不勾选,则点击“保存”按钮保存资料后,该条记录依然显示在“完工确认”界面的记录中;该设计是考虑到验收(纸质)资料未收集齐全时,可先行录入验收后的开关资料;
数据导出功能:该功能是把验收开关记录的所有内容另存为后缀名为xls的电子表格文件,方便数据资料的共享;点击“数据导出”按钮后,就弹出WPS Office的“另存为”窗口(如图10),选择文件夹及输入文件名后就可以保存文件了;
维护功能:该功能是程序员用于调试程序及维护参数的,需要输入密码方能使用。
[0010] 一种10kV柱上开关验收流程的程序化应用方法的软件源代码全局变量定义:
Public A_bh As String '验收单编号
Public A_yhmc As String '用户名称
Public A_ctlmark As Boolean '
Public A_save As Boolean '用于判断是否保存数据
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA"
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As LongPublic Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Public Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function DeleteMenu Lib "user32" (ByVal hmenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Public Const SW_SHOWMAXIMIZED = 3
Public Const WS_MAXIMIZEBOX = &H10000
Public Const WS_MINIMIZEBOX = &H20000
Public Const WS_THICKFRAME = &H40000
Public Const GWL_STYLE = (-16)
Option Explicit
主窗
体模块代码:
Option Explicit
Dim A_azdd As String '安装地点
Dim A_sydw As String '通流试验单位
Dim A_CT As String 'CT变比
Dim A_CT_X As Single 'CT变比倍数
Dim A_sd_dl As Single '速断保护电流
Dim A_sd_S As Single '速断保护时限
Dim A_gl_dl As Single '过流保护电流
Dim A_gl_S As Single '过流保护时限
Dim A_dog_dl As Single '看门狗电流
Dim A_dog_S As Single '看门狗时限
Dim A_T As String 'T接杆号
Dim A_date As Date '派工日期
Dim A_date_sy As Date '试验(验收)日期
Dim A_zcss As String '资产所属
Dim A_remark As String '备注
Dim A_check As String '电流是一/二次值
Dim A_ran As String, A_row As String, A_row1 As String, A_row2 As String, A_row3 As String, A_row4 As String '单元格范围
Dim A_obj As Object, A_sheet As Object '工作表对象
Dim C As Object '窗体对象
Dim process As Object '过程对象
Dim A_line As Integer '起始行号
Dim A_count As Integer '记录行数
Dim Fname As String '文件名
Dim Astr() As String '定义数组
Dim A_kgxh As String, A_kgbh As String, A_kgcj As String
Dim A_kzqxh As String, A_kzqbh As String '控制器型号、编号
Private Sub Command_Appand_Click() '新增验收单记录
A_save = True
A_ctlmark = True '恢复变量为初始值
Workbooks("验收单2018.xlsm").Sheets("参数表").Activate
A_count = ActiveSheet.Range("A65536").End(xlUp).Row '试验单位名称加到组合下拉框
A_ran = "A1:A" & A_count
For A_line = 1 To A_count
UserForm1.Combo_sydw.AddItem Sheets("参数表").Range("A" & A_line).ValueNext
CommandButton_show.Visible = False
Command_bulid.Enabled = False
Command_edit.Enabled = False
A_line = 1
A_count = 1
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
A_ran = "A2:A" & A_count
A_bh = Year(Date) * 1000 + 1
Set A_obj = Worksheets("开关记录表").Range(A_ran)
If A_obj.Find(A_bh) Is Nothing Then
A_bh = Year(Date) * 1000 + 1
Else
A_bh = A_obj.Range("A" & A_count - 1).Value + 1
End If
For Each C In UserForm1.Controls
If TypeName(C) = "TextBox" Then C.Enabled = True
Next
For Each C In UserForm1.Controls
If TypeName(C) = "ComboBox" Then C.Enabled = True
Next
For Each C In UserForm1.Controls
If TypeName(C) = "TextBox" Then C.Text = ""
Next
For Each C In UserForm1.Controls
If TypeName(C) = "CheckBox" Then C.Enabled = True
Next
With UserForm1
.Text_today.Enabled = True
.Text_today.Value = "今天是" + Format(Date, "dddddd") & Format(Date, "aaaa")
.Frame2.Enabled = True
.Frame1.Enabled = True
.Text_yhmc.Text = ""
.Command_Save.Enabled = True
.Text_sd_dl = 0
.Text_sd_S = 0
.Text_gl_dl = 0
.Text_gl_S = 0.2
.Text_dog_dl = 0
.Text_dog_S = 0
.Text_date = Date
End With
UserForm1.Label_bh.Caption = A_bh
End Sub
Private Sub Command_bulid_Click() '打印验收单
Worksheets("参数表").Activate '获取CT倍数
A_count = ActiveSheet.UsedRange.Rows.Count
A_ran = "C1:C" & A_count
Set A_sheet = Worksheets("参数表").Range(A_ran)
A_CT_X = A_sheet.Cells(A_sheet.Find(UserForm1.ComboBox_ct.Text).Row, 4)Worksheets("验收单").Activate
Set A_obj = Worksheets("验收单").Range("A1:D15")
With Worksheets("验收单")
.Range("d2").Value = UserForm1.Text_date
.Range("e2").Value = "编号:" + UserForm1.Label_bh.Caption
.Range("b3").Value = UserForm1.Text_yhmc.Text
.Range("b4").Value = UserForm1.Text_azdd.Text
.Range("b5").Value = UserForm1.Combo_sydw.Text
.Range("b8").Value = UserForm1.ComboBox_ct.Text
.Range("b14").Value = UserForm1.Text_T
.Range("d11").Value = UserForm1.Text_sd_S.Text
.Range("d12").Value = UserForm1.Text_gl_S.Text
If UserForm1.CheckBox_check.Value = True Then
Select Case Val(UserForm1.ComboBox_ct.Text)
Case Is > 1
.Range("b11").Value = Str(A_CT_X * UserForm1.Text_sd_dl.Text) + "/" & (UserForm1.Text_sd_dl.Text)
.Range("b12").Value = Str(A_CT_X * UserForm1.Text_gl_dl.Text) + "/" & (UserForm1.Text_gl_dl.Text)
.Range("b13").Value = Str(A_CT_X * UserForm1.Text_dog_dl.Text) + "/" & (UserForm1.Text_dog_dl.Text)
Case 1
.Range("b8").Value = " " + "/"
.Range("b11").Value = "" + "/" & (UserForm1.Text_sd_dl.Text)
.Range("b12").Value = " " + "/" & (UserForm1.Text_gl_dl.Text)
.Range("b13").Value = " " + "/" & (UserForm1.Text_dog_dl.Text)
End Select
End If
If UserForm1.CheckBox_check.Value = False Then
Select Case Val(UserForm1.ComboBox_ct.Text)
Case Is > 1
.Range("b11").Value = Str(UserForm1.Text_sd_dl.Text) + "/" & Round
((UserForm1.Text_sd_dl.Text / A_CT_X), 2)
.Range("b12").Value = Str(UserForm1.Text_gl_dl.Text) + "/" & Round
((UserForm1.Text_gl_dl.Text / A_CT_X), 2)
.Range("b13").Value = Str(UserForm1.Text_dog_dl.Text) + "/" & Round((UserForm1.Text_dog_dl.Text / A_CT_X), 2)
Case 1
.Range("b8").Value = " " + "/"
.Range("b11").Value = Str(UserForm1.Text_sd_dl.Text) + "/"
.Range("b12").Value = Str(UserForm1.Text_gl_dl.Text) + "/"
.Range("b13").Value = Str(UserForm1.Text_dog_dl.Text) + "/"
End Select
End If
Select Case UserForm1.ComboBox_zcss.Value
Case "公网"
.Range("a1").Value = "西宁供电公司检修分公司10kV公网开关通流试验验收单"
Case "用户"
.Range("a1").Value = "西宁供电公司检修分公司10kV用户分界开关通流试验验收单"
End Select
End With
If MsgBox("是否打印当前验收单! ", vbOKCancel) = vbOK Then
UserForm1.Command_Save.Enabled = False
Worksheets("验收单").Visible = xlSheetVisible
ActiveSheet.PrintOut
Worksheets("验收单").Visible = xlSheetHidden
End If
End Sub
Private Sub Command_exit_Click() '退出程序
Unload UserForm1
ThisWorkbook.Close False '关闭“验收单2018.xlsm”工作薄
For Each process In GetObject(process.Terminate(0))
Next
Application.Quit
End Sub
Private Sub Command_query_Click() '查询验收单
Load UserForm6
UserForm6.Show
End Sub
Private Sub Command_save_Click() '数据保存
With UserForm1
If ComboBox_zcss.Text = "" Then
Call MsgBox("请选择资产所属!")
Exit Sub
End If
If .Text_yhmc.Text = "" Or Trim(.Text_azdd.Text) = "" Then
Call MsgBox("用户名称或安装地点不能为空!")
Exit Sub
End If
If ComboBox_ct = "" Then ComboBox_ct = "1/1"
A_yhmc = .Text_yhmc.Text
A_count = 1
Worksheets("开关记录表").Activate
A_count = Worksheets("开关记录表").Range("B65536").End(xlUp).Row
A_ran = "B2:B" & A_count
Set A_sheet = Worksheets("开关记录表").Range(A_ran)
If Not A_sheet.Find(A_yhmc) Is Nothing Then
If StrComp(A_sheet.Find(A_yhmc).Value, A_yhmc) = 0 And A_ctlmark = True Then 'A_ctlmark 变量为“真”时,判断为新增同名用户,过滤可能重复派工的用户!
Load UserForm7 '保存时所有同名用户的列表窗
UserForm7.Show
End If
If A_save = False Then
A_ctlmark = True '初始化该变量
Exit Sub
End If
End If
A_count = 1
Worksheets("参数表").Activate
A_sydw = Trim(UserForm1.Combo_sydw.Text)
If Len(A_sydw) < 10 Then
Call MsgBox("请检查试验单位名称是否完整!")
Exit Sub
End If
A_count = ActiveSheet.Range("A65536").End(xlUp).Row
A_ran = "A1:A" & A_count
Set A_sheet = Worksheets("参数表").Range(A_ran)
If A_sheet.Find(A_sydw) Is Nothing Then
A_sheet.Cells(A_count + 1).Value = A_sydw
ElseIf StrComp(A_sheet.Find(A_sydw).Value, A_sydw) <> 0 Then
A_sheet.Cells(A_count + 1).Value = A_sydw
End If
A_bh = .Label_bh.Caption
A_zcss = .ComboBox_zcss.Text
A_yhmc = .Text_yhmc.Text
A_azdd = .Text_azdd.Text
A_sydw = .Combo_sydw.Text
If .ComboBox_ct.Text = "" Then
.ComboBox_ct.Text = "1/1"
End If
A_CT = .ComboBox_ct.Text
A_T = .Text_T.Text
A_date = .Text_date
A_date_sy = .Text_date
A_zcss = .ComboBox_zcss.Text
A_remark = .TextBox_remark.Text
If .CheckBox_check.Value = True Then
A_check = "二次值"
Else
A_check = "一次值"
End If
End With
A_count = 1
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
A_row1 = "A2"
A_row2 = "A" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("开关记录表").Range(A_ran)
If Not A_obj.Find(A_bh) Is Nothing Then
A_count = A_obj.Find(A_bh).Row
Else
A_count = ActiveSheet.UsedRange.Rows.Count + 1
End If
A_row1 = "A" & A_count
A_row2 = "S" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("开关记录表").Range(A_ran)
With A_obj
.Cells(1).Value = A_bh
.Cells(2).Value = A_yhmc
.Cells(3).Value = A_azdd
.Cells(4).Value = A_sydw
.Cells(5).NumberFormatLocal = "@" '设置单元格为文本格式
.Cells(5).Value = A_CT
.Cells(6).Value = A_check
.Cells(7).Value = UserForm1.Text_sd_dl.Text '速断电流
.Cells(8).Value = UserForm1.Text_sd_S.Text '速断时限
.Cells(9).Value = UserForm1.Text_gl_dl.Text '过流电流
.Cells(10).Value = UserForm1.Text_gl_S.Text '过流时限
.Cells(11).Value = UserForm1.Text_dog_dl.Text '看门狗电流
.Cells(12).Value = UserForm1.Text_dog_S.Text '看门狗时限
.Cells(13).Value = A_T
.Cells(14).Value = A_date
.Cells(15).Value = A_date_sy
.Cells(16).Value = A_zcss
.Cells(17).Value = A_remark
End With
Workbooks("验收单2018.xlsm").Save
Call MsgBox("保存完毕!")
A_ctlmark = True '恢复变量为初始值
UserForm1.Command_bulid.Enabled = True
End Sub
Private Sub CommandButton_show_Click() '显示验收合格开关的资料
Load UserForm8
UserForm8.Show
End Sub
Private Sub UserForm_Initialize()
Dim lStyle, hwnd As Long
hwnd = FindWindow("ThunderDFrame", Me.Caption) '获得窗口句柄
lStyle = GetWindowLong(hwnd, GWL_STYLE)
lStyle = lStyle Or WS_MINIMIZEBOX '窗口最小化
SetWindowLong hwnd, GWL_STYLE, lStyle
UserForm1.StartUpPosition = 2
UserForm1.Command_Appand.SetFocus
Workbooks("验收单2018.xlsm").Sheets("参数表").Activate
'A_row = ActiveSheet.Range("A65536").End(xlUp).Row '试验单位记录合计数'UserForm1.Combo_sydw.RowSource = "A1:A" & A_row
A_row1 = ActiveSheet.Range("C65536").End(xlUp).Row 'CT变比记录合计数
UserForm1.ComboBox_ct.RowSource = "C1:C" & A_row1
A_row2 = ActiveSheet.Range("D65536").End(xlUp).Row '产权所属记录合计数UserForm1.ComboBox_zcss.RowSource = "D1:D" & A_row2
A_ctlmark = True '初始化该标记为真,用于数据修改时的判断
With UserForm1
.Command_bulid.Enabled = False
.Command_Save.Enabled = False
.Command_edit.Enabled = False
.Text_today.Value = "今天是" + Format(Date, "dddddd") & Format(Date, "aaaa")
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) '点击窗体关闭按钮,关闭程序
Unload UserForm1
ThisWorkbook.Close False
For Each process In GetObject(process.Terminate(0))
Next
Application.Application.Quit
End Sub
浏览窗体模块代码:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "
GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "
SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hmenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SW_SHOWMAXIMIZED = 3
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
'Private Const WS_CLOSEBOX =
Private Const WS_THICKFRAME = &H40000
Private Const GWL_STYLE = (-16)
Private Sub Command_exit_Click()
Unload UserForm3
UserForm1.Show
End Sub
Private Sub ListView1_DblClick()
A_bh = UserForm3.ListView1.SelectedItem.Text
Call Quary_read
End Sub
Private Sub UserForm_Initialize()
Dim hwnd As Long, lStyle As Long, hmenu As Long, myrc As Long, sc_Close As Long
sc_Close = &HF060
hwnd = FindWindow("ThunderDFrame", Me.Caption) '获得窗口句柄
lStyle = GetWindowLong(hwnd, GWL_STYLE)
lStyle = lStyle Or WS_MINIMIZEBOX '窗口最小化
lStyle = lStyle Or WS_MAXIMIZEBOX '窗口最大化
'lStyle = lStyle And Not WS_CLOSEBOX
lStyle = lStyle Or WS_THICKFRAME '窗口大小随
鼠标调整
SetWindowLong hwnd, GWL_STYLE, lStyle
hmenu = GetSystemMenu(hwnd, 0&)
myrc = DeleteMenu(hmenu, sc_Close, MF_BYCOMMAND)
myrc = DrawMenuBar(hwnd)
Dim i, j As Integer
Dim A_count As Integer
Dim A_itm As Object
Workbooks("验收单2018.xlsm").Sheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
DoEvents
With UserForm3
.ListView1.ColumnHeaders.Add 1, , "编号", 50, lvwColumnLeft
For i = 2 To 25
.ListView1.ColumnHeaders.Add i, , Cells(1, i), Cells(1, i).Width + 9, lvwColumnLeft
Next
.ListView1.View = lvwReport
.ListView1.FullRowSelect = True
.ListView1.Gridlines = True
For j = 2 To A_count
Set A_itm = .ListView1.ListItems.Add()
A_itm.Text = Cells(j, 1)
For i = 1 To 24
A_itm.SubItems(i) = Cells(j, i + 1)
Next i
Next j
End With
UserForm1.CommandButton_show.Visible = False
End Sub
Private Sub UserForm_Resize()
If Me.Height > 24 Then
ListView1.Move 12, 18, Me.Width - 24, Me.Height - 85
Command_exit.Move Me.Width / 2 - 27, Me.Height - 57
End If
End Sub
Function Quary_read() '查询数据读入查询界面
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
A_ran = "A2:A" & A_count
Set A_sheet = Worksheets("开关记录表").Range(A_ran)
For Each C In UserForm1.Controls
If TypeName(C) = "TextBox" Then C.Text = ""
Next
UserForm1.Label_bh.Caption = ""
A_count = A_sheet.Find(A_bh).Row
A_row1 = "A" & A_count
A_row2 = "S" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_sheet = Worksheets("开关记录表").Range(A_ran)
For Each C In UserForm1.Controls '置控件不可写入
If TypeName(C) = "TextBox" Then C.Enabled = False
Next
For Each C In UserForm1.Controls
If TypeName(C) = "ComboBox" Then C.Enabled = False
Next
For Each C In UserForm1.Controls
If TypeName(C) = "CheckBox" Then C.Enabled = False
Next
A_ctlmark = False '置该变量为假,以禁用下列有关控件事件,防止数据更改
With UserForm1
.Text_today.Enabled = True
.Text_today.Value = "今天是" + Format(Date, "dddddd") & Format(Date, "aaaa")
.Label_bh.Caption = A_sheet.Cells(1).Value
.Text_yhmc.Text = A_sheet.Cells(2).Value
.Text_azdd.Text = A_sheet.Cells(3).Value
.Combo_sydw.Text = A_sheet.Cells(4).Value
.ComboBox_ct.Text = A_sheet.Cells(5).Value
If A_sheet.Cells(6).Value = "一次值" Then
.CheckBox_check.Value = False
Else
.CheckBox_check.Value = True
End If
.Text_sd_dl.Text = A_sheet.Cells(7).Value
.Text_sd_S.Text = A_sheet.Cells(8).Value
.Text_gl_dl.Text = A_sheet.Cells(9).Value
.Text_gl_S.Text = A_sheet.Cells(10).Value
.Text_dog_dl.Text = A_sheet.Cells(11).Value
.Text_dog_S.Text = A_sheet.Cells(12).Value
.Text_T.Text = A_sheet.Cells(13).Value
.Text_date = A_sheet.Cells(14).Value
.ComboBox_zcss.Text = A_sheet.Cells(16).Value
.TextBox_remark.Text = A_sheet.Cells(17).Value
.Command_Save.Enabled = False '以下代码设定数据不可修改
.Command_bulid.Enabled = True
.Frame1.Enabled = False
.Frame2.Enabled = False
If A_sheet.Cells(18).Value = "是" And Left(A_bh, 4) <> "2018" Then
UserForm1.CommandButton_show.Visible = True
End If
End With
A_ctlmark = True '恢复控件事件
Unload UserForm3
UserForm1.Command_edit.Enabled = True
UserForm1.Show
End Function
查询窗体模块代码:
Private Sub Command_cancel_Click()
A_bh = ""
Unload UserForm6
End Sub
Private Sub Command_yes_Click()
If OptionButton2.Value = True Then '按用户名称查询
If A_bh = "" Then
Call MsgBox("请选中要查询的记录! ", vbOKOnly)
Else
Call Quary_read'读入数据
End If
End If
If OptionButton3.Value = True Then '按安装线路名称查询
If A_bh = "" Then
Call MsgBox("请选中要查询的记录! ", vbOKOnly)
Else
Call Quary_read '读入数据
End If
End If
End Sub
Function Quary_read() '查询数据读入查询界面
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
A_ran = "A2:A" & A_count
Set A_sheet = Worksheets("开关记录表").Range(A_ran)
For Each C In UserForm1.Controls
If TypeName(C) = "TextBox" Then C.Text = ""
Next
UserForm1.Label_bh.Caption = ""
A_count = A_sheet.Find(A_bh).Row
A_row1 = "A" & A_count
A_row2 = "S" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_sheet = Worksheets("开关记录表").Range(A_ran)
For Each C In UserForm1.Controls '置控件不可写入
If TypeName(C) = "TextBox" Then C.Enabled = False
Next
For Each C In UserForm1.Controls
If TypeName(C) = "ComboBox" Then C.Enabled = False
Next
For Each C In UserForm1.Controls
If TypeName(C) = "CheckBox" Then C.Enabled = False
Next
A_ctlmark = False '置该变量为假,以禁用下列有关控件事件,防止数据更改
With UserForm1
.Text_today.Enabled = True
.Text_today.Value = "今天是" + Format(Date, "dddddd") & Format(Date, "aaaa")
.Label_bh.Caption = A_sheet.Cells(1).Value
.Text_yhmc.Text = A_sheet.Cells(2).Value
.Text_azdd.Text = A_sheet.Cells(3).Value
.Combo_sydw.Text = A_sheet.Cells(4).Value
.ComboBox_ct.Text = A_sheet.Cells(5).Value
If A_sheet.Cells(6).Value = "一次值" Then
.CheckBox_check.Value = False
Else
.CheckBox_check.Value = True
End If
.Text_sd_dl.Text = A_sheet.Cells(7).Value
.Text_sd_S.Text = A_sheet.Cells(8).Value
.Text_gl_dl.Text = A_sheet.Cells(9).Value
.Text_gl_S.Text = A_sheet.Cells(10).Value
.Text_dog_dl.Text = A_sheet.Cells(11).Value
.Text_dog_S.Text = A_sheet.Cells(12).Value
.Text_T.Text = A_sheet.Cells(13).Value
.Text_date = A_sheet.Cells(14).Value
.ComboBox_zcss.Text = A_sheet.Cells(16).Value
.TextBox_remark.Text = A_sheet.Cells(17).Value
.Command_Save.Enabled = False '以下代码设定数据不可修改
.Command_bulid.Enabled = True
.Frame1.Enabled = False
.Frame2.Enabled = False
If A_sheet.Cells(18).Value = "是" And Left(A_bh, 4) <> "2018" Then
UserForm1.CommandButton_show.Visible = True
End If
End With
A_ctlmark = True '恢复控件事件
Unload UserForm6
UserForm1.Command_edit.Enabled = True
End Function
Private Sub ListView1_Click()
A_bh = UserForm6.ListView1.SelectedItem.Text
End Sub
Private Sub OptionButton1_Click() '按验收单编号查询
ListView1.ListItems.Clear
ListView1.Enabled = False
Label1.Enabled = False
If Quary_bh <> "" Then
Call Quary_read '读入查询数据
End If
End Sub
Private Sub OptionButton2_Click() '选择该单选按钮时,调用按用户名查询过程A_bh = ""
Call Quary_name
End Sub
Private Sub OptionButton3_Click() '选择该单选按钮时,调用安装地点线路名查询过程
A_bh = ""
Call Quary_T_name
End Sub
Private Sub UserForm_Initialize()
OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
ListView1.Enabled = False
Label1.Enabled = False
A_bh = ""
UserForm1.CommandButton_show.Visible = False
End Sub
完工确认窗体模块代码:
Dim i, j As Integer
Dim A_itm As Object
Private Sub Command_quit_Click()
Unload UserForm4
UserForm1.Show
End Sub
Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
A_bh = Item
Load UserForm8
UserForm8.Show
End Sub
Private Sub OptionButton_all_Click() '列表记录全选
OptionButton_reverse.Value = False
With UserForm4
Set A_itm = .ListView1.ListItems
For j = 1 To .ListView1.ListItems.Count
A_itm(j).Checked = True
Next j
End With
End Sub
Private Sub UserForm_Initialize() '窗口初始化
Dim hwnd, lStyle, hmenu, myrc, sc_Close, MF_BYCOMMAND As Long
sc_Close = &HF060
hwnd = FindWindow("ThunderDFrame", Me.Caption) '获得窗口句柄
lStyle = GetWindowLong(hwnd, GWL_STYLE)
SetWindowLong hwnd, GWL_STYLE, lStyle
hmenu = GetSystemMenu(hwnd, 0&)
myrc = DeleteMenu(hmenu, sc_Close, MF_BYCOMMAND)
myrc = DrawMenuBar(hwnd)
With UserForm4
.ListView1.ColumnHeaders.Add 1, , "编号", 70, lvwColumnLeft
.ListView1.ColumnHeaders.Add 2, , "用户名称", 250, lvwColumnLeft
.ListView1.ColumnHeaders.Add 3, , "安装地点", 150, lvwColumnLeft
.ListView1.ColumnHeaders.Add 4, , "试验单位", 175, lvwColumnLeft
.ListView1.View = lvwReport
.ListView1.FullRowSelect = True
.ListView1.Gridlines = True
.ListView1.ListItems.Clear
End With
Call list
End Sub
Function list() '显示未完工确认验收单
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
With UserForm4
.ListView1.ListItems.Clear
For j = 2 To A_count
If Cells(j, 18) <> "是" Then
Set A_itm = .ListView1.ListItems.Add()
A_itm.Text = Cells(j, 1)
A_itm.SubItems(1) = Cells(j, 2)
A_itm.SubItems(2) = Cells(j, 3)
A_itm.SubItems(3) = Cells(j, 4)
End If
Next j
End With
End Function
验收数据输入及归档(显示)窗体模块代码:
Private Sub ComboBox_kgcj_Change() '
选定开关型号自动选定开关的其它参数If A_ctlmark = False Then '首次不运行以下代码
Exit Sub
End If
Workbooks("验收单2018.xlsm").Sheets("参数表").Activate
A_kgcj = ComboBox_kgcj.Text
A_count = ActiveSheet.UsedRange.Rows.Count
A_row1 = "G1"
A_row2 = "G" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("参数表").Range(A_ran)
If Not A_obj.Find(A_kgcj) Is Nothing Then
ComboBox_kgxh.Text = A_obj.Find(A_kgcj).Offset(columnoffset:=1).Value
ComboBox_kzqxh.Text = A_obj.Find(A_kgcj).Offset(columnoffset:=2).ValueEnd If
End Sub
Private Sub CommandButton_exit_Click()
A_ctlmark = True '恢复为初始值
Unload UserForm8
Load UserForm4
UserForm4.Show
End Sub
Private Sub CommandButton_save_Click()
A_count = 1
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
A_row1 = "A2"
A_row2 = "A" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("开关记录表").Range(A_ran)
If Not A_obj.Find(A_bh) Is Nothing Then
A_count = A_obj.Find(A_bh).Row
Else
MsgBox ("该工单编号不存在!")
End
End If
A_row1 = "A" & A_count
A_row2 = "Z" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("开关记录表").Range(A_ran)
With A_obj
If CheckBox1.Value = True Then
.Cells(18).Value = "是" '完工归档
.Cells(26).Value = Date '完工归档日期
End If
.Cells(15).Value = TextBox_date_sy
.Cells(17).Value = TextBox_remark
.Cells(19).Value = ComboBox_kgxh.Text
.Cells(20).NumberFormatLocal = "@"
.Cells(20).Value = Text_kgbh.Text
.Cells(21).Value = ComboBox_kgcj.Text
.Cells(22).Value = ComboBox_kzqxh.Text
.Cells(23).NumberFormatLocal = "@"
.Cells(23).Value = TextBox_kzqbh.Text
.Cells(24).Value = ComboBox_explain.Text '保护投入说明
If OptionButton1.Value = True Then
.Cells(25).Value = "一次值"
End If
If OptionButton2.Value = True Then
.Cells(25).Value = "二次值"
End If
End With
Workbooks("验收单2018.xlsm").Save
Call MsgBox("保存完毕!")
End Sub
Private Sub Cmd_show_quit_Click() '显示开关信息资料后的退出按钮
Unload UserForm8
End Sub
Private Sub OptionButton1_Click()
OptionButton1.Value = True
OptionButton2.Value = False
End Sub
Private Sub OptionButton2_Click()
OptionButton1.Value = False
OptionButton2.Value = True
End Sub
Private Sub UserForm_Initialize()
Workbooks("验收单2018.xlsm").Sheets("参数表").Activate
A_row = ActiveSheet.Range("H65536").End(xlUp).Row '开关型号记录合计数UserForm8.ComboBox_kgxh.RowSource = "H1:H" & A_row
A_row1 = ActiveSheet.Range("G65536").End(xlUp).Row '开关厂家记录合计数UserForm8.ComboBox_kgcj.RowSource = "G1:G" & A_row1
A_row3 = ActiveSheet.Range("I65536").End(xlUp).Row '控制器型号记录合计数UserForm8.ComboBox_kzqxh.RowSource = "I1:I" & A_row3
A_row4 = ActiveSheet.Range("J65536").End(xlUp).Row '保护投入说明记录合计数
UserForm8.ComboBox_explain.RowSource = "J1:J" & A_row4
Worksheets("开关记录表").Activate
A_count = ActiveSheet.UsedRange.Rows.Count
A_row1 = "A2"
A_row2 = "A" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("开关记录表").Range(A_ran)
A_count = A_obj.Find(A_bh).Row
A_row1 = "A" & A_count
A_row2 = "Z" & A_count
A_ran = A_row1 + ":" + A_row2
Set A_obj = Worksheets("开关记录表").Range(A_ran)
With A_obj
Label_bh = A_bh
Label_yhmc = .Cells(2).Value
Label_T = .Cells(3).Value
TextBox_date_sy = .Cells(15).Value
TextBox_remark = .Cells(17).Value
ComboBox_kgxh.Text = .Cells(19).Value
Text_kgbh.Text = .Cells(20).Value
If .Cells(21).Value <> "" Then A_ctlmark = False '设置变量为假防止关联数据变动
ComboBox_kgcj.Text = .Cells(21).Value
ComboBox_kzqxh.Text = .Cells(22).Value
TextBox_kzqbh.Text = .Cells(23).Value
ComboBox_explain.Text = .Cells(24).Value
If .Cells(25).Value = "一次值" Then
OptionButton1.Value = True
OptionButton2.Value = False
End If
If .Cells(25).Value = "二次值" Then
OptionButton1.Value = False
OptionButton2.Value = True
End If
If .Cells(18).Value = "是" Then
CheckBox1.Value = True
UserForm8.Caption = "验收开关数据资料"
For Each C In UserForm8.Controls '置控件不可写入
If TypeName(C) = "TextBox" Then C.Enabled = False
Next
For Each C In UserForm8.Controls
If TypeName(C) = "ComboBox" Then C.Enabled = False
Next
For Each C In UserForm8.Controls
If TypeName(C) = "CheckBox" Then C.Enabled = False
Next
For Each C In UserForm8.Controls
If TypeName(C) = "OptionButton" Then C.Enabled = False
Next
Cmd_show_quit.Visible = True
CommandButton_save.Visible = False
CommandButton_exit.Visible = False
End If
End With
Unload UserForm4
End Sub。