Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi,
I have a code to show different rows of a worksheet that asks for a password when the workbook is opened as follows: Option Explicit Private Sub Workbook_open() Sheets("Risk Log").Select ActiveSheet.Rows("2:500").Select Selection.EntireRow.Hidden = True ActiveSheet.Cells(1, 1).Select Dim strPasswd As String strPasswd = InputBox("Please enter Password", "Restricted Access") 'Check to see if there is any entry made to input box, or if 'cancel button is pressed. If no entry made then exit sub. If strPasswd = "" Or strPasswd = Empty Then MsgBox "Please enter the abbreviated name of your department or your password if you have been assigned one. Please note they are case sensative", vbInformation, "Required Data" Sheets(Sheet2).Select ActiveSheet.Cells(1, 1).Select Exit Sub End If 'If correct password is entered open Employees form 'If incorrect password entered give message and exit sub Select Case strPasswd Case "Hockey" Call ShowOrHide("Commercial") Exit Sub Case "ESD" Call ShowOrHide("Environment & Sustainable Development") Exit Sub Case "CCE" Call ShowOrHide("Culture, Ceremonies & Education") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case "" Call ShowOrHide("") Exit Sub Case Else MsgBox "Please enter the correct password, they are case sensative", _ vbOKOnly, "Important Information" Sheets(Sheet2).Select ActiveSheet.Cells(1, 1).Select End Select 'If correct password is entered open Employees form 'If incorrect password entered give message and exit sub strPasswd = InputBox("Please enter Password", "Restricted Access") Select Case strPasswd Case "Hockey" Call ShowOrHide("Commercial") Case "ESD" Call ShowOrHide("ESD") Case "CCE" Call ShowOrHide("Culture, Ceremonies & Education") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case "" Call ShowOrHide("") Case Else MsgBox "You have not entered the correct information this workbook will now close. Please contact Alison to obtain your password.", _ vbOKOnly, "Important Information" ActiveWorkbook.Close True End Select End Sub Private Sub ShowOrHide(department As String) Dim count As Integer Dim currentDept As String '''Define rows needing searched Dim rowFrom As Integer Dim rowTo As Integer '''CHANGE HERE IF NEW RISKS ADDED'''' rowFrom = 2 rowTo = 500 '''''''''''''''''''''''''''''''''''' For count = rowFrom To rowTo currentDept = Cells(count, 1).Value If (currentDept = department) Then ActiveSheet.Rows(count).Select Selection.EntireRow.Hidden = False Else ActiveSheet.Rows(count).Select Selection.EntireRow.Hidden = True End If Next count End Sub What I need to do is set it so that if I enter password "Password1" it will show me everything. I tried to do it myself but I can't make it work and I don't know too much about VBA so need some help. Thanx, A |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |