创建多个数据验证列表而不引用相同范围的 EXCEL VBA
我正在 Excel VBA 中编写一个宏,该宏在指定单元格中创建数据验证列表。然后,程序提示用户输入包含数据验证列表内容的单元格。然后,包含列表内容的相同行将从视图中隐藏。但是,当我尝试多次重新运行宏时,每次我选择新的内容范围时,每个后续列表都会引用该范围。 我不希望这种情况发生。
我编写了这行代码来防止这种情况:
For Each nm In ThisWorkbook.Names
strRngNumLbl = strRngNmLbl + 1
Next nm
strRange = strRange & strRngNumLbl
其中 strRng 是添加到数据验证时要引用的范围的名称。然而,由于某种原因,这不起作用。我认为这会起作用,因为它会为要添加到列表中的每个范围创建独立的名称。但它没有...
这是完整的代码:
Sub CreatDropDownList()
Dim strRange As String
Dim celNm As Range
Dim celNm2 As Range 'use only if necessary
Dim celRng As Range
Dim strRngNumLbl As Integer
Dim nm As Name
On Error GoTo pressedCancel:
Set celNm = Application.InputBox(Prompt:= _
"Please select a cell to create a list.", _
Title:="SPECIFY Cell", Type:=8)
If celNm Is Nothing Then Exit Sub
'Inserts a copy of the row where the drop down list is going to be
celNm.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.Insert '?
'moves the cell to the appropriate location
celNm.Offset(0, -1).Value = "N/A"
'cell range equal to nothing
Set celRng = Nothing
'asks user to determine range of strings
Set celRng = Application.InputBox(Prompt:= _
"Please select the range of cells to be included in list.", _
Title:="SPECIFY RANGE", Type:=8)
If celRng Is Nothing Then Exit Sub
On Error GoTo 0
strRange = "DataRange"
strRngNumLbl = 1
'Increments strRngNumLblb for the number of names present in the workbook to
'ensure list is not referring to duplicate ranges
For Each nm In ThisWorkbook.Names
strRngNumLbl = strRngNmLbl + 1
Next nm
strRange = strRange & strRngNumLbl
'user defined data range is now called strRange, refer to it as Range(strRange)
ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng
'format the refernce name for use in Validation.add
strRange = "=" & strRange
celNm.Offset(-1, 0).Select
'Add the drop down list to the target range using the list range
celNm.Validation.Delete
celNm.Validation.Add xlValidateList, , , strRange
'hide the range where the list came from
celRng.EntireRow.Hidden = True
pressedCancel:
End Sub
有什么建议吗?
I am writing a Macro in excel VBA that creates a data validation list in a specified cell. The program then prompts the user for the cells which contain the contents of the data validation lists. The same rows containing the list contents are then to be hidden from view. However, when I try to rerun the macro multiple times, each time I select a new range for contents, each of the proceeding lists then refers to this range. I DO NOT want this to happen.
I wrote this line of code to prevent this:
For Each nm In ThisWorkbook.Names
strRngNumLbl = strRngNmLbl + 1
Next nm
strRange = strRange & strRngNumLbl
Where strRng is the name of the range to refer to when adding to the data validation. However, for some reason this does not work. I thought this would work because it would create independent names for each of the ranges to be added to a list. But it does not...
Here is the entire code:
Sub CreatDropDownList()
Dim strRange As String
Dim celNm As Range
Dim celNm2 As Range 'use only if necessary
Dim celRng As Range
Dim strRngNumLbl As Integer
Dim nm As Name
On Error GoTo pressedCancel:
Set celNm = Application.InputBox(Prompt:= _
"Please select a cell to create a list.", _
Title:="SPECIFY Cell", Type:=8)
If celNm Is Nothing Then Exit Sub
'Inserts a copy of the row where the drop down list is going to be
celNm.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.Insert '?
'moves the cell to the appropriate location
celNm.Offset(0, -1).Value = "N/A"
'cell range equal to nothing
Set celRng = Nothing
'asks user to determine range of strings
Set celRng = Application.InputBox(Prompt:= _
"Please select the range of cells to be included in list.", _
Title:="SPECIFY RANGE", Type:=8)
If celRng Is Nothing Then Exit Sub
On Error GoTo 0
strRange = "DataRange"
strRngNumLbl = 1
'Increments strRngNumLblb for the number of names present in the workbook to
'ensure list is not referring to duplicate ranges
For Each nm In ThisWorkbook.Names
strRngNumLbl = strRngNmLbl + 1
Next nm
strRange = strRange & strRngNumLbl
'user defined data range is now called strRange, refer to it as Range(strRange)
ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng
'format the refernce name for use in Validation.add
strRange = "=" & strRange
celNm.Offset(-1, 0).Select
'Add the drop down list to the target range using the list range
celNm.Validation.Delete
celNm.Validation.Add xlValidateList, , , strRange
'hide the range where the list came from
celRng.EntireRow.Hidden = True
pressedCancel:
End Sub
Any suggestions?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
解决您的问题
而不是:
您应该:
有关您的代码的一些提示或问题
我不明白这部分代码有什么用:
我不明白要么这部分。此外,如果用户在 A 列中选择一个单元格,这可能会导致错误,
希望有帮助,
Solving your issue
Instead of:
You should have:
Some tips or questions about your code
i don't understand what's the use of this part of code:
i don't understand either this part. Moreover, this could cause an error if the user select a cell in the column A
Hope that helps,
我只需检查 strRange 名称是否已在 ThisWorkbook.names 中即可解决此问题。这是对上面代码的编辑:
I was able to solve this problem simply by checking if the strRange name was already in ThisWorkbook.names. Here is an edit to the code above: