Susan,

IV1 is available in my spread sheet. However, I am getting errors when
running this code. Does this code cover the entire spreadsheet or am I
suppose to input any ranges? The error I am getting is as follows:

Compile error:

Syntax Error.

It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As
Range)

And highlights with my cursor :
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,

Is this something I am doing incorrectly?

Please advise, Thanks,

Steve


"Susan" wrote:

ok. there's 2 subs, one for selection_change, which determines
whether or not there's a formula in the cell, and one for
worksheet_change, which activates the data validation if you change a
formula. i had to utilize a far-off cell (IV1) as a boolean value as
to whether or not there was a formula. hopefully that cell is
available in your spreadsheet.
===================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sReason As String
Dim sUser As String
Dim dDate As Date
Dim sStatus As String

If ActiveSheet.Range("iv1").Value = 1 Then

Application.EnableEvents = False
sReason = InputBox("Enter the reason for the override.")
dDate = Date
sUser = Environ("username")
sStatus = dDate & ", " & sReason
With Target.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = sUser
.ErrorTitle = ""
.InputMessage = sStatus
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
'do nothing
End If

Application.EnableEvents = True

End Sub
=======================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.HasFormula Then
Application.EnableEvents = False
ActiveSheet.Range("iv1").Value = 1
Else
ActiveSheet.Range("iv1").Value = 0
End If

Application.EnableEvents = True

End Sub
========================
good luck! save your worksheet before trying this.
susan



On Jul 10, 10:45 am, Susan wrote:
no, wait, that's not quite right. it doesn't account for the actual
change. i'm working on it..................
susan

On Jul 10, 10:36 am, Susan wrote:



ok. this checks the cell to see if there's a formula. if so, it adds
data validation using the username and the date and the input box
reason for override. hope it helps!!!
=======================
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim sReason As String
Dim sUser As String
Dim dDate As Date
Dim sStatus As String

If Target.HasFormula Then

Application.EnableEvents = False
' MsgBox "It's a formula"
sReason = InputBox("Enter the reason for the override.")
dDate = Date
sUser = Environ("username")
sStatus = dDate & ", " & sReason
With Target.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = sUser
.ErrorTitle = ""
.InputMessage = sStatus
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Else
'MsgBox "It's a number or blank"
End If

Application.EnableEvents = True
End Sub
=========================
:)
susan

On Jul 10, 9:30 am, Steve wrote:

This may be tricky, but I hope it can be done.

I'm looking to create a pop-up box that when a user overrides a formula by
entering a "hard" number a pop-up will appear. Once this pop-up appears I
would like it to ask for

User name:
Reason for Override:
Date:

I'm pretty sure this is all possible. The real trick then comes into play
next. What I want to do with this information is have it stored there so
that when i click on a cell that has been overriden it will pop-up with a box
that tells me who overrode it and all the other above information.

I am new at writing code, I have just started exploring this world earlier
this week and any help/suggestions will be greatly appreciated.

Thanks!

Steve- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -