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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

That code sets the color to "No Color" for me. Make sure it is in the
correct place in the routine.

Tom

"Steve" wrote:

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

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

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.

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

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 -

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

Susan/Tom,

Here's the final version of the code. Mostly a combination between what
both of you gave me. Thank you both for all your help. It works great!


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
With Target.Interior
.ColorIndex = 44
.Pattern = xlSolid
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 = xlNone
.Pattern = xlSolid
End With
End If

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




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

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

Hey guys!

The code is working great. I was wondering however, if it is possible to do
one last adjustment. This one is going to be pretty tricky i think.

Currently if a formula is entered into the cell or copy and pasted into the
cell, the orange highlight goes away and so does all the stored information
about how made the overide. Is it possible that if a formula is autofiltered
into a cell that it does the above too? If not, it's no big deal of having
to copy and paste into the cell, but I was just curious.

Thanks so much!

Steve

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

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

i don't exactly understand the question, and i don't know if you can
autofilter based on comments............... maybe you need to start a
new thread asking this new question.
:)
susan, bowing out gracefully


On Jul 17, 10:40*am, Steve wrote:
Hey guys!

The code is working great. *I was wondering however, if it is possible to do
one last adjustment. *This one is going to be pretty tricky i think.

Currently if a formula is entered into the cell or copy and pasted into the
cell, the orange highlight goes away and so does all the stored information
about how made the overide. *Is it possible that if a formula is autofiltered
into a cell that it does the above too? *If not, it's no big deal of having
to copy and paste into the cell, but I was just curious.

Thanks so much!

Steve