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