Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA to emulate "validation" drop-down list
I like the Excel data validation tool, but it is lost if the user does a paste of a range of cells. Is there a way to emultate this function in VBA. If I use a form to do it, the form will steal the focus and the user can only work on one cell at a time. BTW this is for 2000 version of the program
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VBA to emulate "validation" drop-down list
You could always re-create the DV when a user selects teh cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("C1:C10")) Is Nothing Then With Target With .Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=A1:A10" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Donager Dave" wrote in message ... I like the Excel data validation tool, but it is lost if the user does a paste of a range of cells. Is there a way to emultate this function in VBA. If I use a form to do it, the form will steal the focus and the user can only work on one cell at a time. BTW this is for 2000 version of the program |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation ?:Accepting both Numbers AND specific letters("N","n"," | Excel Discussion (Misc queries) | |||
Data Validation "List" - Setting length of list shown | Excel Discussion (Misc queries) | |||
Can I include a "validation drop down" in a conditional statement? | Excel Worksheet Functions | |||
how do i enable a drop down list depending on result of "if" funct | Excel Worksheet Functions | |||
Make typing "jump" to matching item(s) in drop-down list? | Excel Discussion (Misc queries) |