CODEWITHSUNDEEP
vbaexcel
Aurelius
Aurelius

Reputation: 485

Hiding worksheet completely in favour of userform

I have a userform and would like this to be the first thing that is shown to the user when opening the workbook, and the sheet behind this form to be hidden.

I understand the below is the code to do this:

Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show vbModeless
End Sub

This performs the operation successfully, but my worksheet flashes up for a second or two before it is hidden and the userform appears. This is long enough for someone to take a screenshot or see valuable information behind the userform.

It also doesn't look very tidy!

Is there a way to alter anything within the VBA to accomplish this?

I have discovered that it is possible with batch scripts or something similar but I have no experience of this and would prefer not to add another dimension to an already complex form.

Upvotes: 1

Views: 1960

Answers (2)

CallumDA
CallumDA

Reputation: 12113

I'd opt for a Workbook_BeforeClose event that hides all of the sensitive sheets. That way your data remains hidden to people opening your file without macros enabled.

This goes in a new standard module

Option Explicit
Option Private Module

Public Sub SheetsHidden(ByRef hidden As Boolean)
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.name <> "Home" And hidden Then 'your *safe* sheet name
            ws.Visible = xlSheetVeryHidden
        Else
            ws.Visible = xlSheetVisible
        End If
    Next ws
End Sub

And then you can call it from your ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    SheetsHidden True
End Sub

Once you have authenticated the user you can unhide the sheets with the parameter as False.

I would also recommend exploring UserForms, particularly:

With New UserForm1
    .Show vbModeless
    'do more with your form
End With

Upvotes: 1

Slai
Slai

Reputation: 22866

By design, opening Excel file without Excel showing up is not possible without external script or tool.

Easier way to hide all sheets is to save the file as .xla(m) (or using ThisWorkbook.IsAddin = True)

Private Sub Workbook_Open()
    ThisWorkbook.IsAddin = True ' True by default for .xla(m) Excel Add-In files
    Application.Visible = Workbooks.Count
    UserForm1.Show vbModeless
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not ThisWorkbook.IsAddin Then ThisWorkbook.IsAddin = True
    If Not ThisWorkbook.Saved Then ThisWorkbook.Save
    If Workbooks.Count Then Application.Visible = True Else Application.Quit
End Sub

and in the form close event:

Private Sub UserForm_Terminate()
    If Workbooks.Count Then ThisWorkbook.Close True Else Application.Quit
End Sub

Upvotes: 0

Related Questions