| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE Code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBE Code
The code you posted is obviously designed to provide a degree of security for
the data which knowing the password will allow one to access. Before compromising that security by changing the code as it now exists, you should get the originator of the code to authorize you to make the change. "Alison84" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBE Code
I am the originator.
I had a lot of help writing this though and have tried my best to get it to show me all of them when I use "Password1" but can't figure it out. "JLGWhiz" wrote: The code you posted is obviously designed to provide a degree of security for the data which knowing the password will allow one to access. Before compromising that security by changing the code as it now exists, you should get the originator of the code to authorize you to make the change. "Alison84" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBE Code
Then it looks like you need to add another case for "Password1" so that when
it is the "strPasswd" , you can unhide all of the rows with: Worksheets(1).Rows.Hidden = False You will need to change the Worksheets index number or name to the appropriate one, but that line will unhide all rows on the applicable sheet. It works the same with columns. If this is not what you needed, then maybe additional explanation will help. "Alison84" wrote: I am the originator. I had a lot of help writing this though and have tried my best to get it to show me all of them when I use "Password1" but can't figure it out. "JLGWhiz" wrote: The code you posted is obviously designed to provide a degree of security for the data which knowing the password will allow one to access. Before compromising that security by changing the code as it now exists, you should get the originator of the code to authorize you to make the change. "Alison84" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| 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 | |||
Linear Mode
