Open excel with only a userform

**benedictexcel** · 01-20-2012, 11:14 AM

Hi everyone
I am working on a "secure" excel workbook. Is it possible to have excel open an userform BEFORE opening any worksheet?
I started creating an userform wich let the user select the sheet on wich he want to work and opens that worksheet.
I am having a hard time finding the way to hide/unhide sheets with vba I thought it would be something like: "sheet4.visible=false" ... anyone know how I could do it?
Also, my workbook is really heavy and I don't want to have extra sheets, is there another way to hide content from user than creating a blank sheet on top of wich my userform would appear?
Thanks for your help, I hope you understood my problems and have a solution!

Bénédict Soucy

p.s. I'm sorry for my english, I did my best but I'm french. I didn't go on french forum because I really want an answer!

**swoop99** · 01-20-2012, 11:19 AM

You need a loading screen, ie a worksheet that you dont mind users seeing before your code opens a userform. You'll also need to ensure that your "loading" worksheet is always the first sheet seen when opening your spreadsheet.

In: Microsoft Excel Objects
ThisWorkbook

Put this code:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

' prevents users saving copies
If SaveAsUI Then
Cancel = True
End
End If

' ensures workbook is always saved with the "Loading" worksheet visible first.

ActiveWorkbook.Unprotect Password:="whatever"
Sheets("loading").Visible = True
Sheets("loading").Select
ActiveWorkbook.Protect Password:="whatever", Structure:=True, Windows:=False

end sub

**john55** · 01-20-2012, 12:49 PM

hi,
no, you can not, you must have at least one visible sheet.
choose one sheet in order to be visible, set StandardWidth to 0, so yr Sheet will become blue.
just for test see the the file attached!

Private Sub Workbook_Open()
Dim sh As Worksheet
  For Each sh In Worksheets
    If sh.Name <> "Start" Then sh.Visible = xlSheetVeryHidden   ' "Start" is your visible sheet
  Next
UserForm1.Show
End Sub

and show the sheets

Private Sub CommandButton1_Click()
Dim sh As Worksheet   'this is a commandbutton in a userform
  For Each sh In Worksheets
    If sh.Name <> "Start" Then sh.Visible = xlSheetVisible
  Next
End Sub