隐藏表格VBA - Excel错误

Question

我目前正在做一个VBA代码，需要隐藏一些工作表时，Excel文件关闭，几乎一切都工作良好的期望，当我做以下步骤:

1. 在表格中做一些更改/插入数据
2. 点击保存按钮
3. 做另一个更改(我不想保存)
4. 点击关闭文件，点击不保存

问题是我隐藏了工作表，但由于我不保存文件(因为我不想保存在步骤3中所做的更改)，当我重新打开文件时，工作表不隐藏。我不能用Workbook_open方法这样做，因为它是不允许的(在我的项目中)。

我重写了beforeclose方法，如下所示:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Msg As String
    Dim ireply As Integer
    If Not Me.Saved Then
        Msg = "Do you want to save the file?"
        ireply = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        Select Case ireply
            Case vbYes
                Call hidesheets
                Me.Save
            Case vbNo
                Me.Saved = True
                Application.Quit
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
          Else
            Call hidesheets
            Me.Save
    End If
End Sub
Sub hidesheets()
    ThisWorkbook.Sheets("Cars").Visible = xlVeryHidden
    ThisWorkbook.Sheets("Brands").Visible = xlVeryHidden
    ThisWorkbook.Sheets("Models").Visible = xlVeryHidden
    ThisWorkbook.Sheets("Price").Visible = xlVeryHidden
End Sub

我的问题是，有可能只是保存隐藏的表格配置/设置，而不保存用户更改/插入的信息/数据?

PS:当我保存文件并进行任何更改时，代码工作正常，即隐藏表格。

提前感谢大家关于

Answer 1

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.Sheets("Cars").Visible = xlVeryHidden
    ThisWorkbook.Sheets("Brands").Visible = xlVeryHidden
    ThisWorkbook.Sheets("Models").Visible = xlVeryHidden
    ThisWorkbook.Sheets("Price").Visible = xlVeryHidden
End Sub

Answer 2

你必须反过来做

1)设置你的工作簿有这四个工作表为非常隐藏默认

将它们设置为这样，然后保存工作簿以使其假设其默认配置

2)当你打开它的时候，你使那些表格可见

Private Sub Workbook_Open()
    ThisWorkbook.Sheets("Cars").Visible = True
    ThisWorkbook.Sheets("Brands").Visible = True
    ThisWorkbook.Sheets("Models").Visible = True
    ThisWorkbook.Sheets("Price").Visible = True
End Sub

3)当你关闭它的时候，你就把这些表单设置为不可见

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Your code to be processed upon closing the sheet
    '...
    Call hidesheets '<--| hide your sheets
End Sub