***Challenging Pop-up excel box dealing with user information***

In the "Else" part of you if statement you have "'do nothing".
Try replacing it with this:
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
End If

I think that will work.

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.



"Steve" wrote:

> I'm not sure if this is possible, but I hope it is. I'm fairly new to
> working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
>
> I have this code which is all most 100% what I'm looking for. only problem
> is I need what I hope will be a small adjustment/addition to it. Not sure
> where it needs to go though. Please help.
>
> Currently I have a code that when somebody goes in and overwrites a cell
> with a formula in it, a box pops up asking for their name and reason for
> overide. It then stores this information in a vaildation box.
>
> What I still need:
>
> What I need is a way to not have the vaildation box show up if a formula is
> put back in the cell. The reason is because the same spreadsheet is used
> each year. So every year there will be different cells that will be
> overidden. Currently when you go to put a formula back in the cell it keeps
> the validation box information from the change.
>
> I'm thinking there is 2 ways this can be done, but I really don't know.
>
> 1. make an adjustment to the code where if a formula is put into the cell
> the validation box disappears for that cell. (preferred way)
>
> 2. before putting a formula in the cell you must clear the cell of all
> numbers and formulas - which will then make the validation box disappear.
>
> Hope these ideas help. Your advice and help is greatly appreciated.
>
> Here is the current code I have.
>
> Option Explicit
>
>
> 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 ActiveSheet.Range("iv1").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
> '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

Tom,

Thanks a lot! Formula works PERFECT!!

Steve


"TomPl" wrote:

> In the "Else" part of you if statement you have "'do nothing".
> Try replacing it with this:
> 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
> End If
>
> I think that will work.
>
> 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.
>
>
>
> "Steve" wrote:
>
> > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> >
> > I have this code which is all most 100% what I'm looking for. only problem
> > is I need what I hope will be a small adjustment/addition to it. Not sure
> > where it needs to go though. Please help.
> >
> > Currently I have a code that when somebody goes in and overwrites a cell
> > with a formula in it, a box pops up asking for their name and reason for
> > overide. It then stores this information in a vaildation box.
> >
> > What I still need:
> >
> > What I need is a way to not have the vaildation box show up if a formula is
> > put back in the cell. The reason is because the same spreadsheet is used
> > each year. So every year there will be different cells that will be
> > overidden. Currently when you go to put a formula back in the cell it keeps
> > the validation box information from the change.
> >
> > I'm thinking there is 2 ways this can be done, but I really don't know.
> >
> > 1. make an adjustment to the code where if a formula is put into the cell
> > the validation box disappears for that cell. (preferred way)
> >
> > 2. before putting a formula in the cell you must clear the cell of all
> > numbers and formulas - which will then make the validation box disappear.
> >
> > Hope these ideas help. Your advice and help is greatly appreciated.
> >
> > Here is the current code I have.
> >
> > Option Explicit
> >
> >
> > 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 ActiveSheet.Range("iv1").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
> > '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

Tom,

One follow up question. Is it possible to automatically Fill Color the cell
that is changed from a formula to a number?

Steve

"TomPl" wrote:

> In the "Else" part of you if statement you have "'do nothing".
> Try replacing it with this:
> 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
> End If
>
> I think that will work.
>
> 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.
>
>
>
> "Steve" wrote:
>
> > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> >
> > I have this code which is all most 100% what I'm looking for. only problem
> > is I need what I hope will be a small adjustment/addition to it. Not sure
> > where it needs to go though. Please help.
> >
> > Currently I have a code that when somebody goes in and overwrites a cell
> > with a formula in it, a box pops up asking for their name and reason for
> > overide. It then stores this information in a vaildation box.
> >
> > What I still need:
> >
> > What I need is a way to not have the vaildation box show up if a formula is
> > put back in the cell. The reason is because the same spreadsheet is used
> > each year. So every year there will be different cells that will be
> > overidden. Currently when you go to put a formula back in the cell it keeps
> > the validation box information from the change.
> >
> > I'm thinking there is 2 ways this can be done, but I really don't know.
> >
> > 1. make an adjustment to the code where if a formula is put into the cell
> > the validation box disappears for that cell. (preferred way)
> >
> > 2. before putting a formula in the cell you must clear the cell of all
> > numbers and formulas - which will then make the validation box disappear.
> >
> > Hope these ideas help. Your advice and help is greatly appreciated.
> >
> > Here is the current code I have.
> >
> > Option Explicit
> >
> >
> > 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 ActiveSheet.Range("iv1").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
> > '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

Just thought of another possibility (probably a better way to do it) Is it
possible that if the cell includes a validation box that it is automatically
fill colored?

Steve

"TomPl" wrote:

> In the "Else" part of you if statement you have "'do nothing".
> Try replacing it with this:
> 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
> End If
>
> I think that will work.
>
> 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.
>
>
>
> "Steve" wrote:
>
> > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> >
> > I have this code which is all most 100% what I'm looking for. only problem
> > is I need what I hope will be a small adjustment/addition to it. Not sure
> > where it needs to go though. Please help.
> >
> > Currently I have a code that when somebody goes in and overwrites a cell
> > with a formula in it, a box pops up asking for their name and reason for
> > overide. It then stores this information in a vaildation box.
> >
> > What I still need:
> >
> > What I need is a way to not have the vaildation box show up if a formula is
> > put back in the cell. The reason is because the same spreadsheet is used
> > each year. So every year there will be different cells that will be
> > overidden. Currently when you go to put a formula back in the cell it keeps
> > the validation box information from the change.
> >
> > I'm thinking there is 2 ways this can be done, but I really don't know.
> >
> > 1. make an adjustment to the code where if a formula is put into the cell
> > the validation box disappears for that cell. (preferred way)
> >
> > 2. before putting a formula in the cell you must clear the cell of all
> > numbers and formulas - which will then make the validation box disappear.
> >
> > Hope these ideas help. Your advice and help is greatly appreciated.
> >
> > Here is the current code I have.
> >
> > Option Explicit
> >
> >
> > 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 ActiveSheet.Range("iv1").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
> > '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

This should do it:

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

"Steve" wrote:

> Tom,
>
> One follow up question. Is it possible to automatically Fill Color the cell
> that is changed from a formula to a number?
>
> Steve
>
> "TomPl" wrote:
>
> > In the "Else" part of you if statement you have "'do nothing".
> > Try replacing it with this:
> > 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
> > End If
> >
> > I think that will work.
> >
> > 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.
> >
> >
> >
> > "Steve" wrote:
> >
> > > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> > >
> > > I have this code which is all most 100% what I'm looking for. only problem
> > > is I need what I hope will be a small adjustment/addition to it. Not sure
> > > where it needs to go though. Please help.
> > >
> > > Currently I have a code that when somebody goes in and overwrites a cell
> > > with a formula in it, a box pops up asking for their name and reason for
> > > overide. It then stores this information in a vaildation box.
> > >
> > > What I still need:
> > >
> > > What I need is a way to not have the vaildation box show up if a formula is
> > > put back in the cell. The reason is because the same spreadsheet is used
> > > each year. So every year there will be different cells that will be
> > > overidden. Currently when you go to put a formula back in the cell it keeps
> > > the validation box information from the change.
> > >
> > > I'm thinking there is 2 ways this can be done, but I really don't know.
> > >
> > > 1. make an adjustment to the code where if a formula is put into the cell
> > > the validation box disappears for that cell. (preferred way)
> > >
> > > 2. before putting a formula in the cell you must clear the cell of all
> > > numbers and formulas - which will then make the validation box disappear.
> > >
> > > Hope these ideas help. Your advice and help is greatly appreciated.
> > >
> > > Here is the current code I have.
> > >
> > > Option Explicit
> > >
> > >
> > > 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 ActiveSheet.Range("iv1").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
> > > '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

You could use language similar to my last response to set the color when you
set the validation.

Tom

"Steve" wrote:

> Just thought of another possibility (probably a better way to do it) Is it
> possible that if the cell includes a validation box that it is automatically
> fill colored?
>
> Steve
>
> "TomPl" wrote:
>
> > In the "Else" part of you if statement you have "'do nothing".
> > Try replacing it with this:
> > 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
> > End If
> >
> > I think that will work.
> >
> > 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.
> >
> >
> >
> > "Steve" wrote:
> >
> > > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> > >
> > > I have this code which is all most 100% what I'm looking for. only problem
> > > is I need what I hope will be a small adjustment/addition to it. Not sure
> > > where it needs to go though. Please help.
> > >
> > > Currently I have a code that when somebody goes in and overwrites a cell
> > > with a formula in it, a box pops up asking for their name and reason for
> > > overide. It then stores this information in a vaildation box.
> > >
> > > What I still need:
> > >
> > > What I need is a way to not have the vaildation box show up if a formula is
> > > put back in the cell. The reason is because the same spreadsheet is used
> > > each year. So every year there will be different cells that will be
> > > overidden. Currently when you go to put a formula back in the cell it keeps
> > > the validation box information from the change.
> > >
> > > I'm thinking there is 2 ways this can be done, but I really don't know.
> > >
> > > 1. make an adjustment to the code where if a formula is put into the cell
> > > the validation box disappears for that cell. (preferred way)
> > >
> > > 2. before putting a formula in the cell you must clear the cell of all
> > > numbers and formulas - which will then make the validation box disappear.
> > >
> > > Hope these ideas help. Your advice and help is greatly appreciated.
> > >
> > > Here is the current code I have.
> > >
> > > Option Explicit
> > >
> > >
> > > 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 ActiveSheet.Range("iv1").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
> > > '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

Hey Tom,

The formula you gave me is working great! One question. I'm making a small
alteration and I was wondering what is the color index # for "no color"? I
was playing around with it and 00 is white, but i couldn't find anything that
is no color (google searches weren't helping much either). Let me know if
you have any ideas.

Thanks Tom!

Steve

"TomPl" wrote:

> You could use language similar to my last response to set the color when you
> set the validation.
>
> Tom
>
> "Steve" wrote:
>
> > Just thought of another possibility (probably a better way to do it) Is it
> > possible that if the cell includes a validation box that it is automatically
> > fill colored?
> >
> > Steve
> >
> > "TomPl" wrote:
> >
> > > In the "Else" part of you if statement you have "'do nothing".
> > > Try replacing it with this:
> > > 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
> > > End If
> > >
> > > I think that will work.
> > >
> > > 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.
> > >
> > >
> > >
> > > "Steve" wrote:
> > >
> > > > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > > > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> > > >
> > > > I have this code which is all most 100% what I'm looking for. only problem
> > > > is I need what I hope will be a small adjustment/addition to it. Not sure
> > > > where it needs to go though. Please help.
> > > >
> > > > Currently I have a code that when somebody goes in and overwrites a cell
> > > > with a formula in it, a box pops up asking for their name and reason for
> > > > overide. It then stores this information in a vaildation box.
> > > >
> > > > What I still need:
> > > >
> > > > What I need is a way to not have the vaildation box show up if a formula is
> > > > put back in the cell. The reason is because the same spreadsheet is used
> > > > each year. So every year there will be different cells that will be
> > > > overidden. Currently when you go to put a formula back in the cell it keeps
> > > > the validation box information from the change.
> > > >
> > > > I'm thinking there is 2 ways this can be done, but I really don't know.
> > > >
> > > > 1. make an adjustment to the code where if a formula is put into the cell
> > > > the validation box disappears for that cell. (preferred way)
> > > >
> > > > 2. before putting a formula in the cell you must clear the cell of all
> > > > numbers and formulas - which will then make the validation box disappear.
> > > >
> > > > Hope these ideas help. Your advice and help is greatly appreciated.
> > > >
> > > > Here is the current code I have.
> > > >
> > > > Option Explicit
> > > >
> > > >
> > > > 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 ActiveSheet.Range("iv1").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
> > > > '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

Try:

Target.Interior.ColorIndex = xlNone

Tom

"Steve" wrote:

> Hey Tom,
>
> The formula you gave me is working great! One question. I'm making a small
> alteration and I was wondering what is the color index # for "no color"? I
> was playing around with it and 00 is white, but i couldn't find anything that
> is no color (google searches weren't helping much either). Let me know if
> you have any ideas.
>
> Thanks Tom!
>
> Steve
>
> "TomPl" wrote:
>
> > You could use language similar to my last response to set the color when you
> > set the validation.
> >
> > Tom
> >
> > "Steve" wrote:
> >
> > > Just thought of another possibility (probably a better way to do it) Is it
> > > possible that if the cell includes a validation box that it is automatically
> > > fill colored?
> > >
> > > Steve
> > >
> > > "TomPl" wrote:
> > >
> > > > In the "Else" part of you if statement you have "'do nothing".
> > > > Try replacing it with this:
> > > > 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
> > > > End If
> > > >
> > > > I think that will work.
> > > >
> > > > 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.
> > > >
> > > >
> > > >
> > > > "Steve" wrote:
> > > >
> > > > > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > > > > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> > > > >
> > > > > I have this code which is all most 100% what I'm looking for. only problem
> > > > > is I need what I hope will be a small adjustment/addition to it. Not sure
> > > > > where it needs to go though. Please help.
> > > > >
> > > > > Currently I have a code that when somebody goes in and overwrites a cell
> > > > > with a formula in it, a box pops up asking for their name and reason for
> > > > > overide. It then stores this information in a vaildation box.
> > > > >
> > > > > What I still need:
> > > > >
> > > > > What I need is a way to not have the vaildation box show up if a formula is
> > > > > put back in the cell. The reason is because the same spreadsheet is used
> > > > > each year. So every year there will be different cells that will be
> > > > > overidden. Currently when you go to put a formula back in the cell it keeps
> > > > > the validation box information from the change.
> > > > >
> > > > > I'm thinking there is 2 ways this can be done, but I really don't know.
> > > > >
> > > > > 1. make an adjustment to the code where if a formula is put into the cell
> > > > > the validation box disappears for that cell. (preferred way)
> > > > >
> > > > > 2. before putting a formula in the cell you must clear the cell of all
> > > > > numbers and formulas - which will then make the validation box disappear.
> > > > >
> > > > > Hope these ideas help. Your advice and help is greatly appreciated.
> > > > >
> > > > > Here is the current code I have.
> > > > >
> > > > > Option Explicit
> > > > >
> > > > >
> > > > > 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 ActiveSheet.Range("iv1").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
> > > > > '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

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.
>

Tom,

Still shows as if white when using xlNone.

Not a big deal though, I can live with that. You have helped me out
greatly! Thanks again for your help!

Steve

"TomPl" wrote:

> Try:
>
> Target.Interior.ColorIndex = xlNone
>
> Tom
>
> "Steve" wrote:
>
> > Hey Tom,
> >
> > The formula you gave me is working great! One question. I'm making a small
> > alteration and I was wondering what is the color index # for "no color"? I
> > was playing around with it and 00 is white, but i couldn't find anything that
> > is no color (google searches weren't helping much either). Let me know if
> > you have any ideas.
> >
> > Thanks Tom!
> >
> > Steve
> >
> > "TomPl" wrote:
> >
> > > You could use language similar to my last response to set the color when you
> > > set the validation.
> > >
> > > Tom
> > >
> > > "Steve" wrote:
> > >
> > > > Just thought of another possibility (probably a better way to do it) Is it
> > > > possible that if the cell includes a validation box that it is automatically
> > > > fill colored?
> > > >
> > > > Steve
> > > >
> > > > "TomPl" wrote:
> > > >
> > > > > In the "Else" part of you if statement you have "'do nothing".
> > > > > Try replacing it with this:
> > > > > 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
> > > > > End If
> > > > >
> > > > > I think that will work.
> > > > >
> > > > > 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.
> > > > >
> > > > >
> > > > >
> > > > > "Steve" wrote:
> > > > >
> > > > > > I'm not sure if this is possible, but I hope it is. I'm fairly new to
> > > > > > working with VBA coding, but i'm sure someone is up to the challenge. Thanks!
> > > > > >
> > > > > > I have this code which is all most 100% what I'm looking for. only problem
> > > > > > is I need what I hope will be a small adjustment/addition to it. Not sure
> > > > > > where it needs to go though. Please help.
> > > > > >
> > > > > > Currently I have a code that when somebody goes in and overwrites a cell
> > > > > > with a formula in it, a box pops up asking for their name and reason for
> > > > > > overide. It then stores this information in a vaildation box.
> > > > > >
> > > > > > What I still need:
> > > > > >
> > > > > > What I need is a way to not have the vaildation box show up if a formula is
> > > > > > put back in the cell. The reason is because the same spreadsheet is used
> > > > > > each year. So every year there will be different cells that will be
> > > > > > overidden. Currently when you go to put a formula back in the cell it keeps
> > > > > > the validation box information from the change.
> > > > > >
> > > > > > I'm thinking there is 2 ways this can be done, but I really don't know.
> > > > > >
> > > > > > 1. make an adjustment to the code where if a formula is put into the cell
> > > > > > the validation box disappears for that cell. (preferred way)
> > > > > >
> > > > > > 2. before putting a formula in the cell you must clear the cell of all
> > > > > > numbers and formulas - which will then make the validation box disappear.
> > > > > >
> > > > > > Hope these ideas help. Your advice and help is greatly appreciated.
> > > > > >
> > > > > > Here is the current code I have.
> > > > > >
> > > > > > Option Explicit
> > > > > >
> > > > > >
> > > > > > 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 ActiveSheet.Range("iv1").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
> > > > > > '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