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

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 <To...@discussions.microsoft.com> 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.
>