英文:
select random item from a drop down list in excel
问题
我有一个包含只能从下拉列表中选择项目的Excel表格，我想要随机填充它。
网上的许多解决方案选择了一个列表中的随机值（而不是下拉列表），这对我来说不起作用，因为我的下拉列表依赖于其他单元格，所以它们会根据它们而变化。我想要的是从每个新的下拉列表中随机选择项目。
英文:
I have an Excel sheet with cells that can be filled only with items from the dropdown list and I want to fill it randomly
]
Many of the solutions on the web select a random value from a list (not a dropdown list) this does not work for me because my drop-down lists are dependent on other cells so they change based on them what i want is to randomly select items from each new dropdown list
答案1
得分: 2
在我的示例中，单元格A1具有验证列表。当用户选择该单元格时，它会从其验证列表中获取一个随机值：
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v As Variant
If Target.CountLarge = 1 Then
'If Target.Address = Range("A1").Address Then
If Target.Column = 1 Then ' 列1中的所有单元格（A）
v = getRandomValue(Target, Me)
If v <> vbNullString And v <> Target.Value2 Then
Application.EnableEvents = False
Target.Value2 = v
Application.EnableEvents = True
End If
End If
End If
End Sub
' 从随机单元格获取值，使用单元格的验证列表
Public Function getRandomValue(r As Range, ws As Worksheet) As Variant
Dim s As String, ir As Range
getRandomValue = vbNullString
If hasValidationList(r) Then
s = Trim(r.Validation.Formula1)
Set ir = ws.Range(Trim(Mid(s, 2)))
getRandomValue = ir.Cells(WorksheetFunction.RandBetween(1, ir.CountLarge)).Value2
End If
End Function
' 辅助函数 - 检查单元格是否具有验证列表
Public Function hasValidationList(r As Range) As Boolean
Dim t As XlDVType
On Error Resume Next
t = r.Validation.Type
On Error GoTo 0
hasValidationList = (t = xlValidateList)
End Function
' 一个示例，用随机值填充A1:A1000中具有验证列表的所有单元格
' 将此复制到工作表的模块中（不要放在普通模块中）
Private Sub fillAllCellsRandomly()
Dim lastRow As Long, rng As Range, rr As Range, v As Variant
lastRow = 1000
Set rng = Me.Range("A1:A" & lastRow)
For Each rr In rng
v = getRandomValue(rr, Me)
If v <> vbNullString Then rr.Value2 = v
Next
End Sub
英文:
In my example cell A1 has a validation list. When the user selects the cell, it gets a random value from its validation list:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v As Variant
If Target.CountLarge = 1 Then
'If Target.Address = Range("A1").Address Then
If Target.Column = 1 Then ' all cells in column 1 (A)
v = getRandomValue(Target, Me)
If v <> vbNullString And v <> Target.Value2 Then
Application.EnableEvents = False
Target.Value2 = v
Application.EnableEvents = True
End If
End If
End If
End Sub
' GET A VALUE FROM A RANDOM CELL, OF CELL's VALIDATION LIST
Public Function getRandomValue(r As Range, ws As Worksheet) As Variant
Dim s As String, ir As Range
getRandomValue = vbNullString
If hasValidationList(r) Then
s = Trim(r.Validation.Formula1)
Set ir = ws.Range(Trim(Mid(s, 2)))
getRandomValue = ir.Cells(WorksheetFunction.RandBetween(1, ir.CountLarge)).Value2
End If
End Function
' HELPER FUNCTION - CHECK IF A CELL HAS VALIDATION LIST
Public Function hasValidationList(r As Range) As Boolean
Dim t As XlDVType
On Error Resume Next
t = r.Validation.Type
On Error GoTo 0
hasValidationList = (t = xlValidateList)
End Function
' AN EXAMPLE TO FILL ANY CELLS IN A1:A1000 WHITCH HAVE A VALIDATION LIST
' WITH A RANDOM VALUE FROM THEIR LIST
'COPY THIS IN THE SHEET'S MODULE (NO IN A COMMON MODULE)
Private Sub fillAllCellsRandomly()
Dim lastRow As Long, rng As Range, rr As Range, v As Variant
lastRow = 1000
Set rng = Me.Range("A1:A" & lastRow)
For Each rr In rng
v = getRandomValue(rr, Me)
If v <> vbNullString Then rr.Value2 = v
Next
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论