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
|
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 |
All times are GMT +1. The time now is 05:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com