Hi,
I have a workbook of which I have usernames (column A:A) and passwords
(column B:B) on Sheet3. I have some tables, forms etc. on Sheet1 but I want
to allow using this page to people only which can input matching
username&password with Sheet3. Can anyone suggest VBA solutions?.
TIA

Hi Jack,

create a small form with 2 fields, one for the username the other for the
password.
Call it up from the ThisWorkbook.Workbook_Open event and search for the
username then match the password.

sub cmdOKButton()

with sheet3.UsedRange
CurRow=1

Do Until IsEmpty(Cells(CurRow, 1)) 'Syntax:
Cells(RowIndex,ColumnIndex)
if Cells(CurRow, 1).value=txtUserName.text then
if Cells(CurRow, 2.value=txtPassword.text then
sheets("sheet1").activate
unload me
else
msgbox "the password provided is incorrect,
please try again"
txtPassword.setfocus
end if
else ' Username not found
msgbox "I'm sorry you do seem to be a registered user,
please contact the Administrator"
unload me ' or thisworkbook.close or application.quit
end if
CurRow=CurRow+_1
Loop
end with

end sub

"Jack" wrote in message
...
Hi,
I have a workbook of which I have usernames (column A:A) and passwords
(column B:B) on Sheet3. I have some tables, forms etc. on Sheet1 but I
want
to allow using this page to people only which can input matching
username&password with Sheet3. Can anyone suggest VBA solutions?.
TIA

Thank you so much for your suggestion Philo,
But I'd prefer not to use forms but just hide written data
(usernames&passwords) from Sheet3. Could you modify your code to accomplish
that.
Regards


"Philo Hippo" wrote in message
...
Hi Jack,

create a small form with 2 fields, one for the username the other for the
password.
Call it up from the ThisWorkbook.Workbook_Open event and search for the
username then match the password.

sub cmdOKButton()

with sheet3.UsedRange
CurRow=1

Do Until IsEmpty(Cells(CurRow, 1)) 'Syntax:
Cells(RowIndex,ColumnIndex)
if Cells(CurRow, 1).value=txtUserName.text then
if Cells(CurRow, 2.value=txtPassword.text then
sheets("sheet1").activate
unload me
else
msgbox "the password provided is incorrect,
please try again"
txtPassword.setfocus
end if
else ' Username not found
msgbox "I'm sorry you do seem to be a registered user,
please contact the Administrator"
unload me ' or thisworkbook.close or
application.quit
end if
CurRow=CurRow+_1
Loop
end with

end sub

"Jack" wrote in message
...
Hi,
I have a workbook of which I have usernames (column A:A) and passwords
(column B:B) on Sheet3. I have some tables, forms etc. on Sheet1 but I
want
to allow using this page to people only which can input matching
username&password with Sheet3. Can anyone suggest VBA solutions?.
TIA

First, worksheet and workbook protection (via tools|protection) is very weak.
It can be broken in just moments with a macro that's readily available.

J.E. McGimpsey has code that'll unprotect your worksheet within moments:
http://www.mcgimpsey.com/excel/removepwords.html

But if you still want to try...

Rightclick on the excel icon to the left of the File option on the worksheet
menubar.

Select view code and paste this in:


Option Explicit
Const Sheet1Pwd As String = "hi"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim resp As Long

If Me.Saved Then
'already saved
Exit Sub
End If

resp = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Close")

Select Case resp
Case vbYes
Call Workbook_BeforeSave(False, False)
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Sheet1Protection As Boolean

Cancel = True 'don't let excel save it

If SaveAsUI = True Then
MsgBox "can't save this workbook as a new name!"
Exit Sub
End If

Sheet1Protection = Worksheets("sheet1").ProtectContents

If Sheet1Protection Then
'already protected
Else
Worksheets("sheet1").Protect Password:=Sheet1Pwd
End If

Application.EnableEvents = False
Me.Save
Application.EnableEvents = True

If Sheet1Protection Then
'still protected
Else
Worksheets("sheet1").Unprotect Password:=Sheet1Pwd
Me.Saved = True
End If

End Sub
Private Sub Workbook_Open()

Dim myUser As String
Dim myPwd As String
Dim myValidationRng As Range
Dim res As Variant

Worksheets("sheet1").Protect Password:=Sheet1Pwd

With Worksheets("sheet3")
Set myValidationRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

myUser = InputBox("Enter your UserName for sheet1")

If Trim(myUser) = "" Then
'do nothing
Else
res = Application.Match(myUser, myValidationRng, 0)
If IsError(res) Then
MsgBox "Not authorized"
Else
myPwd = InputBox("Enter your Password for sheet1")
If myValidationRng(res, 2).Value = myPwd Then
Worksheets("sheet1").Unprotect Password:=Sheet1Pwd
Else
MsgBox "wrong password"
End If
End If
End If

End Sub


But if the user breaks the password, disables macros, or disables events, then
lots of things could go wrong.

(I wouldn't bother with this kind of protection--it's just a false sense of
security.)



Jack wrote:

Thank you so much for your suggestion Philo,
But I'd prefer not to use forms but just hide written data
(usernames&passwords) from Sheet3. Could you modify your code to accomplish
that.
Regards

"Philo Hippo" wrote in message
...
Hi Jack,

create a small form with 2 fields, one for the username the other for the
password.
Call it up from the ThisWorkbook.Workbook_Open event and search for the
username then match the password.

sub cmdOKButton()

with sheet3.UsedRange
CurRow=1

Do Until IsEmpty(Cells(CurRow, 1)) 'Syntax:
Cells(RowIndex,ColumnIndex)
if Cells(CurRow, 1).value=txtUserName.text then
if Cells(CurRow, 2.value=txtPassword.text then
sheets("sheet1").activate
unload me
else
msgbox "the password provided is incorrect,
please try again"
txtPassword.setfocus
end if
else ' Username not found
msgbox "I'm sorry you do seem to be a registered user,
please contact the Administrator"
unload me ' or thisworkbook.close or
application.quit
end if
CurRow=CurRow+_1
Loop
end with

end sub

"Jack" wrote in message
...
Hi,
I have a workbook of which I have usernames (column A:A) and passwords
(column B:B) on Sheet3. I have some tables, forms etc. on Sheet1 but I
want
to allow using this page to people only which can input matching
username&password with Sheet3. Can anyone suggest VBA solutions?.
TIA





--

Dave Peterson

Thank you Dave,
I also agree your comments on security. The macro will only be used for
educational purposes....

"Dave Peterson" wrote in message
...
First, worksheet and workbook protection (via tools|protection) is very
weak.
It can be broken in just moments with a macro that's readily available.

J.E. McGimpsey has code that'll unprotect your worksheet within moments:
http://www.mcgimpsey.com/excel/removepwords.html

But if you still want to try...

Rightclick on the excel icon to the left of the File option on the
worksheet
menubar.

Select view code and paste this in:


Option Explicit
Const Sheet1Pwd As String = "hi"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim resp As Long

If Me.Saved Then
'already saved
Exit Sub
End If

resp = MsgBox("Do you want to save your changes?", vbYesNoCancel,
"Close")

Select Case resp
Case vbYes
Call Workbook_BeforeSave(False, False)
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim Sheet1Protection As Boolean

Cancel = True 'don't let excel save it

If SaveAsUI = True Then
MsgBox "can't save this workbook as a new name!"
Exit Sub
End If

Sheet1Protection = Worksheets("sheet1").ProtectContents

If Sheet1Protection Then
'already protected
Else
Worksheets("sheet1").Protect Password:=Sheet1Pwd
End If

Application.EnableEvents = False
Me.Save
Application.EnableEvents = True

If Sheet1Protection Then
'still protected
Else
Worksheets("sheet1").Unprotect Password:=Sheet1Pwd
Me.Saved = True
End If

End Sub
Private Sub Workbook_Open()

Dim myUser As String
Dim myPwd As String
Dim myValidationRng As Range
Dim res As Variant

Worksheets("sheet1").Protect Password:=Sheet1Pwd

With Worksheets("sheet3")
Set myValidationRng = .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp))
End With

myUser = InputBox("Enter your UserName for sheet1")

If Trim(myUser) = "" Then
'do nothing
Else
res = Application.Match(myUser, myValidationRng, 0)
If IsError(res) Then
MsgBox "Not authorized"
Else
myPwd = InputBox("Enter your Password for sheet1")
If myValidationRng(res, 2).Value = myPwd Then
Worksheets("sheet1").Unprotect Password:=Sheet1Pwd
Else
MsgBox "wrong password"
End If
End If
End If

End Sub


But if the user breaks the password, disables macros, or disables events,
then
lots of things could go wrong.

(I wouldn't bother with this kind of protection--it's just a false sense
of
security.)



Jack wrote:

Thank you so much for your suggestion Philo,
But I'd prefer not to use forms but just hide written data
(usernames&passwords) from Sheet3. Could you modify your code to
accomplish
that.
Regards

"Philo Hippo" wrote in message
...
Hi Jack,

create a small form with 2 fields, one for the username the other for
the
password.
Call it up from the ThisWorkbook.Workbook_Open event and search for
the
username then match the password.

sub cmdOKButton()

with sheet3.UsedRange
CurRow=1

Do Until IsEmpty(Cells(CurRow, 1)) 'Syntax:
Cells(RowIndex,ColumnIndex)
if Cells(CurRow, 1).value=txtUserName.text then
if Cells(CurRow, 2.value=txtPassword.text then
sheets("sheet1").activate
unload me
else
msgbox "the password provided is
incorrect,
please try again"
txtPassword.setfocus
end if
else ' Username not found
msgbox "I'm sorry you do seem to be a registered
user,
please contact the Administrator"
unload me ' or thisworkbook.close or
application.quit
end if
CurRow=CurRow+_1
Loop
end with

end sub

"Jack" wrote in message
...
Hi,
I have a workbook of which I have usernames (column A:A) and
passwords
(column B:B) on Sheet3. I have some tables, forms etc. on Sheet1 but
I
want
to allow using this page to people only which can input matching
username&password with Sheet3. Can anyone suggest VBA solutions?.
TIA





--

Dave Peterson

OK but first clarify this for me please, how will you know who's who? Get
the logged on username?

"Jack" wrote in message
...
Thank you so much for your suggestion Philo,
But I'd prefer not to use forms but just hide written data
(usernames&passwords) from Sheet3. Could you modify your code to
accomplish
that.
Regards


"Philo Hippo" wrote in message
...
Hi Jack,

create a small form with 2 fields, one for the username the other for
the
password.
Call it up from the ThisWorkbook.Workbook_Open event and search for the
username then match the password.

sub cmdOKButton()

with sheet3.UsedRange
CurRow=1

Do Until IsEmpty(Cells(CurRow, 1)) 'Syntax:
Cells(RowIndex,ColumnIndex)
if Cells(CurRow, 1).value=txtUserName.text then
if Cells(CurRow, 2.value=txtPassword.text then
sheets("sheet1").activate
unload me
else
msgbox "the password provided is incorrect,
please try again"
txtPassword.setfocus
end if
else ' Username not found
msgbox "I'm sorry you do seem to be a registered
user,
please contact the Administrator"
unload me ' or thisworkbook.close or
application.quit
end if
CurRow=CurRow+_1
Loop
end with

end sub

"Jack" wrote in message
...
Hi,
I have a workbook of which I have usernames (column A:A) and passwords
(column B:B) on Sheet3. I have some tables, forms etc. on Sheet1 but I
want
to allow using this page to people only which can input matching
username&password with Sheet3. Can anyone suggest VBA solutions?.
TIA