Macros for changing variables with password

Right click on the sheet1 tab, select "view code" and copy & paste below macro there.
It's a worksheet change macro, that checks if you change either cell D6 (name) or cell D8 (password).

The rest is pretty self explanatory (and I put some explanation in there) - see if you can figure it out

Private Sub Worksheet_Change(ByVal Target As Range)

'A "worksheet_change" macro automatically fires whenever a cell on the worksheet changes - that's why I needs to be on the worksheet and not in a module, cause it's worksheet dependant.

Dim pass As String, name As String 'set the variables used - we'll be looking at the passwords and the names

If Target = Range("D6") Then Range("D8").Value = "" 'if Range D6 changes, then remove the password from D8"

If Target <> Range("D8") Then Exit Sub 'if the change occurs in ANY other cell than D8, just exit the sub and do nothing
pass = UCase(Range("D8").Value) 'Since we only check for changes in range D8, that's where the password is - set the variable pass to hold the password, in upper case
name = UCase(Range("D6").Value) 'We also set the variable name to hold the name as in cell D6, also in upper case

Application.EnableEvents = False
Target.Value = "*****" 'change the cell D8 so the password isn't shown anymore - we disable the events for this part, cause we're changing cell D8 and don't want the macro to fire again
Application.EnableEvents = True

'If the name and passwords match any of the allowed combinations, we continue
If name = "ALEX" And pass = "ABC" Then GoTo continue
If name = "JACK" And pass = "DEF" Then GoTo continue
If name = "JAMES" And pass = "GHI" Then GoTo continue
If name = "JOHN" And pass = "JKL" Then GoTo continue

'Else (if there's no match) - the sheets with data are set to be hidden, so the user can't check them, then we exit the sub
Sheet2.Visible = False
Sheet3.Visible = False
Sheet4.Visible = False
Sheet5.Visible = False
Exit Sub

continue:
'If we continue, we don't want excel to 'jump all over the place', so we turn off screenupdating for the rest of the macro
Application.ScreenUpdating = False

'Since there was a correct name & pass entered, sheet2 to 5 can be shown
Sheet2.Visible = True
Sheet3.Visible = True
Sheet4.Visible = True
Sheet5.Visible = True

'Normally, sheet 2 to 5 are protected (so the user can't show other users data), with the password "password" - since we need to change things on the sheet, we unprotect it
Sheet2.Unprotect Password:="password"
'We check which is the bottom row in column L that holds data and hide row L1 until that row, basically hiding all data
Sheet2.Range("L1:L" & Sheet2.Range("L65536").End(xlUp).Row).EntireRow.Hidden = True

'Then we check every row from L1 to the row with the bottom data, to check if it either contains "Sales Rep Name" or the name as selected on sheet1. If there's a match, we unhide those rows. The rest of the rows stay hidden

For Each cell In Sheet2.Range("L1:L" & Sheet2.Range("L65536").End(xlUp).Row)
If cell.Value = "Sales Rep name" Then cell.EntireRow.Hidden = False
If UCase(cell.Value) = name Then cell.EntireRow.Hidden = False
Next cell

'We protect the sheet again, so the user can't unhide other users data
Sheet2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

'This process is repeated for the other sheets.

Sheet3.Unprotect Password:="password"
Sheet3.Range("L1:L" & Sheet3.Range("L65536").End(xlUp).Row).EntireRow.Hidden = True
For Each cell In Sheet3.Range("L1:L" & Sheet3.Range("L65536").End(xlUp).Row)
If cell.Value = "Sales Rep name" Then cell.EntireRow.Hidden = False
If UCase(cell.Value) = name Then cell.EntireRow.Hidden = False
Next cell
Sheet3.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

Sheet4.Unprotect Password:="password"
Sheet4.Range("L1:L" & Sheet4.Range("L65536").End(xlUp).Row).EntireRow.Hidden = True
For Each cell In Sheet4.Range("L1:L" & Sheet4.Range("L65536").End(xlUp).Row)
If cell.Value = "Sales Rep name" Then cell.EntireRow.Hidden = False
If UCase(cell.Value) = name Then cell.EntireRow.Hidden = False
Next cell
Sheet4.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

Sheet5.Unprotect Password:="password"
Sheet5.Range("L1:L" & Sheet5.Range("L65536").End(xlUp).Row).EntireRow.Hidden = True
For Each cell In Sheet5.Range("L1:L" & Sheet5.Range("L65536").End(xlUp).Row)
If cell.Value = "Sales Rep name" Then cell.EntireRow.Hidden = False
If UCase(cell.Value) = name Then cell.EntireRow.Hidden = False
Next cell
Sheet5.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

'At the end of the macro, we allow the screen to update again. If we didn't, Excel would basically 'freeze' and become useless.
Application.ScreenUpdating = True

'That's it - easy, isn't it :)
End Sub

The code could be made smaller with a "Do until" loop and then use a variable to select the sheet, but since you're new to VBA that might be more daunting then necessary, I decided to keep the repeated steps in, so you can understand it more easily. If you want a bit of a challenge, consider to use this instead :

Replace the entire code with this :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pass As String, name As String, i as integer

If Target = Range("D6") Then Range("D8").Value = "" 
If Target <> Range("D8") Then Exit Sub 

pass = UCase(Range("D8").Value) 
name = UCase(Range("D6").Value) 

Application.EnableEvents = False
Target.Value = "*****" 
Application.EnableEvents = True

If name = "ALEX" And pass = "ABC" Then GoTo continue
If name = "JACK" And pass = "DEF" Then GoTo continue
If name = "JAMES" And pass = "GHI" Then GoTo continue
If name = "JOHN" And pass = "JKL" Then GoTo continue

Sheet2.Visible = False
Sheet3.Visible = False
Sheet4.Visible = False
Sheet5.Visible = False
Exit Sub

continue:
Application.ScreenUpdating = False

i = 2
Do Until i = 6
Sheets(i).Visible = True
Sheets(i).Unprotect Password:="password"
Sheets(i).Range("L1:L" & Sheets(i).Range("L65536").End(xlUp).Row).EntireRow.Hidden = True
For Each cell In Sheets(i).Range("L1:L" & Sheets(i).Range("L65536").End(xlUp).Row)
If cell.Value = "Sales Rep name" Then cell.EntireRow.Hidden = False
If UCase(cell.Value) = name Then cell.EntireRow.Hidden = False
Next cell
Sheets(i).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
i = i + 1
Loop

Application.ScreenUpdating = True
End Sub

For the buttons on your sheet to work well, change the button code to :

If Sheets("Sheet2").Visible = True Then Sheets("Sheet2").Activate

These macros are in module1 and not on the page - so make sure to change them there.

Ha Ha no need to apologize!! you're the one helping me out

But that still doesnt work, the same issue is there.

Also if possible could you let me know how to enter a msgbox if the username /password combo is correct?

only if its not too much of an inconvenience!!

Much Thanks JasperD