VBA重置所有Checkbox，ComboBox，文本框和数据验证下拉列表

I'm very new to Visual Basics and I'm trying to reset the comboboxes to the first index, dropdownlist to the first index and reset all checkboxes and textboxes.

我是Visual Basics的新手,我正在尝试将组合框重置为第一个索引,将dropdownlist重置为第一个索引并重置所有复选框和文本框。

But I'm having trouble reseting the data validation drop down list and combo boxes back to their first index/value.

但是我无法将数据验证下拉列表和组合框重新设置为第一个索引/值。

Sheet3

DND

Code:

Private Sub CommandButton1_Click()

Sheets("Sheet5").Range("A2:DE2").Copy
Sheets("Final").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Dim Sh As Worksheet
    For Each Sh In Sheets
        On Error Resume Next
            Sh.CheckBoxes.Value = False
        On Error GoTo 0
    Next Sh
    Dim tbx As OLEObject
    For Each tbx In ActiveSheet.OLEObjects
        If TypeName(tbx.Object) = "TextBox" Then
        tbx.Object.Text = ""
        End If
    Next

    Range("Sheet3!C2").Value = ""
    Sheets("DND").Range("A17").Value = 0
    Sheets("DND").Range("C17").Value = 0


End Sub

1 个解决方案

#1

This checks whether the data validation in L6 of the active sheet is a hardcoded list or a range reference. It then either resets the cell to the first list item or the first cell in the range reference:

这将检查活动工作表的L6中的数据验证是硬编码列表还是范围参考。然后,它将单元格重置为第一个列表项或范围引用中的第一个单元格:

Sub ResetCellValidation()
Dim cell As Excel.Range
Dim rngTest As Excel.Range
Dim ErrNumber As Long

Set cell = ActiveSheet.Range("L6")
With cell.Validation
    If .Type = xlValidateList Then    
        On Error Resume Next
        Set rngTest = Application.Range(.Formula1)
        'I do this goofy thing with ErrNumber to keep my indenting and flow pretty
        ErrNumber = Err.Number
        On Error GoTo 0
        'if the Validation is defined as a range
        If ErrNumber = 0 Then
            cell.Value = Application.WorksheetFunction.Index(rngTest, 1)
        'if the validation is defined by comma-separated values
        Else
            cell.Value = Split(.Formula1, ",")(0)
        End If
    End If
End With
End Sub

There's no error-checking to confirm that there's any data validation in that cell, but not sure that could be an issue the way you're using it.

没有错误检查以确认该单元格中有任何数据验证,但不确定这可能是您使用它的方式的问题。

EDIT: Changed it from Activecell.Parent.Range(.Formula1) to Application.Range(.Formula1). Interestingly, for example, if Formula1 is Sheet2!E6, the above evaluates the full address. Don't know if I ever knew that.

编辑:将它从Activecell.Parent.Range(.Formula1)更改为Application.Range(.Formula1)。有趣的是,例如,如果Formula1是Sheet2!E6,则上面评估完整地址。不知道我是否知道这一点。

EDIT 2: I think I've really overthought this, or didn't adjust to the facts as they became clearer. If you just want to set the cell value back to blank, this one line of code will do it:

编辑2:我认为我已经真的推翻了这一点,或者在事情变得更加清楚时没有适应事实。如果您只想将单元格值设置为空白,则这一行代码将执行此操作:

ActiveSheet.Range("L6").Value = ""

ActiveSheet.Range(“L6”)。Value =“”

But where's the fun in that?

但那里的乐趣在哪里?

#1