Create a variable passcode for unlocking sheet

Daugaard92

New Member
Joined
Nov 20, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hello.

So I've sheet, which I'd like to lock and "hide" from others on our shared network.

My preliminary solution to this is using codes that autorun, when the worksheets opens/closes:

Sub Auto_Open()

Dim MyInput As String
MyInput = InputBox("Indtast venligst koden til arket. Kontakt evt. Mathias Laursen.", "Indtast Kode.", "INDTAST KODE HER")


If MyInput = "PASSWORD" Then

ActiveWorkbook.Unprotect "PASSWORD"
Range("A1").Value = "Ulåst"
Sheets("Målstyring").Visible = True
Sheets("Mål").Visible = True
Sheets("Mål").Select
Sheets("Forside").Visible = False

Else

MsgBox ("Forkert kode. Prøv venligst igen, eller kontakt Mathias Laursen.")

End If


End Sub


When the sheet opens it prompts for the "PASSWORD"-input - and I've a similar code which runs, when the workbook closes. When the worksheet is in its "locked"-state, it prevents the user from viewing and/or accessing the hidden sheets ("Mål") and ("Målstyring") - and instead forcing the user to view the ("Forside")-sheet.

As you can tell, I'm very new to VBA and a flaw of the method above, is obviously that the user can simply see the password to unlock the sheet, by accessing VBA (Though few of my coworkers use it). Still, I'd like to make a better password/lock for the sheet.

Is it therefore possible; In VBA, to make a user-generated password with input, which the user can't see when viewing the code? Alternatively, if anoyone know of a simpler and better method for both locking and hiding a sheet - Please feel free to comment. I hope it makes sense. Cheers.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
391
Office Version
  1. 2016
Platform
  1. Windows
There is not a way to 100% protect your workbook.
You can do that in the higher or lower level.
There is many hacker over internet who can hack workbook for 5$.
If you have some data in workbook with no heigh security needs be carefull,
otherwise you are free to explore some solutions.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
391
Office Version
  1. 2016
Platform
  1. Windows
Correction!!!
If you have some data in workbook with heigh security needs be carefull,
otherwise you are free to explore some solutions.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,973
As EXCEL MAX stated, you can lock the VBE so your code is not easily viewed. However, a little searching on the internet will provide anyone with
instructions how to by-pass it.

There are 3rd Party programs out there for purchase that will obscure (scramble) your code so it is unreadable. The casual Excel User will not be able
to understand your code if you choose this route. A dedicated coder will be able to crack it ... but then ... there are hackers everywhere who are more
interested in breaking into CIA Headquarters computers vs your workbook.

Another method would be to create a macro that hides the password in a text file located in an obscure area of the users computer hard drive. Your macro
would first match their password entry against the password in the text file prior to opening or not opening the workbook.
 
Solution

Daugaard92

New Member
Joined
Nov 20, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
As EXCEL MAX stated, you can lock the VBE so your code is not easily viewed. However, a little searching on the internet will provide anyone with
instructions how to by-pass it.

There are 3rd Party programs out there for purchase that will obscure (scramble) your code so it is unreadable. The casual Excel User will not be able
to understand your code if you choose this route. A dedicated coder will be able to crack it ... but then ... there are hackers everywhere who are more
interested in breaking into CIA Headquarters computers vs your workbook.

Another method would be to create a macro that hides the password in a text file located in an obscure area of the users computer hard drive. Your macro
would first match their password entry against the password in the text file prior to opening or not opening the workbook.

Too bad; I'll have to settle for my mediocre solution then - But as you said, hackers interested in stuff other than my workbook :)

Thanks to the both of you regardless.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,831
Messages
5,627,149
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top