software

I have a spread sheet that someone else wrote for doing some simple
scheduling. It has cells that the user fills in with their name to place
themselves on the schedule. I need to fix it so that once the cell has a name
in it, it can not be removed/changed without a password. I have done a bit of
VB and VBA programming, but have no idea how to do it or gain access to the
quot;Codequot; side of a cell in Excel to do a simple check for existing data and if
not blank require a password to change. Once this security is implemented I
would also need to know how to lockdown the spread sheet so that the only
thing that a user can do without a password is put their name in a blank
quot;time slotquot; cell. I don't want any one going in to design mode and finding to
password etc. This is used by students and right now there are some who will
erase someone’s name and then put theirs in place of it so that they can get
some of the coveted spots on the calendar.

Anyone with any suggestions for how to do this or a good quick free online
tutorial that will show how to do it will be forever in my debt. Thanks for
all your help.

Ralph MalphRalph,

The following code goes in the code module for the scheduling sheet.
You access that by right-clicking the sheet tab and selecting quot;View Codequot;.
The code prevents entry into any cell on the sheet. However, when any cell
in Column B is double-clicked then, if the cell is empty, an entry can be made.
You should change quot;Bquot; to the column with the students name.
All three instances of quot;Passwordquot; should be replaced with your choice of password.
The code is dynamic, so you will not be able to make changes to the sheet
unless you disable the code.
To protect the code: while in the code module, go to...
Tools | VBAProjectProperties | Protection (tab) ...
Enter a password and checkmark quot;Lock project for viewingquot;.
Use a different password from that used in the code.

Regards,
Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftware

'-------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Me.Columns(quot;Bquot;), Target) Is Nothing Then
If Len(Target.Value) Then
Cancel = True
Else
Me.Unprotect quot;Passwordquot;
Target.Locked = False
End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Unprotect quot;Passwordquot;
Target.Locked = True
Me.Protect quot;Passwordquot;
End Sub
'------------------quot;Ralph Malphquot; gt;
wrote in message...
I have a spread sheet that someone else wrote for doing some simple
scheduling. It has cells that the user fills in with their name to place
themselves on the schedule. I need to fix it so that once the cell has a name
in it, it can not be removed/changed without a password. I have done a bit of
VB and VBA programming, but have no idea how to do it or gain access to the
quot;Codequot; side of a cell in Excel to do a simple check for existing data and if
not blank require a password to change. Once this security is implemented I
would also need to know how to lockdown the spread sheet so that the only
thing that a user can do without a password is put their name in a blank
quot;time slotquot; cell. I don't want any one going in to design mode and finding to
password etc. This is used by students and right now there are some who will
erase someone’s name and then put theirs in place of it so that they can get
some of the coveted spots on the calendar.

Anyone with any suggestions for how to do this or a good quick free online
tutorial that will show how to do it will be forever in my debt. Thanks for
all your help.

Ralph Malph
Thank you Jim for all your help,

The code worked but I did have to make some adjustments to get it to prompt
for a password and to handle multiple columns for input. The only problem I
had was that I get security warnings about macros and it gets disabled unless
I lower the security settings. I would like avoid doing this but keep the
code working. Any suggestions, I have seen where if it is digitally “signed”
it will pass as safe? I would like to do this but I don’t know how to. I also
need a digital signature, but I of course have no funds for it.

Thanks again for all your help, here is the modified code I am now using.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim strPassword As String

If (Not Application.Intersect(Me.Columns(quot;Cquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Gquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Kquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Oquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Squot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Wquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;AAquot;), Target) Is Nothing) Then

If Len(Target.Value) Then

strPassword = InputBox(quot;Enter password to change/remove this name.quot;,
quot;Password required !quot;)

If strPassword = quot;spcemsquot; Then
Me.Unprotect quot;spcemsquot;
Target.Locked = False
Exit Sub
Else
Cancel = True
MsgBox quot;Password Incorrectquot;, , quot;Wrong passwordquot;
Exit Sub
End If
Else
Me.Unprotect quot;spcemsquot;
Target.Locked = False
End If

End IfEnd Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect quot;spcemsquot;

Target.Locked = True

Me.Protect quot;spcemsquot;

End SubThanks again,

Ralph Malph
quot;Jim Conequot; wrote:

gt; Ralph,
gt;
gt; The following code goes in the code module for the scheduling sheet.
gt; You access that by right-clicking the sheet tab and selecting quot;View Codequot;.
gt; The code prevents entry into any cell on the sheet. However, when any cell
gt; in Column B is double-clicked then, if the cell is empty, an entry can be made.
gt; You should change quot;Bquot; to the column with the students name.
gt; All three instances of quot;Passwordquot; should be replaced with your choice of password.
gt; The code is dynamic, so you will not be able to make changes to the sheet
gt; unless you disable the code.
gt; To protect the code: while in the code module, go to...
gt; Tools | VBAProjectProperties | Protection (tab) ...
gt; Enter a password and checkmark quot;Lock project for viewingquot;.
gt; Use a different password from that used in the code.
gt;
gt; Regards,
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt; '-------------
gt; Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
gt; If Not Application.Intersect(Me.Columns(quot;Bquot;), Target) Is Nothing Then
gt; If Len(Target.Value) Then
gt; Cancel = True
gt; Else
gt; Me.Unprotect quot;Passwordquot;
gt; Target.Locked = False
gt; End If
gt; End If
gt;
gt; End Sub
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; Me.Unprotect quot;Passwordquot;
gt; Target.Locked = True
gt; Me.Protect quot;Passwordquot;
gt; End Sub
gt; '------------------
gt;
gt;
gt; quot;Ralph Malphquot; gt;
gt; wrote in message...
gt; I have a spread sheet that someone else wrote for doing some simple
gt; scheduling. It has cells that the user fills in with their name to place
gt; themselves on the schedule. I need to fix it so that once the cell has a name
gt; in it, it can not be removed/changed without a password. I have done a bit of
gt; VB and VBA programming, but have no idea how to do it or gain access to the
gt; quot;Codequot; side of a cell in Excel to do a simple check for existing data and if
gt; not blank require a password to change. Once this security is implemented I
gt; would also need to know how to lockdown the spread sheet so that the only
gt; thing that a user can do without a password is put their name in a blank
gt; quot;time slotquot; cell. I don't want any one going in to design mode and finding to
gt; password etc. This is used by students and right now there are some who will
gt; erase someone’s name and then put theirs in place of it so that they can get
gt; some of the coveted spots on the calendar.
gt;
gt; Anyone with any suggestions for how to do this or a good quick free online
gt; tutorial that will show how to do it will be forever in my debt. Thanks for
gt; all your help.
gt;
gt; Ralph Malph
gt;
gt;

Jim,

Thanks again for all your help, I just noticed a flaw in my modified code.
The Inputbox function does not mask the password as it is put in. Any ideas
on how to do that.

Ralph Malphquot;Jim Conequot; wrote:

gt; Ralph,
gt;
gt; The following code goes in the code module for the scheduling sheet.
gt; You access that by right-clicking the sheet tab and selecting quot;View Codequot;.
gt; The code prevents entry into any cell on the sheet. However, when any cell
gt; in Column B is double-clicked then, if the cell is empty, an entry can be made.
gt; You should change quot;Bquot; to the column with the students name.
gt; All three instances of quot;Passwordquot; should be replaced with your choice of password.
gt; The code is dynamic, so you will not be able to make changes to the sheet
gt; unless you disable the code.
gt; To protect the code: while in the code module, go to...
gt; Tools | VBAProjectProperties | Protection (tab) ...
gt; Enter a password and checkmark quot;Lock project for viewingquot;.
gt; Use a different password from that used in the code.
gt;
gt; Regards,
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt; '-------------
gt; Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
gt; If Not Application.Intersect(Me.Columns(quot;Bquot;), Target) Is Nothing Then
gt; If Len(Target.Value) Then
gt; Cancel = True
gt; Else
gt; Me.Unprotect quot;Passwordquot;
gt; Target.Locked = False
gt; End If
gt; End If
gt;
gt; End Sub
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; Me.Unprotect quot;Passwordquot;
gt; Target.Locked = True
gt; Me.Protect quot;Passwordquot;
gt; End Sub
gt; '------------------
gt;
gt;
gt; quot;Ralph Malphquot; gt;
gt; wrote in message...
gt; I have a spread sheet that someone else wrote for doing some simple
gt; scheduling. It has cells that the user fills in with their name to place
gt; themselves on the schedule. I need to fix it so that once the cell has a name
gt; in it, it can not be removed/changed without a password. I have done a bit of
gt; VB and VBA programming, but have no idea how to do it or gain access to the
gt; quot;Codequot; side of a cell in Excel to do a simple check for existing data and if
gt; not blank require a password to change. Once this security is implemented I
gt; would also need to know how to lockdown the spread sheet so that the only
gt; thing that a user can do without a password is put their name in a blank
gt; quot;time slotquot; cell. I don't want any one going in to design mode and finding to
gt; password etc. This is used by students and right now there are some who will
gt; erase someone’s name and then put theirs in place of it so that they can get
gt; some of the coveted spots on the calendar.
gt;
gt; Anyone with any suggestions for how to do this or a good quick free online
gt; tutorial that will show how to do it will be forever in my debt. Thanks for
gt; all your help.
gt;
gt; Ralph Malph
gt;
gt;

You cannot mask the input to a InputBox. You'd have to create a
userform with a textbox whose PasswordChar property set to '*'.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.comquot;Ralph Malphquot; gt; wrote in message
...
gt; Jim,
gt;
gt; Thanks again for all your help, I just noticed a flaw in my
gt; modified code.
gt; The Inputbox function does not mask the password as it is put
gt; in. Any ideas
gt; on how to do that.
gt;
gt; Ralph Malph
gt;
gt;
gt; quot;Jim Conequot; wrote:
gt;
gt;gt; Ralph,
gt;gt;
gt;gt; The following code goes in the code module for the scheduling
gt;gt; sheet.
gt;gt; You access that by right-clicking the sheet tab and selecting
gt;gt; quot;View Codequot;.
gt;gt; The code prevents entry into any cell on the sheet. However,
gt;gt; when any cell
gt;gt; in Column B is double-clicked then, if the cell is empty, an
gt;gt; entry can be made.
gt;gt; You should change quot;Bquot; to the column with the students name.
gt;gt; All three instances of quot;Passwordquot; should be replaced with your
gt;gt; choice of password.
gt;gt; The code is dynamic, so you will not be able to make changes
gt;gt; to the sheet
gt;gt; unless you disable the code.
gt;gt; To protect the code: while in the code module, go to...
gt;gt; Tools | VBAProjectProperties | Protection (tab) ...
gt;gt; Enter a password and checkmark quot;Lock project for viewingquot;.
gt;gt; Use a different password from that used in the code.
gt;gt;
gt;gt; Regards,
gt;gt; Jim Cone
gt;gt; San Francisco, USA
gt;gt; www.realezsites.com/bus/primitivesoftware
gt;gt;
gt;gt; '-------------
gt;gt; Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
gt;gt; Cancel As Boolean)
gt;gt; If Not Application.Intersect(Me.Columns(quot;Bquot;), Target) Is
gt;gt; Nothing Then
gt;gt; If Len(Target.Value) Then
gt;gt; Cancel = True
gt;gt; Else
gt;gt; Me.Unprotect quot;Passwordquot;
gt;gt; Target.Locked = False
gt;gt; End If
gt;gt; End If
gt;gt;
gt;gt; End Sub
gt;gt;
gt;gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt;gt; Me.Unprotect quot;Passwordquot;
gt;gt; Target.Locked = True
gt;gt; Me.Protect quot;Passwordquot;
gt;gt; End Sub
gt;gt; '------------------
gt;gt;
gt;gt;
gt;gt; quot;Ralph Malphquot; gt;
gt;gt; wrote in message...
gt;gt; I have a spread sheet that someone else wrote for doing some
gt;gt; simple
gt;gt; scheduling. It has cells that the user fills in with their
gt;gt; name to place
gt;gt; themselves on the schedule. I need to fix it so that once the
gt;gt; cell has a name
gt;gt; in it, it can not be removed/changed without a password. I
gt;gt; have done a bit of
gt;gt; VB and VBA programming, but have no idea how to do it or gain
gt;gt; access to the
gt;gt; quot;Codequot; side of a cell in Excel to do a simple check for
gt;gt; existing data and if
gt;gt; not blank require a password to change. Once this security is
gt;gt; implemented I
gt;gt; would also need to know how to lockdown the spread sheet so
gt;gt; that the only
gt;gt; thing that a user can do without a password is put their name
gt;gt; in a blank
gt;gt; quot;time slotquot; cell. I don't want any one going in to design mode
gt;gt; and finding to
gt;gt; password etc. This is used by students and right now there are
gt;gt; some who will
gt;gt; erase someone's name and then put theirs in place of it so
gt;gt; that they can get
gt;gt; some of the coveted spots on the calendar.
gt;gt;
gt;gt; Anyone with any suggestions for how to do this or a good quick
gt;gt; free online
gt;gt; tutorial that will show how to do it will be forever in my
gt;gt; debt. Thanks for
gt;gt; all your help.
gt;gt;
gt;gt; Ralph Malph
gt;gt;
gt;gt;
Chip,

Thank you for your help. I had to do the same thing in VB and Access VB.
Leave it to Microsoft to do half a job on the most potentially useful
features of a product and to go overboard when not needed. It sure would be
nice if they would talk to some of the COMON users in the street before
finalizing a program....enough of my soap box. I was able to figure out how
to create a form and code around it for getting a password via a masked text
box. It seems to work fine. For those who may be interested I will show all
my code below. The last thing I need is a master button I can put on the
spread sheet that will unlock all the cells for easy editing with the
appropriate password being asked for only once. I can use the same password
form for it, but I am not sure of the code for totally unlocking the spread
sheet for easy editing instead of just a few select cells with a double click
as it does now. I want the current security setup to continue to work, just
be temporarily disabled so that the instructor can make mass edits as needed.
I will then want to have a lock down button which will put it back to the
default way of requiring a double click to insert a name if cell is blank and
a password for the cell if it is not in order to change it.

Any ideas on how to do this would be very appreciated.

Thanks again Chip and Jim for all your help !!!!!

Ralph Malph

Here is my code as it now stands:

***************************
The Sheet1 worksheet code:
***************************

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim strPassword As String

If (Not Application.Intersect(Me.Columns(quot;Cquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Gquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Kquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Oquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Squot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;Wquot;), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns(quot;AAquot;), Target) Is Nothing) Then

If Len(Target.Value) Then

FormPassword.ShowstrPassword = Password
Password = quot;quot;

If strPassword = quot;Thepasswordquot; Then
Me.Unprotect quot;Thepasswordquot;
Target.Locked = False
Exit Sub
Else
Cancel = True
MsgBox quot;Password Incorrectquot;, , quot;Wrong passwordquot;
Exit Sub
End If
Else
Me.Unprotect quot;Thepasswordquot;
Target.Locked = False
End If

End IfEnd Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect quot;Thepasswordquot;

Target.Locked = True

Me.Protect quot;Thepasswordquot;

End Sub

************************************************** *
The .BAS module called GlobalVar which is used
for the global variable called password:
************************************************** **

Global Password As String

************************************************** **
The code for the form which I named FormPassword:

The form has 1 lable, 1 text input box, and 2 command buttons.
They are called

lblPWPrompt
TxtPassword
CmdOK
CmdCancel

Respectively

The “cancel” property for the CmdCancel button has been set to true
So that the Esc key can be used to exit the password prompt form.
************************************************** ****

Private Sub CmdCancel_Click()

Password = quot;quot;

Unload Me

End Sub

Private Sub CmdOK_Click()

Password = TxtPassword

Unload MeEnd SubPrivate Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox quot;Please use the OK or Cancel buttons or press the Esc key!quot;
End If
End SubRalph Malphquot;Chip Pearsonquot; wrote:

gt; You cannot mask the input to a InputBox. You'd have to create a
gt; userform with a textbox whose PasswordChar property set to '*'.
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt; quot;Ralph Malphquot; gt; wrote in message
gt; ...
gt; gt; Jim,
gt; gt;
gt; gt; Thanks again for all your help, I just noticed a flaw in my
gt; gt; modified code.
gt; gt; The Inputbox function does not mask the password as it is put
gt; gt; in. Any ideas
gt; gt; on how to do that.
gt; gt;
gt; gt; Ralph Malph
gt; gt;
gt; gt;
gt; gt; quot;Jim Conequot; wrote:
gt; gt;
gt; gt;gt; Ralph,
gt; gt;gt;
gt; gt;gt; The following code goes in the code module for the scheduling
gt; gt;gt; sheet.
gt; gt;gt; You access that by right-clicking the sheet tab and selecting
gt; gt;gt; quot;View Codequot;.
gt; gt;gt; The code prevents entry into any cell on the sheet. However,
gt; gt;gt; when any cell
gt; gt;gt; in Column B is double-clicked then, if the cell is empty, an
gt; gt;gt; entry can be made.
gt; gt;gt; You should change quot;Bquot; to the column with the students name.
gt; gt;gt; All three instances of quot;Passwordquot; should be replaced with your
gt; gt;gt; choice of password.
gt; gt;gt; The code is dynamic, so you will not be able to make changes
gt; gt;gt; to the sheet
gt; gt;gt; unless you disable the code.
gt; gt;gt; To protect the code: while in the code module, go to...
gt; gt;gt; Tools | VBAProjectProperties | Protection (tab) ...
gt; gt;gt; Enter a password and checkmark quot;Lock project for viewingquot;.
gt; gt;gt; Use a different password from that used in the code.
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt; Jim Cone
gt; gt;gt; San Francisco, USA
gt; gt;gt; www.realezsites.com/bus/primitivesoftware
gt; gt;gt;
gt; gt;gt; '-------------
gt; gt;gt; Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
gt; gt;gt; Cancel As Boolean)
gt; gt;gt; If Not Application.Intersect(Me.Columns(quot;Bquot;), Target) Is
gt; gt;gt; Nothing Then
gt; gt;gt; If Len(Target.Value) Then
gt; gt;gt; Cancel = True
gt; gt;gt; Else
gt; gt;gt; Me.Unprotect quot;Passwordquot;
gt; gt;gt; Target.Locked = False
gt; gt;gt; End If
gt; gt;gt; End If
gt; gt;gt;
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt;gt; Me.Unprotect quot;Passwordquot;
gt; gt;gt; Target.Locked = True
gt; gt;gt; Me.Protect quot;Passwordquot;
gt; gt;gt; End Sub
gt; gt;gt; '------------------
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Ralph Malphquot; gt;
gt; gt;gt; wrote in message...
gt; gt;gt; I have a spread sheet that someone else wrote for doing some
gt; gt;gt; simple
gt; gt;gt; scheduling. It has cells that the user fills in with their
gt; gt;gt; name to place
gt; gt;gt; themselves on the schedule. I need to fix it so that once the
gt; gt;gt; cell has a name
gt; gt;gt; in it, it can not be removed/changed without a password. I
gt; gt;gt; have done a bit of
gt; gt;gt; VB and VBA programming, but have no idea how to do it or gain
gt; gt;gt; access to the
gt; gt;gt; quot;Codequot; side of a cell in Excel to do a simple check for
gt; gt;gt; existing data and if
gt; gt;gt; not blank require a password to change. Once this security is
gt; gt;gt; implemented I
gt; gt;gt; would also need to know how to lockdown the spread sheet so
gt; gt;gt; that the only
gt; gt;gt; thing that a user can do without a password is put their name
gt; gt;gt; in a blank
gt; gt;gt; quot;time slotquot; cell. I don't want any one going in to design mode
gt; gt;gt; and finding to
gt; gt;gt; password etc. This is used by students and right now there are
gt; gt;gt; some who will
gt; gt;gt; erase someone's name and then put theirs in place of it so
gt; gt;gt; that they can get
gt; gt;gt; some of the coveted spots on the calendar.
gt; gt;gt;
gt; gt;gt; Anyone with any suggestions for how to do this or a good quick
gt; gt;gt; free online
gt; gt;gt; tutorial that will show how to do it will be forever in my
gt; gt;gt; debt. Thanks for
gt; gt;gt; all your help.
gt; gt;gt;
gt; gt;gt; Ralph Malph
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;