Storing value in a variable from a cell

[Guest]

hi I am try to make password protected workseets. every worksheet has its own
password, stroed in a sheet named "HiddenPass". The problem is that how can I
pick the value of Cell D4 of Sheet "HiddenPass" when I click on sheet
"Biodata". I am using "UserPass" as variable in Activate event of workseet
"Biodata".

The code I am using (but not worked)

UserPass = Sheets("Biodata").Cells(4, 4).Value


anyone could help.

Saadi

 

[Bob Phillips]

Try

UserPass = Sheets("HiddenPass").Cells(4, 4).Value

or

UserPass = Sheets("HiddenPass").range("D4").Value

 

[Guest]

It worked, the complete code is given below for others. But there is still a
problem. While it works correctly, but the Activate Event displays the
contants of the sheet , when the Inputbox asks the password from the user.
How do I do, that no one can see the contents of Sheet without entering the
Password.

The code is,

Private Sub Worksheet_Activate()
Dim Pass As String
Dim UPass As String
Pass = Sheets("Password").Range("A3").Value
UPass = InputBox("Enter Your Password")
If UPass <> Pass Then
MsgBox("Your Password is Invalid")
Application.Sheets("Sheet1").Activate
End If
End Sub

 

[Bob Phillips]

Saadi,

Hide the sheet. Go into the VBE and make it very hidden, by typing

Worksheets("sheet_name").Visible = xlSheetVeryHidden

and then use this code in the worksheet code module

Private Sub Worksheet_Activate()
Dim Pass As String
Dim UPass As String
Pass = "ABC" 'Sheets("Password").Range("A3").Value
UPass = InputBox("Enter Your Password")
If UPass <> Pass Then
MsgBox ("Your Password is Invalid")
Application.Sheets("Sheet1").Activate
Else
Me.Visible = xlSheetVisible
End If
End Sub

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub


HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I got your point but there is still confusion, if a sheet is in hidden mode,
then how could it be clicked for activation to blast the event Activate. And
also the Worksheets(“Passwordsâ€).Visible = xlSheetVeryHidden code where will
be placed ?

 

[Bob Phillips]

Good point .

How about this variation which hides it when clicked then unhides it, scrap
the deactivate event

Private Sub Worksheet_Activate()
Dim Pass As String
Dim UPass As String
Me.Visible = xlSheetHidden
Pass = "ABC" 'Sheets("Password").Range("A3").Value
UPass = InputBox("Enter Your Password")
If UPass <> Pass Then
MsgBox ("Your Password is Invalid")
Application.Sheets("Sheet1").Activate
Else
Me.Visible = xlSheetVisible
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

A better variation

Private Sub Worksheet_Activate()
Dim Pass As String
Dim UPass As String
Me.Visible = xlSheetHidden
Pass = Sheets("Password").Range("A3").Value
UPass = InputBox("Enter Your Password")
If UPass <> "" Then
If UPass <> Pass Then
MsgBox ("Your Password is Invalid")
Application.Sheets("Sheet1").Activate
Me.Visible = xlSheetVisible
Else
Application.EnableEvents = False
Me.Activate
Application.EnableEvents = True
Me.Visible = xlSheetVisible
End If
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)