Excel custom data validation with drop down list

[Pawel]

Hi,
I was wondering if it's possible to add a dropdown list while using:
Data validation:
Validation criteria
allow: Custom
I've created this formula
=OR(G11=Setup!$E$447:$E$449)
to validate the input, but I also need to display valid values in dropdown list ( while clicking on cell)
I can't use
Validation criteria
allow: List
because then it accepts "?" as input (probably excel treats this as a "wildcard") and I don't want that.
Please help

[Paul_Hossler]

Are you the built in Excel [Data Validation] feature, or your own data validation formula?

Using the built in one, "?" is not accepted


18155

I created a simple list of acceptable values for A1 (the embedded screen shot) and a "?" is caught

[SamT]

Custom DropDown Validation using UserForm?

WorkSheet Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Target.Address = "$D$1" Then Exit Sub
If Target = "" Then Exit Sub
Cancel = True
frmDater.Show
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target.Address = "$D$1" Then Exit Sub
If Not Target = "" Then Exit Sub
frmDater.Show
End Sub


UserForm Code.
Private Sub lbxDatePicker_Change()
'ControlSource and RowSource hardcoded
Unload Me
End Sub


Private Sub UserForm_Initialize()
'Set Form position herein

'Example of returning cell address
Dim Addy As String
Dim Tp, Lf 'in pixels '
Addy = Split(Me.lbxDatePicker.ControlSource, "!")(1)

With Sheet1
Tp = .Range(Addy).Top
Lf = .Range(Addy).Left
End With
End Sub

This doesn't work quite right. See: Unexpected "Action Will Reset Project" (http://www.vbaexpress.com/forum/showthread.php?58390-Unexpected-quot-Action-Will-Reset-Project-quot)

[Pawel]

Are you the built in Excel [Data Validation] feature, or your own data validation formula?

Using the built in one, "?" is not accepted


18155

I created a simple list of acceptable values for A1 (the embedded screen shot) and a "?" is caught

I forgot to mention that I'm using ERROR ALERT > Style: Information ( I cannot use "stop")

[Paul_Hossler]

I forgot to mention that I'm using ERROR ALERT > Style: Information ( I cannot use "stop")


Useful information


Two approaches to look at.


Option Explicit
'Private Sub Worksheet_Change(ByVal Target As Range)
'
' Me.ClearCircles
' Me.CircleInvalid
'
'End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target.Cells
With r
If .Validation.ShowError Then
If .Validation.Value Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbRed
End If
End If
End With
Next
End Sub




This is the second one. You could do your own message or clear the cell or other actions


18164

[Pawel]

The problem is that macros have to stay disabled

[Paul_Hossler]

You might have to teach the users to use [Circle Invalid Data]

You could add the buttons to the ribbon to make it easier for them


18168

[Pawel]

I guess that's the only solution... Thanks Paul