PDA

View Full Version : Cannot get the protection code to work with the rest



ndendrinos
02-15-2011, 05:11 PM
Option Explicit
Public myForm As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:D1,A7")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Formula = myForm
MsgBox "Please do not change that cell." & vbCrLf & vbCrLf & _
"Thank you," & vbCrLf & "Management", vbInformation, "Cell contains Formula!"
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myForm = Target.Formula
End Sub

When using above in the same worksheet with other codes then just the protection of the cells work. Can something be done?

example of other code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A17:A35")) Is Nothing Then Target.Offset(0, 1).Select
'this multiplies
If Not Intersect(Target, Range("$A$17:$C$35")) Is Nothing Then
Dim isect As Range

If Target.Cells.Count > 1 Then Exit Sub

Set isect = Intersect(Target, Range("A:A, C:C"))
If Not isect Is Nothing Then
If Target <> "" Then
Range("E" & Target.Row) = Cells(Target.Row, "A") * Cells(Target.Row, "C")
Else
Range("E" & Target.Row) = ""
End If
End If
End If

'this is to turn positive to negative in 'payment received'
If Intersect(Target, Range("F40")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value > 0 Then Target = Target.Value * -1

End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("F2")) Is Nothing Then
MsgBox "Do not edit this cell. Use the 'NEW INVOICE' button instead "
End If

If Not Intersect(Target, Range("B8")) Is Nothing Then
UserForm2.Show
End If

If Not Intersect(Target, Range("B17:B35")) Is Nothing Then
UserForm1.Show
End If

End Sub

Simon Lloyd
02-15-2011, 05:32 PM
I don't understand what you're asking, but you can protect and hide all formula without the need for all that VBA, why bother telling them that the cell has a formula, it would be far better to simply deny access to the cell using Format Cells>Protection and check the locked and hidden checkboxes, before you do that you would need to unlock all cells first to do this press Ctrl+A then right click>format cells>protection and uncheck both boxes and click ok, then hold down ctrl and click or highlight each cell that contains a formula then right click one of them>format cells>protection and checkboth boxes click ok, now goto tools>protection>protect sheet>enter a password and make sure that you UN check "select locked cells" complete the process and they wont be able to access those cells.

The trouble with VBA is if they have security set to medium or high they can decide not to run VBA and then access what they want, if the VBA fails and is in break mode then they can access what they want, using the worksheet method they can't!

ndendrinos
02-15-2011, 05:44 PM
Hello Simon don't you ever sleep ? :)
I was just reading your code in KBase ( the one with the useform )
Up to now I only protect cells that have formulas with Data/Validation/Custom/=FALSE
and since over the past two days you've solved all the problems I published here and I cannot think of any other and since my wife is presently watching a cooking show I thought I would just browse the KBase and came about this code and tried to see if it would work with the rest of the sheet codes I already have.
No harm done I'll stay with my initial way to protect the cell against accidental data imput. I know you can still delete or over paste on a cell protected this way.
Maybe I should take Max (our dog) out for a walk ...

Simon Lloyd
02-15-2011, 07:18 PM
Hello Simon don't you ever sleep ? :)
........Maybe I should take Max (our dog) out for a walk ...Im working nights at the moment :whip .

Do you still have an issue that needs attention?