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

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

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 -

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 -

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 -

i forgot to mention, this is a worksheet code - it won't work in a
regular module. right click on your worksheet tab & choose "View
Code". the correct area of VB editor will open up. put the code
there & try that.
as for the other error, it may be a line wrapping error from your
newsreader. try removing the spaces and putting all of that line on
one line.
it will cover the entire worksheet, no matter where you click in the
spreadsheet. you don't have to enter any ranges.
susan



On Jul 10, 11:37*am, Steve wrote:
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 -- Hide quoted text -

- Show quoted text -