'VBA macro assigned to checkbox with parameter corrupts workbook
When I assign this macro to hide or unhide a worksheet to a checkbox with a parameter, and then save, I get an error on opening the sheet the next time stating that the file needs to be repaired, followed up by another message "Removed Records: Named range from /xl/workbook.bin part (Workbook)".
This workbook has no other macros, and has no named ranges. Despite this error, the macro functions as expected. If I delete the macro then save and reopen the workbook twice, the error does not return. How can I prevent this?
The assignment to the checkbox is:
Book1.xlsb!'Sheet1.HideUnhideSheets("Sheet2")'
Public Sub HideUnhideSheets(s As String, Optional flag As String, Optional cbnumber As String)
Dim Data As Worksheet
Dim cb As CheckBox
Set Data = ThisWorkbook.Sheets(s)
If cbnumber <> "" Then
Set cb = ActiveSheet.CheckBoxes(cbnumber)
End If
'if flag is set, set visibility to the flag
'if cbnumber is set, toggle the checkbox
If flag = "Hide" Then
Data.Visible = xlSheetHidden
If cbnumber <> "" Then
cb.Value = xlOff
End If
ElseIf flag = "unhide" Then
Data.Visible = xlSheetVisible
If cbnumber <> "" Then
cb.Value = xlOn
End If
'if flag is not set, toggle
ElseIf flag = "" Then
If Data.Visible = xlSheetHidden Then
Data.Visible = xlSheetVisible
Else
Data.Visible = xlSheetHidden
End If
End If
Set Data = Nothing
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|


