00:00Have you ever wondered how you can protect your Excel workbook using OTP or also known as one-time
00:05password which is adopted by companies like Google and Amazon to enhance their security posture.
00:11The main idea behind OTP is that the workbook password will change every time when you open
00:16your workbook. Well, this is how you do it. You're going to right click on any of this sheet here
00:21and then from the context menu, you're going to select view code and a VBA editor will open up.
00:27You're going to double click on this workbook like this and then you're going to paste this VBA
00:32script here. If you need a copy of this VBA script, please make a request on the comment section of
00:38this video. The way this script works is that every time when you open a workbook, a new password is
00:44assigned to this target workbook and at the same time, the same password is also stored in another
00:50Excel spreadsheet called OTPXLS file here and this is where you can customize it here. If you want
00:57to improve the security, you can actually locate this OTP.xls file on a separate network drive
01:04somewhere so that is away from prying eyes. So a quick overview of how this work is that, like I
01:10said, when you open this workbook, this particular subroutine is invoked and when they invoke this
01:16subroutine, this line here actually generates four random digits ranging from 1000 to 9999 and store on
01:24this variable called code. And this code is written to this OTPXLS file on cell A1 here. And at the
01:34same
01:34time, the same code is also assigned to this very workbook when you initially open up the VBA. Let's go
01:42ahead and test this OTP. You're going to press Ctrl S to save this VBA and the workbook and close
01:49this VBA
01:50editor. Next, you're going to close this target workbook here and going to save it here. A quick
01:55overview. This is the OTPXLS file. Right now, if you open it up, there's essentially nothing on that
02:02workbook here. You can see it's completely blank. So when I open the target workbook here, this cell A1
02:09will be filled up with a new passcode and at the same time, the same password will be assigned to
02:16your
02:16workbook. Now when I launch the target workbook on the first launch, you are not required to enter the
02:23password. Let me show you. You can see that the target workbook opens without the need to enter the
02:29passcode. But now if you open this OTPXLS file, you can see that four digit random number is also
02:36visible for you. And this four digit number is also assigned as a passcode to this particular workbook
02:43here. Now if I were to close this guide here and open my target workbook for the second time, as
02:48you
02:48can see, I'm required to enter the passcode here. And this will be my passcode. So if I were to
02:54press
02:551158 and just before I click on OK, you want to keep an eye on this 1158. When I click
03:01on OK, you will see
03:03a new passcode will be visible and that new passcode will be required on your third opening of your target
03:10workbook. You can see that there's a sudden change of number there. Now if you were to open this guy
03:15up
03:15here, you can see a brand new code has been assigned to cell A1. And at the same time, that
03:21code is also
03:22assigned to this target. So the next time when you open this guy here, this particular 9616 is your new
03:29passcode. Let me demonstrate. I'm going to close this guy and reopen the target workbook one more time
03:34and enter this passcode 9616. So moving forward, any future opening of this target workbook, you need
03:43to open up this workbook and have a look at what the password is.
Comments