Block referencing another cell

[Excel-Probie]

Another question as I am not up on macros. I want to block users from simply entering into a cell "C86" a formula pointing to cell "C84". They will need enter a formula as they are adding up numbers from the general ledger into cell "C86". I just want to ensure that they are looking at the general ledger and not taking a short cut.

THanks again

 

[mikerickson]

If you aren't up on macros, controlling user entry to that degree will be a headache.
You could put something like this in a Worksheet_Change event.

If Target.Address = "$C$86" Then
    If Target.HasFormula Then
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
End If

(BTW, if it works, whats bad about shortcuts)

 

[mvptomlinson]

Mike, I don't think your code checks to see if the formula entered into C86 references C84. The following should, though:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C86")) Is Nothing Then
    If InStr(1, Range("C86").Formula, "C84") > 0 Then
        MsgBox "You cannot reference cell C84 from this cell. Use the GL data, dummy!"
        Target.Clear
    End If
End If
End Sub

 

[Excel-Probie]

Thak you both for the help with this - I like the message that appears

 

[Excel-Probie]

ADVERTISEMENT

One last question on this issue, can I expand the macro to cover C86-K86

 

[Excel-Probie]

Does anyone know how I would change the macro to cover a range of cells C86 thru K86

Thanks

 

[Excel-Probie]

Below is the changes I made to the Macro and it is working. Is there a more efficient way to do this.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C86:K86")) Is Nothing Then
If InStr(1, Range("C86").Formula, "C84") > 0 Then
MsgBox "You cannot reference cell C84 from this cell. Please use the information in the General Ledger!"
Target.Clear
End If
If InStr(1, Range("H86").Formula, "H84") > 0 Then
MsgBox "You cannot reference cell H84 from this cell. Please use the information in the General Ledger!"
Target.Clear
End If
If InStr(1, Range("J86").Formula, "J84") > 0 Then
MsgBox "You cannot reference cell J84 from this cell. Please use the information in the General Ledger!"
Target.Clear
End If
If InStr(1, Range("K86").Formula, "K84") > 0 Then
MsgBox "You cannot reference cell K84 from this cell. Please use the information in the General Ledger!"
Target.Clear
End If
End If
End Sub


Thanks