Very Novice Excel user with security question

[Ralph Malph]

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
"Code" 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
"time slot" 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

[Jim Cone]

Ralph,

The following code goes in the code module for the scheduling sheet.
You access that by right-clicking the sheet tab and selecting "View Code".
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 "B" to the column with the students name.
All three instances of "Password" 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 "Lock project for viewing".
Use a different password from that used in the code.

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

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

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Unprotect "Password"
Target.Locked = True
Me.Protect "Password"
End Sub
'------------------


"Ralph Malph" <ralph.malph@happydays.com>
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
"Code" 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
"time slot" 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

[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("C"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("G"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("K"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("O"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("S"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("W"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("AA"), Target) Is Nothing) Then

If Len(Target.Value) Then

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

If strPassword = "spcems" Then
Me.Unprotect "spcems"
Target.Locked = False
Exit Sub
Else
Cancel = True
MsgBox "Password Incorrect", , "Wrong password"
Exit Sub
End If
Else
Me.Unprotect "spcems"
Target.Locked = False
End If

End If


End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect "spcems"

Target.Locked = True

Me.Protect "spcems"

End Sub


Thanks again,

Ralph Malph



"Jim Cone" wrote:

> Ralph,
>
> The following code goes in the code module for the scheduling sheet.
> You access that by right-clicking the sheet tab and selecting "View Code".
> 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 "B" to the column with the students name.
> All three instances of "Password" 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 "Lock project for viewing".
> Use a different password from that used in the code.
>
> Regards,
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
> '-------------
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
> If Not Application.Intersect(Me.Columns("B"), Target) Is Nothing Then
> If Len(Target.Value) Then
> Cancel = True
> Else
> Me.Unprotect "Password"
> Target.Locked = False
> End If
> End If
>
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Me.Unprotect "Password"
> Target.Locked = True
> Me.Protect "Password"
> End Sub
> '------------------
>
>
> "Ralph Malph" <ralph.malph@happydays.com>
> 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
> "Code" 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
> "time slot" 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
>
>

[Ralph Malph]

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 Malph


"Jim Cone" wrote:

> Ralph,
>
> The following code goes in the code module for the scheduling sheet.
> You access that by right-clicking the sheet tab and selecting "View Code".
> 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 "B" to the column with the students name.
> All three instances of "Password" 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 "Lock project for viewing".
> Use a different password from that used in the code.
>
> Regards,
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
> '-------------
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
> If Not Application.Intersect(Me.Columns("B"), Target) Is Nothing Then
> If Len(Target.Value) Then
> Cancel = True
> Else
> Me.Unprotect "Password"
> Target.Locked = False
> End If
> End If
>
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Me.Unprotect "Password"
> Target.Locked = True
> Me.Protect "Password"
> End Sub
> '------------------
>
>
> "Ralph Malph" <ralph.malph@happydays.com>
> 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
> "Code" 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
> "time slot" 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
>
>

[Chip Pearson]

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.com


"Ralph Malph" <ralph.malph@happydays.com> wrote in message
news:F3384729-BF16-4DE4-BB92-0A903E7C8AE6@microsoft.com...
> 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 Malph
>
>
> "Jim Cone" wrote:
>
>> Ralph,
>>
>> The following code goes in the code module for the scheduling
>> sheet.
>> You access that by right-clicking the sheet tab and selecting
>> "View Code".
>> 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 "B" to the column with the students name.
>> All three instances of "Password" 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 "Lock project for viewing".
>> Use a different password from that used in the code.
>>
>> Regards,
>> Jim Cone
>> San Francisco, USA
>> http://www.realezsites.com/bus/primitivesoftware
>>
>> '-------------
>> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
>> Cancel As Boolean)
>> If Not Application.Intersect(Me.Columns("B"), Target) Is
>> Nothing Then
>> If Len(Target.Value) Then
>> Cancel = True
>> Else
>> Me.Unprotect "Password"
>> Target.Locked = False
>> End If
>> End If
>>
>> End Sub
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> Me.Unprotect "Password"
>> Target.Locked = True
>> Me.Protect "Password"
>> End Sub
>> '------------------
>>
>>
>> "Ralph Malph" <ralph.malph@happydays.com>
>> 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
>> "Code" 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
>> "time slot" 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
>>
>>

[Ralph Malph]

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("C"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("G"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("K"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("O"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("S"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("W"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("AA"), Target) Is Nothing) Then

If Len(Target.Value) Then

FormPassword.Show


strPassword = Password
Password = ""

If strPassword = "Thepassword" Then
Me.Unprotect "Thepassword"
Target.Locked = False
Exit Sub
Else
Cancel = True
MsgBox "Password Incorrect", , "Wrong password"
Exit Sub
End If
Else
Me.Unprotect "Thepassword"
Target.Locked = False
End If

End If


End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect "Thepassword"

Target.Locked = True

Me.Protect "Thepassword"

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 = ""

Unload Me

End Sub

Private Sub CmdOK_Click()

Password = TxtPassword

Unload Me


End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the OK or Cancel buttons or press the Esc key!"
End If
End Sub





Ralph Malph


"Chip Pearson" wrote:

> 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.com
>
>
> "Ralph Malph" <ralph.malph@happydays.com> wrote in message
> news:F3384729-BF16-4DE4-BB92-0A903E7C8AE6@microsoft.com...
> > 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 Malph
> >
> >
> > "Jim Cone" wrote:
> >
> >> Ralph,
> >>
> >> The following code goes in the code module for the scheduling
> >> sheet.
> >> You access that by right-clicking the sheet tab and selecting
> >> "View Code".
> >> 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 "B" to the column with the students name.
> >> All three instances of "Password" 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 "Lock project for viewing".
> >> Use a different password from that used in the code.
> >>
> >> Regards,
> >> Jim Cone
> >> San Francisco, USA
> >> http://www.realezsites.com/bus/primitivesoftware
> >>
> >> '-------------
> >> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
> >> Cancel As Boolean)
> >> If Not Application.Intersect(Me.Columns("B"), Target) Is
> >> Nothing Then
> >> If Len(Target.Value) Then
> >> Cancel = True
> >> Else
> >> Me.Unprotect "Password"
> >> Target.Locked = False
> >> End If
> >> End If
> >>
> >> End Sub
> >>
> >> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> Me.Unprotect "Password"
> >> Target.Locked = True
> >> Me.Protect "Password"
> >> End Sub
> >> '------------------
> >>
> >>
> >> "Ralph Malph" <ralph.malph@happydays.com>
> >> 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
> >> "Code" 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
> >> "time slot" 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
> >>
> >>
>
>
>