use "validation" to auto-populate cell

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Help requested please....

I want to automatically populate a cell with a value if
another cell is equal to zero. However, I do not want the
cell that is being populated to contain a formula because
the user is also able to over-ride the default number. If
they do this then the formula will be overwritten.

For example, if were going to use an IF statement in cell
A2 (assuming this is the cell I want populated) I would
use: =if(A1=0,100%,0). A1 can be any value and as long as
it is 0 then I want A2 to automatically = 100%. If A1 is
<>0 then I want the user to enter a value in A2. The value
in A1 can change based on various options the user want to
see.

How can I use a macro or validation to achieve this?

thank you very much.
Robert
 
Hi Robert
one idea (based on the assumption, that A1 is changed manually) could
be to process the worksheet_change event:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = 0 Then
Range("A2").Value = 1
Else
Range("A2").Value = InputBox("Please enter your value for
A2")
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

If cell A1 is changed by a formula, you can process the
Worksheet_calculate event
Private Sub Worksheet_Calculate()
Static Value_A1
Dim rng As Range
Set rng = Range("A1")
If rng.Value = Value_A1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With rng
If .Value = 0 Then
Range("A2").Value = 1
Else
Range("A2").Value = InputBox("Please enter your value for
A2")
End If
Value_A1 = .Value
End With

CleanUp:
Application.EnableEvents = True
End Sub

Note: You should add some more error checking (e.g. testing, if user
input returns a value, etc.) to both procedures

HTH
Frank
 
Thanks Frank,

This works, but I need some help with modifications:

1) how can I format the value that is entered in the input
box for A2 as a percentage? Currently, cell A2 is
formatted as percent, but when I enter a value in the
input box the result is 100 times more than what I want.
For example, when I enter "10" in the input box the result
is 1000% when it should be 10%.

2) I have 5 columns in the same worksheet that I want to
run the same macro on. A1:E1 is entered by the user as
either 0 or something else. A2:E2 is the resulting value
promted by the input box. Each column is mutually
exclusive. I tried creating multiple worksheet change
events (by copying your marco five times) and changed the
cell references but when I do this I get a compile error:
Ambiguous name detected: worksheet_change

Thank you very much in advance for your help!
 
Hi Robert
1) how can I format the value that is entered in the input
box for A2 as a percentage? Currently, cell A2 is
formatted as percent, but when I enter a value in the
input box the result is 100 times more than what I want.
For example, when I enter "10" in the input box the result
is 1000% when it should be 10%.
Click to expand...

divide the input with 100: change the line in both sub procedures
Range("A2").Value = InputBox("Please enter your value for A2 ")
to
Range("A2").Value = InputBox("Please enter your value for A2 ") / 100

2) I have 5 columns in the same worksheet that I want to
run the same macro on. A1:E1 is entered by the user as
either 0 or something else. A2:E2 is the resulting value
promted by the input box. Each column is mutually
exclusive. I tried creating multiple worksheet change
events (by copying your marco five times) and changed the
cell references but when I do this I get a compile error:
Ambiguous name detected: worksheet_change
Click to expand...

you can only have one worksheet_change event. Change the procedure as
follows
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1:E1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = 0 Then
.Offset(1, 0).Value = 1
Else
.Offset(1, 0).Value = InputBox("Please enter your value for
A2 ") / 100
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank
 
Perfect!! That did the trick. Thank you very much
-----Original Message-----
Hi Robert

divide the input with 100: change the line in both sub procedures
Range("A2").Value = InputBox("Please enter your value for A2 ")
to
Range("A2").Value = InputBox("Please enter your value for A2 ") / 100



you can only have one worksheet_change event. Change the procedure as
follows
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1:E1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = 0 Then
.Offset(1, 0).Value = 1
Else
.Offset(1, 0).Value = InputBox("Please enter your value for
A2 ") / 100
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank

.
Click to expand...
 
Back
Top