Excel 按钮，多选框提交实例

最新推荐文章于 2025-09-11 10:19:44 发布

原创

最新推荐文章于 2025-09-11 10:19:44 发布 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

用户有一个Excel，多个sheet，然后首页是一些下拉框和填充框，所有的数据在其他sheet页，客户非常苦恼每次需要在各页找到相关的内容，然或填写到首页，并且首页填写的内容可以自动填充到各个Sheet页。

方法：使用Excel的VB脚本实现，首页信息填充后，点击按钮实现提交的功能。脚本如下:

Private Sub CheckBox1_Click()
Dim n As Integer
Dim st As String
st = Me.CheckBox1.Caption
With Me.CheckBox1
n = Me.UsedRange.Rows.Count + 1
If .Value Then
Me.Range("A" & n).EntireRow.Insert
Me.Range("A" & n) = "N/A"
Me.Range("B" & n) = Cells(2, 3)
Me.Range("C" & n) = st
Me.Range("D" & n) = "null"
Me.Range("E" & n) = "null"
Else
For i = 29 To 50
If Me.Range("C" & i) = st Then
Me.Range("A" & i + 1).Offset(-1, 0).EntireRow.Delete
End If
Next
End If
End With
End Sub

Private Sub CheckBox10_Click()
Dim n As Integer
Dim st As String
st = Me.CheckBox10.Caption
With Me.CheckBox10
n = Me.UsedRange.Rows.Count + 1
If .Value Then
Me.Range("A" & n).EntireRow.Insert
Me.Range("A" & n) = "N/A"
Me.Range("B" & n) = Cells(2, 3)
Me.Range("C" & n) = st
Me.Range("D" & n) = "null"
Me.Range("E" & n) = "null"
Else
For i = 29 To 50
If Me.Range("C" & i) = st Then
Me.Range("A" & i + 1).Offset(-1, 0).EntireRow.Delete
End If
Next
End If
End With
End Sub

Private Sub CheckBox11_Click()
Dim n As Integer
Dim st As String
st = Me.CheckBox11.Caption
With Me.CheckBox11
n = Me.UsedRange.Rows.Count + 1
If .Value Then
Me.Range("A" & n).EntireRow.Insert
Me.Range("A" & n) = "N/A"
Me.Range("B" & n) = Cells(2, 3)
Me.Range("C" & n) = st
Me.Range("D" & n) = "null"
Me.Range("E" & n) = "null"
Else
For i = 29 To 50
If Me.Range("C" & i) = st Then
Me.Range("A" & i + 1).Offset(-1, 0).EntireRow.Delete
End If
Next
End If
End With
End Sub

Private Sub CheckBox12_Click()
Dim n As Integer
Dim st As String
st = Me.CheckBox12.Caption
With Me.CheckBox12
n = Me.UsedRange.Rows.Count + 1
If .Value Then
Me.Range("A" & n).EntireRow.Insert
Me.Range("A" & n) = "N/A"
Me.Range("B" & n) = Cells(2, 3)
Me.Range("C" & n) = st
Me.Range("D" & n) = "null"
Me.Range("E" & n) = "null"
Else
For i = 29 To 50
If Me.Range("C" & i) = st Then
Me.Range("A" & i + 1).Offset(-1, 0).EntireRow.Delete
End If
Next
End If
End With
End Sub

Private Sub CheckBox13_Click()
Dim n As Integer
Dim st As String
st = Me.CheckBox13.Caption
With Me.CheckBox13
n = Me.UsedRange.Rows.Count + 1
If .Value Then
Me.Range("A" & n).EntireRow.Insert
Me.Range("A" & n) = "N/A"
Me.Range("B" & n) = Cells(2, 3)
Me.Range("C" & n) = st
Me.Range("D" & n) = "null"
Me.Range("E" & n) = "null"
Else
For i = 29 To 50
If Me.Range("C" & i) = st Then
Me.Range("A" & i + 1).Offset(