代码流畅性-错误检查，单击/取消单击，提取新值 [英] Code Fluency - ErrorChecking, Click/Unclick, Pulling New Values
本文介绍了代码流畅性-错误检查，单击/取消单击，提取新值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的初始代码:
Option Explicit
Private Sub clearButton_Click()
ClearUFData 'clears the form
End Sub
Private Sub enterButton_Click()
If Not CheckInputs Then Exit Sub 'check for fields to have values
Process GetWs(Me.impactCombobox.Value) ' process data passing the proper worksheet got from GetWs() function
MsgBox "Project Entered Successfully"
ClearUFData 'clear the data
End Sub
Private Sub Process(ws As Worksheet)
Dim iRow As Long
Dim MonthNumber As Byte
Dim ColumnNumber As Long: ColumnNumber = 4
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).row + 1
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
'.Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.nameTextbox.Value
.Cells(iRow, 2).Value = Me.projectTextbox.Value
.Cells(iRow, 3).Value = Me.audienceCombobox.Value
For MonthNumber = 0 To 11
If lengthListbox.Selected(MonthNumber) Then
.Cells(iRow, ColumnNumber).Value = "Yes"
Else
.Cells(iRow, ColumnNumber).Value = "No"
End If
'Increase the column Index for each time through the loop
ColumnNumber = ColumnNumber + 1
Next MonthNumber
If rvpCheckbox.Value = True Then
.Cells(iRow, 28).Value = "RVP"
End If
If uwCheckbox.Value = True Then
.Cells(iRow, 29).Value = "UW"
End If
If uaCheckbox.Value = True Then
.Cells(iRow, 30).Value = "UA"
End If
If umCheckbox.Value = True Then
.Cells(iRow, 31).Value = "UM"
End If
If baCheckbox.Value = True Then
.Cells(iRow, 32).Value = "BA"
End If
If otherCheckbox.Value = True Then
.Cells(iRow, 33).Value = "Other"
End If
End With
End Sub
Function CheckInputs() As Boolean
If Not CheckControl(Me.nameTextbox, "Please enter your name") Then Exit Function
If Not CheckControl(Me.projectTextbox, "Please enter a Project Name") Then Exit Function
If Not CheckControl(Me.audienceCombobox, "Please select an Audience") Then Exit Function
If Not CheckControl(Me.impactCombobox, "Please select Impact Type") Then Exit Function
CheckInputs = True
End Function
Function CheckControl(ctrl As MSForms.Control, errMsg As String) As Boolean
Select Case TypeName(ctrl)
Case "TextBox"
CheckControl = Trim(ctrl.Value) <> ""
Case "ComboBox"
CheckControl = ctrl.ListIndex <> -1
' Case Else
End Select
If CheckControl Then Exit Function
ctrl.SetFocus
MsgBox errMsg
End Function
Function GetWs(impact As String) As Worksheet
Select Case impact
Case "High"
Set GetWs = Worksheets("HI Project Database")
Case "Low"
Set GetWs = Worksheets("LI Project Database")
' Case Else
End Select
End Function
Sub ClearUFData()
Dim i As Integer
'clear the data
Me.nameTextbox.Value = ""
Me.projectTextbox.Value = ""
Me.nameTextbox.SetFocus
Me.audienceCombobox.Value = Null
Me.impactCombobox.Value = Null
Me.q1Checkbox.Value = False
Me.q2Checkbox.Value = False
Me.q3Checkbox.Value = False
Me.q4Checkbox.Value = False
For i = lengthListbox.ListCount - 1 To 0 Step -1
If lengthListbox.Selected(i) = True Then
lengthListbox.Selected(i) = False
End If
Next i
End Sub
Private Sub exitButton_Click()
' exits the form
Unload Me
End Sub
Private Sub q1Checkbox_Click()
lengthListbox.Selected(0) = True
lengthListbox.Selected(1) = True
lengthListbox.Selected(2) = True
If q1Checkbox.Value = False Then
lengthListbox.Selected(0) = False
lengthListbox.Selected(1) = False
lengthListbox.Selected(2) = False
End If
End Sub
Private Sub q2Checkbox_Click()
lengthListbox.Selected(3) = True
lengthListbox.Selected(4) = True
lengthListbox.Selected(5) = True
If q2Checkbox.Value = False Then
lengthListbox.Selected(3) = False
lengthListbox.Selected(4) = False
lengthListbox.Selected(5) = False
End If
End Sub
Private Sub q3Checkbox_Click()
lengthListbox.Selected(6) = True
lengthListbox.Selected(7) = True
lengthListbox.Selected(8) = True
If q3Checkbox.Value = False Then
lengthListbox.Selected(6) = False
lengthListbox.Selected(7) = False
lengthListbox.Selected(8) = False
End If
End Sub
Private Sub q4Checkbox_Click()
lengthListbox.Selected(9) = True
lengthListbox.Selected(10) = True
lengthListbox.Selected(11) = True
If q4Checkbox.Value = False Then
lengthListbox.Selected(9) = False
lengthListbox.Selected(10) = False
lengthListbox.Selected(11) = False
End If
End Sub
Private Sub q1Checkbox2_Click()
lengthListbox2.Selected(0) = True
lengthListbox2.Selected(1) = True
lengthListbox2.Selected(2) = True
If q1Checkbox2.Value = False Then
lengthListbox2.Selected(0) = False
lengthListbox2.Selected(1) = False
lengthListbox2.Selected(2) = False
End If
End Sub
Private Sub q2Checkbox2_Click()
lengthListbox2.Selected(3) = True
lengthListbox2.Selected(4) = True
lengthListbox2.Selected(5) = True
If q2Checkbox2.Value = False Then
lengthListbox2.Selected(3) = False
lengthListbox2.Selected(4) = False
lengthListbox2.Selected(5) = False
End If
End Sub
Private Sub q3Checkbox2_Click()
lengthListbox2.Selected(6) = True
lengthListbox2.Selected(7) = True
lengthListbox2.Selected(8) = True
If q3Checkbox2.Value = False Then
lengthListbox2.Selected(6) = False
lengthListbox2.Selected(7) = False
lengthListbox2.Selected(8) = False
End If
End Sub
Private Sub q4Checkbox2_Click()
lengthListbox2.Selected(9) = True
lengthListbox2.Selected(10) = True
lengthListbox2.Selected(11) = True
If q4Checkbox2.Value = False Then
lengthListbox2.Selected(9) = False
lengthListbox2.Selected(10) = False
lengthListbox2.Selected(11) = False
End If
End Sub
Private Sub UserForm_Initialize()
' sets name textbox to focus on load and loads combobox list
nameTextbox.SetFocus
audienceCombobox.AddItem "HR Activities/Initiatives"
audienceCombobox.AddItem "BI Underwriting"
audienceCombobox.AddItem "Product Management"
audienceCombobox.AddItem "CI Operations"
audienceCombobox.AddItem "UW Systems"
audienceCombobox.AddItem "Regional Initiatives"
audienceCombobox.AddItem "Other"
lengthListbox.AddItem "January"
lengthListbox.AddItem "February"
lengthListbox.AddItem "March"
lengthListbox.AddItem "April"
lengthListbox.AddItem "May"
lengthListbox.AddItem "June"
lengthListbox.AddItem "July"
lengthListbox.AddItem "August"
lengthListbox.AddItem "September"
lengthListbox.AddItem "October"
lengthListbox.AddItem "November"
lengthListbox.AddItem "December"
'New Year
lengthListbox2.AddItem "January"
lengthListbox2.AddItem "February"
lengthListbox2.AddItem "March"
lengthListbox2.AddItem "April"
lengthListbox2.AddItem "May"
lengthListbox2.AddItem "June"
lengthListbox2.AddItem "July"
lengthListbox2.AddItem "August"
lengthListbox2.AddItem "September"
lengthListbox2.AddItem "October"
lengthListbox2.AddItem "November"
lengthListbox2.AddItem "December"
impactCombobox.AddItem "High"
impactCombobox.AddItem "Low"
End Sub
现在，我需要解决的一些特定领域是，我刚刚添加了一个新的lengthListbox2，它是一个完全相同的副本，并且执行了lengthListbox的所有工作，它只是从ColumnNumber = 16开始的数据启动，但我不知道如何调整DIM以解决此问题.
Now, some specific areas I need to address are that I just added in a new lengthListbox2 that is an exact replica and does everything lengthListbox does, it just starts the data starting from ColumnNumber = 16 but I can't figure how to adjust the DIMs to address this.
我也在尝试找出错误检查q1-q4复选框和lengthListbox(2)的正确方法，以便它们必须单击q1-q4框之一或lengthListbox中的选项之一
I am also trying to figure out the proper way to error check the q1-q4 checkboxes and the lengthListbox(2) so that they have to have clicked either one of the q1-q4 boxes or one of the options in the lengthListbox.
我要添加的另一个小添加项是，如果他们单击列表框中的前3个属性(月)，则将选中q1，然后选择下一个3，然后选择q2.现在我的代码反之亦然，如果选择了Q1，那么它将选择前三个月.
Also another small addition I'm trying to add is that if they click the first 3 properties in the listbox (months) q1 is checked, and the next 3 then q2 is selected. Right now my code only does vice versa, if Q1 is selected then it selects the first 3 months.
请原谅某些地方的混乱情况，我为将代码组合在一起以提高某些地方的流畅性提供了很多帮助，而我的新手技能则完成了其余工作.
Excuse the messiness in some areas, I've had a lot of help putting the code together for fluency in certain areas and my newbie skills did the rest.
现在，项目长度正试图从当前和明年的需求中拉出一个，而仅仅是一个或另一个.
Project length is now trying to pull a requirement from both current and next year when it just needs to be one or the other.
我尝试通过设置另一个控件检查来使用此功能，但是专门针对列表并将两个控件检查情况分开，但是我还需要在其中添加一个or语句:
I've tried using this by setting up another control check but for lists specifically and separating two controlcheck situations but I need more so an or statement in there:
Function CheckInputs() As Boolean
If Not CheckControl(Me.nameTextbox, "Please enter your name") Then Exit Function
If Not CheckControl(Me.projectTextbox, "Please enter a Project Name") Then Exit Function
If Not CheckControl(Me.audienceCombobox, "Please select an Audience") Then Exit Function
If Not CheckControl(Me.impactCombobox, "Please select Impact Type") Then Exit Function
If Not CheckControlList(Me.lengthListbox, Me.lengthListbox2, "Please Select Project Length") Then Exit Function
CheckInputs = True
End Function
Private Function CountSelectedListBoxItems(lb As MSForms.ListBox) As Long
Dim i As Long
With lb
For i = 0 To .ListCount - 1
If .Selected(i) Then CountSelectedListBoxItems = CountSelectedListBoxItems + 1
Next i
End With
End Function
Function CheckControlList(ctrl As MSForms.Control, ctrl2 As MSForms.Control, errMsg As String) As Boolean
Select Case TypeName(ctrl)
Case "ListBox" '<--| add the case of a ListBox control passed to check
CheckControlList = CountSelectedListBoxItems(ctrl) > 0 '<--| call new function to check listboxes
' Case Else
End Select
Select Case TypeName(ctrl2)
Case "ListBox" '<--| add the case of a ListBox control passed to check
CheckControlList = CountSelectedListBoxItems(ctrl) > 0 '<--| call new function to check listboxes
' Case Else
End Select
If CheckControlList Then Exit Function
ctrl.SetFocus
MsgBox errMsg
End Function
Function CheckControl(ctrl As MSForms.Control, errMsg As String) As Boolean
Select Case TypeName(ctrl)
Case "TextBox"
CheckControl = Trim(ctrl.Value) <> ""
Case "ComboBox"
CheckControl = ctrl.ListIndex <> -1
Case "ListBox" '<--| add the case of a ListBox control passed to check
CheckControl = CountSelectedListBoxItems(ctrl) > 0 '<--| call new function to check listboxes
' Case Else
End Select
If CheckControl Then Exit Function
ctrl.SetFocus
MsgBox errMsg
End Function
我也尝试过使用OR语句将两者分开，但最终会在成功输入之前显示请输入项目长度":
I've tried using an OR statement to separate the two as well but it just ends up displaying "Please enter Project Length" prior to saying it was successfully entered:
Function CheckInputs() As Boolean
If Not CheckControl(Me.nameTextbox, "Please enter your name") Then Exit Function
If Not CheckControl(Me.projectTextbox, "Please enter a Project Name") Then Exit Function
If Not CheckControl(Me.audienceCombobox, "Please select an Audience") Then Exit Function
If Not CheckControl(Me.impactCombobox, "Please select Impact Type") Then Exit Function
If Not (CheckControl(Me.lengthListbox) Or (CheckControl(Me.lengthListbox2)) Then MsgBox "Please Enter Project Length": Exit Function
CheckInputs = True
End Function
推荐答案
以相似的方式处理两个列表框，但是具有一些不同的参数，添加一个 ProcessListBox()子类来处理 ListBox控制处理，并指定可以更改的参数作为其参数，例如:
to process two listboxes in a similar way but with some different parameters add a ProcessListBox() sub to handle a ListBox control processing, and specify as its parameters those that can change, like:
Sub ProcessListBox(lb As msforms.ListBox, ws As Worksheet, iRow As Long, ByVal iniCol As Long)
Dim MonthNumber As Byte
With ws
For MonthNumber = 0 To 11
If lb.Selected(MonthNumber) Then
.Cells(iRow, iniCol).Value = "Yes"
Else
.Cells(iRow, iniCol).Value = "No"
End If
iniCol = iniCol + 1 'Increase the column Index for each time through the loop
Next MonthNumber
End With
End Sub
，以便您处理 Process()子级的核心"为:
so that your processing "core" of Process() sub would be:
With ws
'.Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.nameTextbox.Value
.Cells(iRow, 2).Value = Me.projectTextbox.Value
.Cells(iRow, 3).Value = Me.audienceCombobox.Value
If rvpCheckbox Then .Cells(iRow, 28).Value = "RVP"
If uwCheckbox Then .Cells(iRow, 29).Value = "UW"
If uaCheckbox Then .Cells(iRow, 30).Value = "UA"
If umCheckbox Then .Cells(iRow, 31).Value = "UM"
If baCheckbox Then .Cells(iRow, 32).Value = "BA"
If otherCheckbox Then .Cells(iRow, 33).Value = "Other"
End With
ProcessListBox lengthListbox, ws, iRow, 4 '<--| process lengthListbox passing "4" as starting column
ProcessListBox lengthListbox2, ws, iRow, 16 '<--| process lengthListbox2 passing "16" as starting column
对于列表框和相应的复选框进行交叉检查，因为您实际上只关心前者，所以只需检查列表框中是否有选定的值
as for the listboxes and corresponding checkboxes cross checking, since you actually only care about the former, just check listboxes for any selected value
，您可以通过以下特定功能来实现:
and you could that by means of a specific Function like:
Private Function CountSelectedListBoxItems(lb As msforms.ListBox) As Long
Dim i As Long
With lb
For i = 0 To .ListCount - 1
If .Selected(i) Then CountSelectedListBoxItems = CountSelectedListBoxItems + 1
Next i
End With
End Function
添加到您的 CheckControl()函数 Case 块中:
Function CheckControl(ctrl As msforms.Control, errMsg As String) As Boolean
Select Case TypeName(ctrl)
Case "TextBox"
CheckControl = Trim(ctrl.Value) <> ""
Case "ComboBox"
CheckControl = ctrl.ListIndex <> -1
Case "ListBox" '<--| add the case of a ListBox control passed to check
CheckControl = CountSelectedListBoxItems(ctrl) > 0 '<--| call new function to check listboxes
' Case Else
End Select
If CheckControl Then Exit Function
ctrl.SetFocus
MsgBox errMsg
End Function
并因此更新您的 CheckInputs()函数
Function CheckInputs() As Boolean
If Not CheckControl(Me.nameTextbox, "Please enter your name") Then Exit Function
If Not CheckControl(Me.projectTextbox, "Please enter a Project Name") Then Exit Function
If Not CheckControl(Me.audienceCombobox, "Please select an Audience") Then Exit Function
If Not CheckControl(Me.impactCombobox, "Please select Impact Type") Then Exit Function
If Not CheckControl(Me.lengthListbox, "Please select a current year month") Then Exit Function '<--| check "lengthListbox"
If Not CheckControl(Me.lengthListbox2, "Please select a next year month") Then Exit Function '<--| check "lengthListbox2"
CheckInputs = True
End Function
我还认为您需要更新您的 ClearUFData()子代以同时处理 lengthListbox 和 lengthListbox2 .还有qXCheckbox2 ...
I also think you need to update your ClearUFData() sub to handle both lengthListbox and lengthListbox2. and qXCheckbox2, too...
Sub ClearUFData()
'clear the data
With Me
.nameTextbox.Value = ""
.projectTextbox.Value = ""
.nameTextbox.SetFocus
.audienceCombobox.Value = Null
.impactCombobox.Value = Null
.q1Checkbox.Value = False
.q2Checkbox.Value = False
.q3Checkbox.Value = False
.q4Checkbox.Value = False
.q1Checkbox2.Value = False '<-- uncheck q1Checkbox2
.q2Checkbox2.Value = False '<-- uncheck q2Checkbox2
.q3Checkbox2.Value = False '<-- uncheck q3Checkbox2
.q4Checkbox2.Value = False '<-- uncheck q4Checkbox2
DeselectListBox lengthListbox '<-- deselect all listbox items
DeselectListBox lengthListbox2 '<-- deselect all lkistbox items
End With
End Sub
我将在其中使用 DeselectListBox()子级，如下所示:
where I'd use a DeselectListBox() sub like follows:
Private Sub DeselectListBox(lb As msforms.ListBox)
Dim i As Long
With lb
For i = 0 To .ListCount - 1
.Selected(i) = False
Next i
End With
End Sub
最后一些改进
Finally some little improvements
复选框控件的默认属性是 Value ，无论是否选中，该属性都会返回True或False
checkbox control default property is Value that returns True or False whether it is checked or not
所以而不是
If q1Checkbox.Value = False Then
您可以简单地使用
If Not q1Checkbox Then
喜欢
这可以使您简化和缩短以下代码:
this can have you simplify and shorten the following code:
Private Sub q1Checkbox_Click()
lengthListbox.Selected(0) = True
lengthListbox.Selected(1) = True
lengthListbox.Selected(2) = True
If Not q1Checkbox Then
lengthListbox.Selected(0) = False
lengthListbox.Selected(1) = False
lengthListbox.Selected(2) = False
End If
End Sub
Private Sub q2Checkbox_Click()
lengthListbox.Selected(3) = True
lengthListbox.Selected(4) = True
lengthListbox.Selected(5) = True
If Not q2Checkbox Then
lengthListbox.Selected(3) = False
lengthListbox.Selected(4) = False
lengthListbox.Selected(5) = False
End If
End Sub
Private Sub q3Checkbox_Click()
lengthListbox.Selected(6) = True
lengthListbox.Selected(7) = True
lengthListbox.Selected(8) = True
If Not q3Checkbox Then
lengthListbox.Selected(6) = False
lengthListbox.Selected(7) = False
lengthListbox.Selected(8) = False
End If
End Sub
Private Sub q4Checkbox_Click()
lengthListbox.Selected(9) = True
lengthListbox.Selected(10) = True
lengthListbox.Selected(11) = True
If Not q4Checkbox Then
lengthListbox.Selected(9) = False
lengthListbox.Selected(10) = False
lengthListbox.Selected(11) = False
End If
End Sub
Private Sub q1Checkbox2_Click()
lengthListbox2.Selected(0) = True
lengthListbox2.Selected(1) = True
lengthListbox2.Selected(2) = True
If Not q1Checkbox2 Then
lengthListbox2.Selected(0) = False
lengthListbox2.Selected(1) = False
lengthListbox2.Selected(2) = False
End If
End Sub
Private Sub q2Checkbox2_Click()
lengthListbox2.Selected(3) = True
lengthListbox2.Selected(4) = True
lengthListbox2.Selected(5) = True
If q2Checkbox2.Value = False Then
lengthListbox2.Selected(3) = False
lengthListbox2.Selected(4) = False
lengthListbox2.Selected(5) = False
End If
End Sub
Private Sub q3Checkbox2_Click()
lengthListbox2.Selected(6) = True
lengthListbox2.Selected(7) = True
lengthListbox2.Selected(8) = True
If Not q3Checkbox2 Then
lengthListbox2.Selected(6) = False
lengthListbox2.Selected(7) = False
lengthListbox2.Selected(8) = False
End If
End Sub
Private Sub q4Checkbox2_Click()
lengthListbox2.Selected(9) = True
lengthListbox2.Selected(10) = True
lengthListbox2.Selected(11) = True
If Not q4Checkbox2 Then
lengthListbox2.Selected(9) = False
lengthListbox2.Selected(10) = False
lengthListbox2.Selected(11) = False
End If
End Sub
使用 With 语句，也可以使
UserForm_Initialize更具可编码性"和可读性:
UserForm_Initialize can be also made more "codeable" and readable with the use of With statement as follows:
Private Sub UserForm_Initialize()
' sets name textbox to focus on load and loads combobox list
nameTextbox.SetFocus
With audienceCombobox
.AddItem "HR Activities/Initiatives"
.AddItem "BI Underwriting"
.AddItem "Product Management"
.AddItem "CI Operations"
.AddItem "UW Systems"
.AddItem "Regional Initiatives"
.AddItem "Other"
End With
With lengthListbox
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
'New Year
With lengthListbox2
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
With impactCombobox
.AddItem "High"
.AddItem "Low"
End With
End Sub
这篇关于代码流畅性-错误检查，单击/取消单击，提取新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
