Cell Security
Posted by Sandi on June 04, 2001 11:48 AM
I want to be able to lock the formula/content of
selected cells but not the entire worksheet, users
must be able to add rows if necessary. If I lock those
cells, then put the protection into effect for contents,
it will not allow any rows to be added to the sheet.
Then I tried a custom data validation where I specified
that only a specific formula was allowed in these cells.
All that this does is bring up a cute little message
when you click on it, but does not prevent you from
actually changing the information.
Any advice? (Using Excel 97.)
Posted by Tuc on June 04, 2001 12:20 PM
Sandi,
Look into the CellChange event for the workbook/worksheet. What you are going to have to do is allow the user to change the formula and then decide if they are supposed to. If not then you can call the application.Undo immediately and everything is restored. HOWEVER, you will need to code for the change event that the Undo will cause so that you do not get into an endless loop. I used a Public (global) boolean value to indicate my change state, carefully resetting it when I was done doing the undo.
Does that help?
Tuc
Posted by Sandi on June 04, 2001 2:19 PM
Well, I kind of understand and I kind of don't. I'm relatively new to the whole VBA/VBE aspect. Would it be possible for you to explain in a little more detail?
Posted by Tuc on June 06, 2001 7:46 PM
first declare a public variable in a standard BAS module.
Public gblnChangeInProgress as Boolean
put code into the Workbook_SheetChange event
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Check to see if Change is in progress...
If gblnChangeInProgress Then Exit Sub
' Other wise call the CellChange procedure
' passing the Target parameter.
' You should also check here to see that the
' SH object is a worksheet BEFORE you pass to
' the procedure, but I don't want to do all the
' work for you... ;-)
CellChange Target
End Sub
Sub CellChange (ByVal Target as Range)
' Here is a sample CellChange procedure...
' Code to taste..
' Put it in a Standard BAS module as well.
Static CurrentTarget as Range
Dim CheckRange as Range
' Check to see if a change is already in progress
If gblnChangeInProgress = True then
Exit Sub
Else
' Assign the flag
gblnChangeInProgress = True
set CurrentTarget = Target
End If
' Determine if the range argument is in the
' range you care about. In this case "A1:B100"
Set CheckRange = activeSheet.Range("A1:B100")
' The following will return True if the first
' range is contained in the second range,
' assuming that both are in the same workbook,
' on the same worksheet.
If Union (currentTarget, CheckRange) = CheckRange.Address Then
' We have a match
Application.Undo
gblnChangeInProgress = False
exit sub
End If
' We allow the change to happen
' -- Other Processing Here
' Reset the flag before we leave.
gblnChangeInProgress = False
End Sub
There are several ways to determine if the range is part of another range. I found a neat routine in John Walkenbach's book "Microsoft Excel Power Programming with VBA", ISBN 0-7645-3263-4.
I also HIGHLY recommend "Excel 2000 VBA Programmer's Reference", by John Green, ISBN 1-861002-5-48. I use that reference every day.
Is this enough to get you started?
Tuc
