Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
Try
UserPass = Sheets("HiddenPass").Cells(4, 4).Value or UserPass = Sheets("HiddenPass").range("D4").Value -- HTH ------- Bob Phillips "Saadi" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
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" wrote: Try UserPass = Sheets("HiddenPass").Cells(4, 4).Value or UserPass = Sheets("HiddenPass").range("D4").Value -- HTH ------- Bob Phillips "Saadi" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
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) "Saadi" wrote in message ... 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" wrote: Try UserPass = Sheets("HiddenPass").Cells(4, 4).Value or UserPass = Sheets("HiddenPass").range("D4").Value -- HTH ------- Bob Phillips "Saadi" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
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" wrote: 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) "Saadi" wrote in message ... 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" wrote: Try UserPass = Sheets("HiddenPass").Cells(4, 4).Value or UserPass = Sheets("HiddenPass").range("D4").Value -- HTH ------- Bob Phillips "Saadi" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
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) "Saadi" wrote in message ... 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" wrote: 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) "Saadi" wrote in message ... 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" wrote: Try UserPass = Sheets("HiddenPass").Cells(4, 4).Value or UserPass = Sheets("HiddenPass").range("D4").Value -- HTH ------- Bob Phillips "Saadi" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing value in a variable from a cell
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) "Bob Phillips" wrote in message ... 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) "Saadi" wrote in message ... 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" wrote: 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) "Saadi" wrote in message ... 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" wrote: Try UserPass = Sheets("HiddenPass").Cells(4, 4).Value or UserPass = Sheets("HiddenPass").range("D4").Value -- HTH ------- Bob Phillips "Saadi" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing textin a variable and comparing to a second value | Excel Discussion (Misc queries) | |||
Storing a range variable in a cell | Excel Discussion (Misc queries) | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
Storing a Macro name in a Variable | Excel Programming | |||
Excel VBA - Storing text in a variable, and "'Cells' of object _Global failed" | Excel Programming |