Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
grecoaica
 | Femeie
22 ani
Dambovita
cauta Barbat
22 - 45 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
I want to protect a few columns of formulas from accidental modification, but without protecting the sheet with a password (or blank protection) - I have my reasons for not having protected sheets.
Simply using data validation with Custom and my formula =IF(Q3>R3;Q3-R3;R3-Q3) didn't prevent users from accidentally entering text.
Solution
Provided that the columns already contain the formulas I need, the solution is to use Data Validation with Custom and the formula ="".
The formula in Data Validation won't affect in any way the formula in the cell, but if you accidentally enter some numbers in the cell without an = sign in front of them, it will give you an error.
Downside
The only downside to this is that users can modify the formula, but this would be an intended modification and not something accidental and I'm counting on the fact that users have no reason to edit formulas on purpose.
Source:
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Additionally, here's a VBA code that will protect the column by preventing users from selecting it.
This can become useless if the user saves the file as macro free.
It can also be bad for a number of reasons, for example if the file needs to be cleared for some reason, or if the calculation in a cell in the column returns an error and must be entered manually.
But here goes:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 10 Then
Beep
Cells(0, Target.Column).Offset(0, 1).Select
End If
End Sub
|
Source:
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
To Prevent specific cell contents from being deleted in Excel
In the code, A1:E17 is the range which the cell contents you will prevent from being deleted. You can change the range based on your needs.
From now on, when you trying to delete cell contents from range A1:E17, you will get an Error dialog box with an OK button and a message.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:E7")) Is Nothing Then Exit Sub
On Error GoTo ExitPoint
Application.EnableEvents = False
If Not IsDate(Target(1)) Then
Application.Undo
MsgBox " You can't delete cell contents from this range " _
, vbCritical, "Error"
End If
ExitPoint:
Application.EnableEvents = True
End Sub
|
Source:
or another more complex solution for this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim b As Boolean
On Error GoTo Terminate
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each c In Target.Cells
If Not Intersect(c, Range("A:B")) Is Nothing And c.Value = "" Then
b = True
GoTo UndoChange
End If
Next c
UndoChange:
If b Then Application.Undo
Terminate:
If Err Then
Debug.Print "Error", Err.Number, Err.Description
Err.Clear
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
|
Source:
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|