i see, you set it up outside of the worksheet events.
cool.
thanks!
susan


On Jul 15, 2:27*pm, TomPl wrote:
I set up this Module Level Variable and it seems to work.

Option Explicit

Dim lngStatus As Long

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sReason1 As String
Dim sReason2 As String
Dim sUser As String
Dim dDate As Date
Dim sStatus As String

If lngStatus = 1 Then *'ActiveSheet.Range("i1").Value = 1 Then

* *Application.EnableEvents = False
* *sReason1 = InputBox("Enter Name (First, Last):")
* *sReason2 = InputBox("Enter the reason for the override:")
* *dDate = Date
* *sUser = Environ("username")
* *sStatus = "Date:" & dDate & " " & "Name:" & sReason1 & " " & "Reason:" &
sReason2
* *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
* If Target.HasFormula Then
* * With Target.Validation
* * * .Delete
* * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
* * * :=xlBetween
* * * .IgnoreBlank = True
* * * .InCellDropdown = True
* * * .InputTitle = ""
* * * .InputMessage = ""
* * * .ShowInput = True
* * * .ShowError = True
* * End With
* * With Target.Interior
* * * .ColorIndex = 44
* * * .Pattern = xlSolid
* * End With
* End If
End If

Application.EnableEvents = True
Debug.Print lngStatus
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.HasFormula Then
* *Application.EnableEvents = False
* *'ActiveSheet.Range("i1").Value = 1
* *lngStatus = 1
Else
* *'ActiveSheet.Range("i1").Value = 0
* *lngStatus = 0
End If

Application.EnableEvents = True
Debug.Print lngStatus

End Sub

Tom



"Susan" wrote:
TomPI - i agree with you (i wrote the original coding). *unfortunately
whenever i tried to set a boolean value with the worksheet selection_
change, it wouldn't carry over to the worksheet_change sub. *i tried
a public variable but i couldn't make it stick, so to speak. *in a
userform i would have made an invisible checkbox or something to
use as a boolean value, so that's how i decided to use a worksheet
cell. *how would you have handled a variable that would carry over
between
the subs?
thanks for any ideas
:)
susan

On Jul 14, 3:04 pm, TomPl wrote:

PS *I don't like the use of cell iv1 as a variable. *I would be inclined to
use a public VBA variable in its place.- Hide quoted text -

- Show quoted text -