vba - VBA 按钮设置单元格的值,但附加宏无法正常运行
问题描述
我有一些按钮,可以将一系列单元格的值设置为在另一个单元格中找到的复选标记。然后使用 Worksheet_Change(ByVal Target as Range) 另一个宏应该运行,因为在 worksheet_change 宏所在的列中找到复选标记。它仅适用于范围中的第一行。如果单个单元格由用户输入手动更改(下拉引用包含复选标记的同一单元格),则宏运行正常。我知道它可以在没有按钮的情况下工作,但是有很多单元格要求每个用户手动选择每个单元格。
其中一个按钮的代码:
Private s As Integer
Sub Button1_Click()
If s = 0 Then
Range("F5,F6:F14").Value = Range("ZZ2").Value
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Characters.Text = "Uncheck All"
s = 1
ElseIf s = 1 Then
Range("F5,F6:F14").Value = Range("ZZ1").Value
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Characters.Text = "Check All"
s = 0
End If
End Sub
然后是列中更改的代码:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$5" Then
If Range("$G$5") = True Then
If Range("I1").Value >= Range("H5").Value Then
Range("I5").Formula = "=$E5"
End If
If Range("J1").Value >= Range("H5").Value Then
Range("J5").Formula = "=$E5"
End If
If Range("K1").Value >= Range("H5").Value Then
Range("K5").Formula = "=$E5"
End If
If Range("L1").Value >= Range("H5").Value Then
Range("L5").Formula = "=$E5"
End If
If Range("M1").Value >= Range("H5").Value Then
Range("M5").Formula = "=$E5"
End If
If Range("N1").Value >= Range("H5").Value Then
Range("N5").Formula = "=$E5"
End If
If Range("O1").Value >= Range("H5").Value Then
Range("O5").Formula = "=$E5"
End If
If Range("P1").Value >= Range("H5").Value Then
Range("P5").Formula = "=$E5"
End If
If Range("Q1").Value >= Range("H5").Value Then
Range("Q5").Formula = "=$E5"
End If
If Range("R1").Value >= Range("H5").Value Then
Range("R5").Formula = "=$E5"
End If
If Range("S1").Value >= Range("H5").Value Then
Range("S5").Formula = "=$E5"
End If
If Range("T1").Value >= Range("H5").Value Then
Range("T5").Formula = "=$E5"
End If
ElseIf Range("G5") = False Then
Range("I5").Value = "=AF16"
Range("J5").Value = "=AG16"
Range("K5").Value = "=AH16"
Range("L5").Value = "=AI16"
Range("M5").Value = "=AJ16"
Range("N5").Value = "=AK16"
Range("O5").Value = "=AL16"
Range("P5").Value = "=AM16"
Range("Q5").Value = "=AN16"
Range("R5").Value = "=AO16"
Range("S5").Value = "=AP16"
Range("T5").Value = "=AQ16"
End If
End If
If Target.Column = 6 And Target.Row > 5 Then
ThisRow = Target.Row
Dim LValue As String
LValue = "=E" & ThisRow
If Range("G" & ThisRow) = True Then
If Range("I1").Value >= Range("H" & ThisRow).Value Then
Range("I" & ThisRow).Formula = LValue
End If
If Range("J1").Value >= Range("H" & ThisRow).Value Then
Range("J" & ThisRow).Formula = LValue
End If
If Range("K1").Value >= Range("H" & ThisRow).Value Then
Range("K" & ThisRow).Formula = LValue
End If
If Range("L1").Value >= Range("H" & ThisRow).Value Then
Range("L" & ThisRow).Formula = LValue
End If
If Range("M1").Value >= Range("H" & ThisRow).Value Then
Range("M" & ThisRow).Formula = LValue
End If
If Range("N1").Value >= Range("H" & ThisRow).Value Then
Range("N" & ThisRow).Formula = LValue
End If
If Range("O1").Value >= Range("H" & ThisRow).Value Then
Range("O" & ThisRow).Formula = LValue
End If
If Range("P1").Value >= Range("H" & ThisRow).Value Then
Range("P" & ThisRow).Formula = LValue
End If
If Range("Q1").Value >= Range("H" & ThisRow).Value Then
Range("Q" & ThisRow).Formula = LValue
End If
If Range("R1").Value >= Range("H" & ThisRow).Value Then
Range("R" & ThisRow).Formula = LValue
End If
If Range("S1").Value >= Range("H" & ThisRow).Value Then
Range("S" & ThisRow).Formula = LValue
End If
If Range("T1").Value >= Range("H" & ThisRow).Value Then
Range("T" & ThisRow).Formula = LValue
End If
ElseIf Range("G" & ThisRow) = False Then
Range("I" & ThisRow).Value = ""
Range("J" & ThisRow).Value = ""
Range("K" & ThisRow).Value = ""
Range("L" & ThisRow).Value = ""
Range("M" & ThisRow).Value = ""
Range("N" & ThisRow).Value = ""
Range("O" & ThisRow).Value = ""
Range("P" & ThisRow).Value = ""
Range("Q" & ThisRow).Value = ""
Range("R" & ThisRow).Value = ""
Range("S" & ThisRow).Value = ""
Range("T" & ThisRow).Value = ""
End If
End If
If Target.Column = 29 Then
ThatRow = Target.Row
Dim MValue As String
MValue = "=AX" & ThatRow
If Range("AD" & ThatRow) = True Then
If Range("I1").Value >= Range("AE" & ThatRow).Value Then
Range("AF" & ThatRow).Formula = MValue
End If
If Range("J1").Value >= Range("AE" & ThatRow).Value Then
Range("AG" & ThatRow).Formula = MValue
End If
If Range("K1").Value >= Range("AE" & ThatRow).Value Then
Range("AH" & ThatRow).Formula = MValue
End If
If Range("L1").Value >= Range("AE" & ThatRow).Value Then
Range("AI" & ThatRow).Formula = MValue
End If
If Range("M1").Value >= Range("AE" & ThatRow).Value Then
Range("AJ" & ThatRow).Formula = MValue
End If
If Range("N1").Value >= Range("AE" & ThatRow).Value Then
Range("AK" & ThatRow).Formula = MValue
End If
If Range("O1").Value >= Range("AE" & ThatRow).Value Then
Range("AL" & ThatRow).Formula = MValue
End If
If Range("P1").Value >= Range("AE" & ThatRow).Value Then
Range("AM" & ThatRow).Formula = MValue
End If
If Range("Q1").Value >= Range("AE" & ThatRow).Value Then
Range("AN" & ThatRow).Formula = MValue
End If
If Range("R1").Value >= Range("AE" & ThatRow).Value Then
Range("AO" & ThatRow).Formula = MValue
End If
If Range("S1").Value >= Range("AE" & ThatRow).Value Then
Range("AP" & ThatRow).Formula = MValue
End If
If Range("T1").Value >= Range("AE" & ThatRow).Value Then
Range("AQ" & ThatRow).Formula = MValue
End If
ElseIf Range("AD" & ThatRow) = False Then
Range("AF" & ThatRow).Value = ""
Range("AG" & ThatRow).Value = ""
Range("AH" & ThatRow).Value = ""
Range("AI" & ThatRow).Value = ""
Range("AJ" & ThatRow).Value = ""
Range("AK" & ThatRow).Value = ""
Range("AL" & ThatRow).Value = ""
Range("AM" & ThatRow).Value = ""
Range("AN" & ThatRow).Value = ""
Range("AO" & ThatRow).Value = ""
Range("AP" & ThatRow).Value = ""
Range("AQ" & ThatRow).Value = ""
End If
End If
End Sub
解决方案
推荐阅读
- r - 在 R 中执行大量 2 样本 t 检验
- swift - 我的应用迷恋 Unexpectedly found nil 同时隐式展开 Optional 值
- web-services - org.springframework.web.client.ResourceAccessException:I/O 错误:com.ibm.websphere.ssl.protocol.SSLSocketFactory
- postgresql - 将 Kubernetes Postgres pod 连接到存储在持久卷上的现有数据库
- java - 私有布尔值无法识别“if 语句”中的返回
- java - Spring-Hibernate 应用程序中的 Whitelabel 错误页面
- javascript - 如何在浏览器中使用表单标签和 javascript 加载本地保存的视频?
- java - Hibernate - 生成 JSON 时覆盖日期的输出格式
- ios - 在ios swift中使用AVPlayer播放mpga格式文件
- git - 无需快进即可获得更改