VBA code for password to open excel

Copper Contributor
Can anyone please help with a VBA code that can be used to ask for password when opening a Excel sheet? There can be a y no of sheets in the work book but none can be accessed if password is wrong..

For example when you open an Excel sheet by double clicking it, it will ask for enter the password and then only u can enter into this..

I know I can do that from file menu other way but I need only to get it done through VBA.
11 Replies

@amitmahapatra 

VBA and macro scripting

You cannot create macros with VBA in Excel for the web, but you can open and edit VBA-enabled spreadsheets without removing (or corrupting) the VBA contained in the file. Learn more about getting started with macros.

 

You could use this code with other versions.
Only if it is your own file, otherwise you could be liable to prosecution.

 

Hope I was able to help you.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

 

ups the same Person as before...sry dont see it.
Hi.. I am looking for adding a password. I am not looking to remove.

If we can't use VBA, can you please help the piece of code that can be used to write a macro that can add a password to the excel.. A simple macro..

@amitmahapatra 

Here is a sample file to insert the password.
Have you fished from the Internet before long time not had more the side. I have used this example a few times with good results. You can of course make it even better.

 

I hope to make some amends could :)

 

Hope I was able to help you.

Nikolino

I know I don't know anything (Socrates)

@amitmahapatra  similar to @NikolinoDE  I recommend showing a sheet upon opening but using that sheet to make sure the user knows that macros must be enabled and I would also put a button on that sheet to "Login".  I would also recommend that you lock the macro project so people don't circumvent your macro/password.

@mtarler  @NikolinoDE 

 

Really thanks so much for all the help and its shaping into what i am looking for but little far off.. What you are proposing is to have 1 sheet with a button or something to ask for password .. but it allows to open the excel sheet.

 

But I am looking for password to be prompt when opening the excel sheet (through Macro or VBA) as attached the attached file...

 

Do you guys think that can be possible ?

@amitmahapatra  So I assume you know you can do this using built in Excel protection but it sounds like you specifically want a Macro solution. In the attached I modified @NikolinoDE solution to hide the protected sheets.  However, I keep 1 "public" sheet visible (hide all the other sheets) so you can prompt the user with information.  It can be frustrating to a user that opens a locked workbook and sees nothing and no password prompt because of their security settings.   I would also recommend locking the macro project but left it unlocked for you to work with easier.  And if you insist you can delete/hide that public sheet also, I just don't recommend it. 

Hi @mtarler 

 

yes you are correct. i know how to do it but i want it through macro to use same feature while opening from OneDrive in office365 web.

 

Now the the file i opened in my desktop. i got a warning to top ribbon asking "Enable Macro" and after i click that i did not got the prompt rather the way in attached screenshot. It has frozen the screen and nothing can be done. So i had to kill it by "Task Manager".

 

Then i had uploaded to OneDrive and tried to open it from there (that was my original requirement) .. so it has nothing. it did not ask for password at all. please find the screen shot attached. 

@amitmahapatra So the password window was tiny for some reason.  you could actually still type in it and it would work but yeah that is a problem (4k monitor issues).  As for drive, it doesn't let macros run.  so if this is all about drive then that is a problem. In the attached I resized the password window but again I don't think this will solve you problem.

@amitmahapatra 

As already,  @mtarler informed you,  the combination of password with VBA and Excel with OneDrive for Web is (to put it mildly) not the most suitable.

Protect / Unprotect is a feature is something which is not supported by Onedrive. Currently, the only option is to download and then protect/unprotect and then reupload.
As far as I know, but... what do I know :).

Workbooks that are protected (encrypted with password protection) cannot be viewed in a browser window or opened via Excel Online. And to open workbooks with password protection, we need to download them to the local PC and open them with Excel.

Here is an article for your reference: Workbook features that can differ between the browser and the desktop

 

Excel worksheet protection in Excel Online

 
Attached are the instructions for doing it yourself, where no worksheet can be seen, but is only for the entire workbook.

Protect an Excel file - For do-it-yourself

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac

To prevent others from accessing data in your Excel files, protect your Excel file with a password. 

https://support.microsoft.com/en-us/office/protect-an-excel-file-7359d4ae-7213-4ac2-b058-f75e9311b59...

 

Protect a worksheet

That was all I could give you in terms of information.
If you want to use VBA but with changes that you want, please simply give us a message.
 

Thank you for your understanding and patience

 

Wish you a nice day.

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE and  @mtarler .. thanks so much for all your help and time.