如何在Excel中保存或保留對ActiveX列錶框的選擇？
假設您已經創建了一些列錶框並在列錶框中進行了選擇，但是，當關閉並重新打開工作簿時，這些列錶框的所有選擇都消失了。 您是否要在關閉並重新打開工作簿時保留在列錶框中所做的選擇？ 本文中的方法可以為您提供幫助。
使用Excel中的VBA代碼保存或保留ActiveX列錶框的選擇
輕鬆在Excel中批量插入或刪除多個複選框：
批量插入複選框 的效用 Excel的Kutools 可以幫助您快速一次在選定範圍內插入多個複選框。 然後，您可以使用 批量刪除複選框。 參見sccreenshot：
Excel的Kutools：具有200多個方便的Excel加載項，可以在60天內免費試用，沒有任何限制。 立即下載並免費試用！
- 重用任何東西： 將最常用或最複雜的公式，圖表等添加到您的收藏夾中，並在將來快速重用它們。
- 超過20種文字功能： 從文本字符串中提取數字； 提取或刪除部分文字； 將數字和貨幣轉換為英文單詞。
- 合併工具：將多個工作簿和工作表合二為一； 合併多個單元格/行/列，而不會丟失數據； 合併重複的行和總和。
- 分割工具：根據價值將數據分割成多個工作表； 一本工作簿可轉換為多個Excel，PDF或CSV文件； 一列到多列。
- 跳過粘貼 隱藏/過濾的行； 計數與求和 按背景色; 向多個收件人批量發送個性化電子郵件。
- 超級過濾器： 創建高級過濾方案並應用於任何工作表； 分類 按星期，日期，頻率等 篩選 用粗體，公式，註釋...
- 超過300種強大的功能； 適用於Office 2007-2019和365; 支持所有語言； 在您的企業或組織中輕鬆部署。
使用Excel中的VBA代碼保存或保留ActiveX列錶框的選擇
如果Excel中有ActiveX列錶框，則下面的VBA代碼可以幫助您保存或保留選擇。 請執行以下操作。
1.在工作簿中包含您要保留選擇的ActiveX列錶框，按 其他 + F11 同時打開 Microsoft Visual Basic for Applications 窗口。
2。 在裡面 Microsoft Visual Basic for Applications 窗口，雙擊 的ThisWorkbook 在左窗格中打開 的ThisWorkbook 推薦碼 窗口。 然後將以下VBA代碼複製到代碼窗口中。
VBA代碼：在Excel中保存對ActiveX列錶框的選擇
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim I As Long
Dim J As Long
Dim K As Long
Dim KK As Long
Dim xSheet As Worksheet
Dim xListBox As Object
On Error GoTo Label
Application.DisplayAlerts = False
Application.ScreenUpdating = False
K = 0
KK = 0
If Not Sheets("ListBox Data") Is Nothing Then
Sheets("ListBox Data").Delete
End If
Label:
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "ListBox Data"
Set xSheet = Sheets("ListBox Data")
For I = 1 To Sheets.Count
For Each xListBox In Sheets(I).OLEObjects
If xListBox.Name Like "ListBox*" Then
With xListBox.Object
For J = 0 To .ListCount - 1
If .Selected(J) Then
xSheet.Range("A1").Offset(K, KK).Value = "True"
Else
xSheet.Range("A1").Offset(K, KK).Value = "False"
End If
K = K + 1
Next
End With
K = 0
KK = KK + 1
End If
Next
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Sub Workbook_Open()
Dim I As Long
Dim J As Long
Dim KK As Long
Dim xRg As Range
Dim xCell As Range
Dim xListBox As Object
Application.DisplayAlerts = False
Application.ScreenUpdating = False
KK = 0
For I = 1 To Sheets.Count - 1
For Each xListBox In Sheets(I).OLEObjects
If xListBox.Name Like "ListBox*" Then
With xListBox.Object
Set xRg = Intersect(Sheets("ListBox Data").Range("A1").Offset(0, KK).EntireColumn, Sheets("ListBox Data").UsedRange)
For J = 1 To .ListCount
Set xCell = xRg(J)
If xCell.Value = "True" Then
.Selected(J - 1) = True
End If
Next
KK = KK + 1
End With
End If
Next
Next
Sheets("ListBox Data").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
4. Now you need to save the workbook as an Excel Macro-enabled workbook. Please click File > Save As > Browse.
5. In the Save As dialog box, select a folder to save the workbook, rename it as you need, select Excel Macro-Enabled Workbook in the Save as type dropdown list, and finally click the Save button. See screenshot:
Please save the workbook every time when you update the list boxes. Then all previous selections will be kept in the list boxes after reopening the workbook.
Note: When saving the workbook, a worksheet named “ListBox Data” will be created automatically at the end of all worksheets of your workbook, please ignore this worksheet because it will disappear automatically when the workbook is closed.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than
300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by
50%, and reduces hundreds of mouse clicks for you every day!
