office politics
It's all just 1s and 0s
- Messages
- 6,555
- Location
- in the lab
what we're going to do here is create a excel sheet that has cells assigned for user name and password. A button will be added that will evaluate the data in these cells when the button is clicked. If the user name and password are correct, we will unlock the worksheet named sheet2. Otherwise, we will display login incorrect.
These instructions are for Execl 2007.
open excel
delete sheet3 by right clicking the sheet3 tab at the bottom of the screen.
Sheet2 should be in focus, if not click the Sheet2 tab.
Enter the following text into cell A1 "can't touch this" ...*hammertime*
Autofill the text across to column I by dragging the black box in the lower right hand corner of the active cell to column I.
Autofill the text down to about row 28.
Click Review in the ribbon at the top.
Click Protect Sheet in the ribbon.
Enter this password: passit
Enter the password again to confirm.
Try to change the text in the cells. You should get a read only error.
Click on the Sheet1 tab at the bottom.
In cell A2, enter the following text: username
In cell A3, enter the following text: password
You may need to enable the developer tab in options. Click the start button for excel in the upper left hand corner. Click Excel options at the bottom of the menu. Check the box that says Show Developer tab in the ribbon. Click ok.
Click the developer tab in the ribbon.
Click insert and click the icon in the upper left hand corner label button.
Click and drag a button onto Sheet 1.
The assign macro dialog box will appear. Click New.
Enter the following code under the sub line
Close the visual basic editor window.
Double click the text on the button, then replace it with the word Login.
Save your workbook.
Click on cell b2.
Click the button. Login Bad. should appear in a message box. Click ok to clear.
Enter the following text in cell b2: user
Enter the following text in cell b3: pass
Be sure to press enter to set the text in b3.
Click the button. Login good should appear. Click ok to clear.
Now you should be able to change the text.
A working sample has been attached
These instructions are for Execl 2007.
open excel
delete sheet3 by right clicking the sheet3 tab at the bottom of the screen.
Sheet2 should be in focus, if not click the Sheet2 tab.
Enter the following text into cell A1 "can't touch this" ...*hammertime*
Autofill the text across to column I by dragging the black box in the lower right hand corner of the active cell to column I.
Autofill the text down to about row 28.
Click Review in the ribbon at the top.
Click Protect Sheet in the ribbon.
Enter this password: passit
Enter the password again to confirm.
Try to change the text in the cells. You should get a read only error.
Click on the Sheet1 tab at the bottom.
In cell A2, enter the following text: username
In cell A3, enter the following text: password
You may need to enable the developer tab in options. Click the start button for excel in the upper left hand corner. Click Excel options at the bottom of the menu. Check the box that says Show Developer tab in the ribbon. Click ok.
Click the developer tab in the ribbon.
Click insert and click the icon in the upper left hand corner label button.
Click and drag a button onto Sheet 1.
The assign macro dialog box will appear. Click New.
Enter the following code under the sub line
Code:
If Range("b2").Value = "user" And Range("b3").Value = "pass" Then
MsgBox "Login good."
Worksheets("sheet2").Unprotect "passit"
Worksheets("sheet2").Activate
Else
MsgBox "Login bad."
End If
Close the visual basic editor window.
Double click the text on the button, then replace it with the word Login.
Save your workbook.
Click on cell b2.
Click the button. Login Bad. should appear in a message box. Click ok to clear.
Enter the following text in cell b2: user
Enter the following text in cell b3: pass
Be sure to press enter to set the text in b3.
Click the button. Login good should appear. Click ok to clear.
Now you should be able to change the text.
A working sample has been attached