Excel VBA - 根据另一个字段的值制作多个字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16072292/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Make multiple fields required based on value of another field
提问by user2292941
I've created a request form in Excel with VB which uses a submit button to generate an email in outlook based on the values entered into the form.
我用 VB 在 Excel 中创建了一个请求表单，它使用提交按钮根据表单中输入的值在 Outlook 中生成电子邮件。
Everything works fine. However, users often fail to complete all the necessary fields before submitting the request.
一切正常。但是，用户在提交请求之前通常无法完成所有必需的字段。
I need to be sure the user completes all the required fields when a specific value is entered into cell D7 before they submit the request
我需要确保用户在提交请求之前在单元格 D7 中输入特定值时完成所有必填字段
Here is where I get lost...I've tried approaching it two slightly different ways.
这就是我迷路的地方......我尝试过两种稍微不同的方法。
Hopefully someone can help me with this!
希望有人能帮我解决这个问题！
Approach 1:
方法一：
When the submit button is pressed...
当提交按钮被按下时...
Button_click()
If Range("D7").Value = "Special Request" THEN
'Make cells B6, B7, B8, B9, D14 mandatory in order to generate email
On Error Resume Next
If ThisWorkbook.Worksheets("Request").Range _
("B6, B7, B8, B9, D14 ") Is Nothing Then
MsgBox ("Please confirm all required fields have been completed!")
'Do not generate email
Approach 2:
方法二：
When the submit button is pressed...
当提交按钮被按下时...
Button_click()
'If the value of cell D7 is ANYTHING OTHER THAN "Special Feature",
'execute code as normal to generate email
'Else, check for empty fields in the required cells ("B6, B7, B8, B9, D14 ")
'if any required cells are empty, display message and do not generate email
MsgBox ("Please confirm all required fields have been completed!")
'If D7 = "Special Feature" AND NO REQUIRED FIELDS ARE MISSING,
'continue with executing code to generate email
回答by sous2817
Here's one way to go about it:
这是一种解决方法：
Sub test()
If Range("D7").Value = "Special Request" And _
Range("B6").Value = "" Or _
Range("B7").Value = "" Or _
Range("B8").Value = "" Or _
Range("B9").Value = "" Or _
Range("D14").Value = "" Then
MsgBox ("Please confirm all required fields have been completed!")
Exit Sub
Else
' whatever method you're using to generate email goes here
End If
End Sub
Here is another way using CountA:
这是使用 CountA 的另一种方法：
Sub test2()
If Range("D7").Value = "Special Request" And _
Application.WorksheetFunction.CountA(Range("B6:B9"), Range("D14")) < 5 Then
MsgBox ("Please confirm all required fields have been completed!")
Exit Sub
Else
' whatever method you're using to generate email goes here
End If
End Sub
