Hide workbook/worksheets Excel VBA

0
votes

I have a simple Microsoft Excel spreadsheet with a few tabs. On opening the workbook I have a USER LOGIN form that loads up and asks for login and password. What I'm trying to do is somehow not let the user see the sheets in the workbook until they've successfully logged in. The problem I'm seeing is that one of my sheets has a macro in it, so what I do

sheet1.visible=xlhidden

it gives me a debug error. However, even if I skipped that sheet and hid all the other ones - and on proper login I tried to make them Visible=xlVisible, they still did not become visible. How would I go about something like that?

Is there some kind of a way to be able to hide the workbook possibly and then make it visible after successful login?

1
vbaexcel
You can lock it and have it password protected. This method does not show the contents unless the correct password is entered. It also does not rely on a username - just a password. – PartyHatPanda
Hopefully the visble in your code is just a typo as you wrote the question. – YowE3K
@YowE3K - yes it is. I apologize. – BobSki
@PartyHatPanda - i like using the UserForm with login/password info because this way I can display a logo and confidentiality message and such. – BobSki
Use what works best for you. I only know of a work around; hopefully someone here knows more about it than I do. – PartyHatPanda

1 Answers

2
votes

xlhidden is not an acceptable value for Worksheet().Visible.

  • Sheet1.Visible = xlSheetVisible makes the Worksheet visible
  • Sheet1.Visible = xlSheetHidden: hides the Worksheet but allows users to unhide it

  • Sheet1.Visible = xlSheetVeryHidden: hides the Worksheet and prevents users from unhiding it

    enter image description here