Solved: Conditional data validation?

[jcfields]

Hey all,

I've been doing this ( ) all day now, and it's a good thing there aren't any sharp objects in grasping range, or I likely would've stabbed myself in the eye by now .

Anyway, how to frame the problem...? I'll make up an example here:

Let's say cell A1 has a drop down list where you can choose from "True" or "False". Now, let's say cell A2 = M5 + M6 (or whatever). Now, here's the problem, let's say I want to set it up so that if A1 = "True", then A3 = A2. However, if A1 = "False", I want A3 to be left blank so that the user can enter in any value they choose (e.g. there can't be any formulas or conditional statements actually "in" A3).

This sounds really simple, and maybe it's because it's the holidays, but I cannot for the life of me figure out how to set this up. Help!?

Merry X-mas/winter solstice/Hanukkah/(whatever hoiday you celebrate .

Jeremy

[lucas]

Pretty tricky, anything you put in to clear the value that is inputted by true will also remove anything you want the user to manually input.

That is if you do it automatically....it can be done with a button so it is only invoked when you hit the button:

[VBA]
Sub a()
If Range("A1").Value = True Then
Range("A3").Value = Range("A2").Value
Else: Range("A3").Value = ""
End If
End Sub
[/VBA]

You have to run the macro each time you change A1. But if you run it on false after entering data into A3....it will be cleared...

[jcfields]

Pretty tricky, anything you put in to clear the value that is inputted by true will also remove anything you want the user to manually input.

That is if you do it automatically....it can be done with a button so it is only invoked when you hit the button:

[vba]
Sub a()
If Range("A1").Value = True Then
Range("A3").Value = Range("A2").Value
Else: Range("A3").Value = ""
End If
End Sub
[/vba]

You have to run the macro each time you change A1. But if you run it on false after entering data into A3....it will be cleared...

I was afraid I might have to invoke a macro here somewhere . I was hoping to get it done where the whole thing is completely automated, but it's not in the cards I guess. I actually have a UDF that will enter the value into "A3" if "A1" = "True", and otherwise it pops up w/ an input box for the user to enter in the value that will go into "A3". However, for some unexplained reason, I can sometimes change values elsewhere in the workbook completely unrelated to any of the UDF's inputs, and the stupid input box will pop up prompting the user to enter the value. I can't figure out how to fix it, so I'm looking for a "better way". I'll post the code for the UDF here in case anyone happens to spot what the problem might be. I'm still pretty green when it comes to all this VBA stuff...

[vba]
Function UDF_1(variable1, variable2)
If Worksheets("Sheet 1").Range("A1").Value = "True" Then
UDF_1 = variable1 + variable2
Else
Do Until UDF_1 <> ""
UDF_1 = Application.InputBox("Enter Value", "Value", , , , , , 1)
Loop
End If
End Function
[/vba]

[mikerickson]

The UDF CellEntry() returns the value that the user types in the cell holding the formula =CellEntry()

Putting =IF(A1,A2,CellEntry()) in A3 should do what you want.

In a normal module:[VBA]Public Const functionName As String = "cellentry()"

Function CellEntry(Optional ByVal inputCell As Range) As Variant
Rem returns the text last entered in the cell
Rem validation.InputMessage holds CellEntry value: .ErrorMessage holds formula
On Error Resume Next
If inputCell Is Nothing Then Set inputCell = Application.Caller
On Error GoTo 0
If inputCell Is Nothing Then
CellEntry = vbNullString
Else
With inputCell.Range("a1").Validation
On Error Resume Next
If IsNumeric(.InputMessage) Then
CellEntry = CDbl(.InputMessage)
Else
CellEntry = .InputMessage
End If
If .Parent.Address <> Application.Caller.Address Then Exit Function
On Error GoTo 0
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertInformation, Formula1:="=(1=1)"
.ErrorMessage = .Parent.FormulaR1C1
.InputMessage = CellEntry
.ShowInput = False
.ShowError = False
End With
End If
End Function
[/VBA]
In ThisWorkbook code module:[VBA]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim myRange As Range, oneCell As Range, xVal As Variant
On Error Resume Next
Set myRange = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each oneCell In myRange
With oneCell
If .HasFormula Then
Rem formula entered
If InStr(LCase(.FormulaR1C1), functionName) = 0 Then
Rem non-ce formula entered, delete ce-Validation
.Validation.Delete
Else
Rem new ce formula entered, update stored formula
xVal = CellEntry(oneCell)
End If
Else
Rem text entered
If Application.CutCopyMode Then
.Validation.Delete
Else
If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then
Rem cell has non-CE validation
Else
Rem set new value for CellEntry and replace formula in cell
.Validation.InputMessage = CStr(.Value)
Application.EnableEvents = False
.FormulaR1C1 = .Validation.ErrorMessage
Application.EnableEvents = True
End If
End If
End If
End With
Next oneCell
End Sub
[/VBA]
CellEntry() returns the last text entered into the cell.
Example:
Enter =CellEntry() into a cell. The cell will show blank and the formula bar will show =CellEntry()
Type "apple" into the cell. The formula bar will show =CellEntry() and the cell will show "apple", the current value of CellEntry().
Type "pear" into the cell and the value of CellEntry() changes to "pear" and the formula stays in the cell.

NOTES:
If a cell has a formula that includes the CellEntry UDF:
a) entering a constant into the cell sets the value of CellEntry to that constant as text.

b) entering a formula into that cell changes the formula in the cell, but not its CellEntry value

c) clearing the contents of that cell sets the value of CellEntry() to vbNullString. It does not remove the formula from the cell.

To remove a CellEntry formula from a cell, either delete the cell or enter a non-CellEntry formula (eg =3) and then delete that formula.

Copy/Pasteing a CellEntry formula cell also copies the CellEntry value.

[jcfields]

The UDF CellEntry() returns the value that the user types in the cell holding the formula =CellEntry()

Putting =IF(A1,A2,CellEntry()) in A3 should do what you want.
...(snip)

mikerickson, you're a freakin' genious and my hero . This works beautifully. Now it'll take me forever to understand the code - a VBA guru I am not. Can I consider this my x-mas present from you?

Happy Holidays!

Jeremy

[mikerickson]

I'm glad it worked for you.

[jcfields]

When I type a value into the cell where the "if" statement is that contains the CellEntry(), the value overwrites the formula. Hmmm....

[mikerickson]

The attachment to my previous post works for me.
Hmm..
1) is the second bit of code in the ThisWorkbook module?
2) is Application.EnableEvents = True?

Can you attach a stripped down version of your workbook?

[jcfields]

The answer to both of your questions is "yes". Give me a few seconds to strip my workbook down, and I'll attach it.

Thanks again for all your help.

[jcfields]

In trying to keep my explanation of the problem simple, I might have left out something important. Your code uses "a1" as the input range. I actually need to use the CellEntry UDF in several places (as demonstrated in the attached workbook). I'm also not using "True" or "False" as the condition, but the determining condition is still a binary choice: "Overhang" or "Intermediate".

I can't figure out the code, let alone how to change it, to take these things into account. But I'm sure this has something to do with the fact that I can't get it to work.

Thanks,
Jeremy

[mikerickson]

One line got changed in the function. This is the correction:
[VBA]Function CellEntry(Optional ByVal inputCell As Range) As Variant
Rem returns the text last entered in the cell
Rem validation.InputMessage holds CellEntry value: .ErrorMessage holds formula
Application.Volatile
On Error Resume Next
If inputCell Is Nothing Then Set inputCell = Application.Caller
On Error GoTo 0
If inputCell Is Nothing Then
CellEntry = vbNullString
Else
With inputCell.Range("a1").Validation
...
End Function[/VBA] The .Range("A1") is relative to inputCell, not the sheet. Its there to prevent errors if inputCell is more than one cell.

[jcfields]

So you just added "application.volatile", and that did it? Hmmm....

Someday (but probably not today ) I'm gonna sit down w/ my VBA book and figure out what you did here. Most of this code is over my '.colorindex=4' head

Thanks again.

[mikerickson]

Sorry, the Application.Volatile was a debugging line that I forgot to remove.
It should work without it.

The line that was changed was the underlined
[VBA]'With inputCell.Range("d8").Validation:rem Bad line[/VBA]
which should be
[VBA]With inputCell.Range("a1").Validation:Rem good line [/VBA]