Excel VBA-验证数据和逗号分隔的字符串 [英] Excel VBA - Validation Data and comma delimited strings
问题描述
我试图仅使用VBA创建数据验证单元.问题是,如果其中之一 验证列表中的项目是一个Excel公式,其中包含逗号(例如以下代码中的IF()),将生成错误.
I am trying to create data validation cells purely with VBA. The problem is, if one of the items in the validated list is a Excel formula which contains commas (such as the IF() in the following code) an error is generated.
Dim str As String
str = "=IF(SUM(A1:A2) = 0, ""Zero"", SUM(A1:A2)) , Item 2 , Item 3 , Item 4"
s1.Range("B6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str
生成错误(运行时错误1004)是因为它在第一个逗号处定界了字符串,因此验证的第一项是"=IF(SUM(A1:A2) = 0"
.我希望逗号可以有转义字符(例如C中的\),以避免出现这种情况.
我希望经过验证的列表显示为这样:
The error (runtime error 1004) is generated because it delimits the string at the first comma, so the first item of the validation is "=IF(SUM(A1:A2) = 0"
. I'm hoping there are escape characters for the commas (such as \ in C) to avoid this.
I want the validated list to appear as such:
<result of IF() function>
Item 2
Item 3
Item 4
注意:我不能使用范围来验证数据.如果用户删除了范围,则电子表格将被破坏.
Note: I CANNOT use a range for the validated data. If the user deleted the range the spreadsheet would be broken.
推荐答案
当单元格A1或A2发生更改时,如何通过WorkSheet_Change事件重置数据验证?
How about a WorkSheet_Change event to reset the data validation when cells A1 or A2 change?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValidationString As String
Dim rngChanging As Excel.Range
Dim rngWithValidation As Excel.Range
Set rngChanging = Range("A1:A2")
Set rngWithValidation = Range("B6")
If Not Intersect(Target, rngChanging) Is Nothing Then
With rngWithValidation
.Validation.Delete
If WorksheetFunction.Sum(rngChanging) = 0 Then
ValidationString = "0"
Else
ValidationString = WorksheetFunction.Sum(rngChanging)
End If
ValidationString = ValidationString & ",Item2,Item3,Item4"
.Validation.Add Type:=xlValidateList, Formula1:=ValidationString
End With
End If
End Sub
这篇关于Excel VBA-验证数据和逗号分隔的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!