我想制作一個復選框，呼叫一個宏來隱藏和取消隱藏 Excel 作業表上具有單元格中特定值的列，但它不起作用
我嘗試了以下 VBA 腳本
Sub Hide_Forecasts()
Dim c As Range
For Each c In Range("E12:CF12").Cells
If c.Value = "Forecast" Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
Sub Unhide_Forecasts()
Dim c As Range
For Each c In Range("E12:CF12").Cells
If c.Value = "Forecast" Then
c.EntireColumn.Hidden = False
End If
Next c
End Sub
Sub CheckBox_For()
If CheckBox1.Value = True Then
Call Hide_Forecasts
Else
Call Unhide_Forecasts
End If
End Sub
請幫幫我
uj5u.com熱心網友回復：
您還沒有說您使用的是哪種型別的復選框 - 表單控制元件或 ActiveX 控制元件。
對于 ActiveX 控制元件，右鍵單擊該控制元件并選擇查看代碼。
使用位于作業表后面的代碼（CheckBox1 將以您的復選框命名）。
Private Sub CheckBox1_Click()
Dim Cell As Range
For Each Cell In Me.Range("E12:CF12")
If Cell.Value = "Forecast" Then
'Checkbox returns TRUE/FALSE - Hidden takes TRUE/FALSE so just connect the two up.
Cell.EntireColumn.Hidden = Me.CheckBox1.Value
End If
Next Cell
End Sub
對于表單控制元件，右鍵單擊控制元件并選擇分配宏。
將此代碼放在普通模塊中。
Sheet1是作業表的代號（該名稱不在專案資源管理器中的括號中）。
'Form Control can have three values:
' 1 = Checked
' -4146 = Unchecked
' 2 = Mixed - ignoring that this value may occur.
Public Sub Checkbox_For()
Dim ChkValue As Boolean
'Is value different from -4146? Returns TRUE = Checked or Mixed / FALSE = Unchecked
ChkValue = Sheet1.Shapes("Check Box 1").OLEFormat.Object.Value <> -4146
Dim Cell As Range
For Each Cell In Sheet1.Range("E12:CF12")
If Cell.Value = "Forecast" Then
Cell.EntireColumn.Hidden = ChkValue
End If
Next Cell
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/526516.html
標籤：擅长vba