excel - VBA data validation to default value from named range -


so have data validation in once cell changes 1 of 3 different named ranges based on user selection in cell. need when user selects value, i.e. "selection a", data validation not change corresponding named range, display first value within range.

currently can manipulate code default value, keeps changing default value every time try , make selection.is possible? below code i'm running once specific named range using worksheet_change event

in example i've used named range selection_a

private sub worksheet_change(byval target range)     if range("$e$3").value = "selection a"     range("l3:r4").validation         .delete         .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _         xlbetween, formula1:="=selection_a"         .ignoreblank = false         .incelldropdown = true         .inputtitle = ""         .errortitle = ""         .inputmessage = ""         .errormessage = ""         .showinput = true         .showerror = true     end     end if

with default value showing , changing constantly:

private sub worksheet_change(byval target range)     if range("$e$3").value = "selection a"     range("$l$3").value = sheets("sheet2").range("$m$4").value         range("l3:r4").validation         .delete         .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _         xlbetween, formula1:="=selection_a"         .ignoreblank = false         .incelldropdown = true         .inputtitle = ""         .errortitle = ""         .inputmessage = ""         .errormessage = ""         .showinput = true         .showerror = true     end     end if

in example below create validation list in cell a1 of sheet1. entries list listed in k7 k9. 

with sheet1("a1").validation     .delete     .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _     xlbetween, formula1:="=sheet1!$k$7:$k$9" end

i'll use format because better. question:

private sub worksheet_change(byval target range)     if not intersect(target, sheet1.range("e3")) 'checks e3 1 changed         application.enableevents = false         if range("$e$3").value = "selection a" 'list              sheet1("a1").validation                .delete                .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _                   xlbetween, formula1:="=sheet1!$k$7:$k$9" 'cells list              end         elseif range("$e$3").value = "selection b" then' list b                               sheet1("a1").validation                .delete                .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _                   xlbetween, formula1:="=sheet1!$k$7:$k$9" 'cells list b              end          end if     end if     application.enableevents = true  end sub

i'm pretty sure got cells wrong, put right ones per example gave top! right when e3 changed, create new list depending on in e3. e3 should validation list in opinion, , possibilities should represented in worksheet change event.

edit: added enableevent = false after seeing byron's comment, without make fire in execution.


Comments

Post a Comment

Popular posts from this blog

powershell Start-Process exit code -1073741502 when used with Credential from a windows service environment -

i'm running strange behavior powershell start-process call. here call: $process = start-process ` "c:\somepath\mybinary.exe" ` -passthru ` -credential $defaultcredential ` -wait ` -workingdirectory "c:\somepath" ` -loaduserprofile if ($process.exitcode -ne 0) { #do } this call return exit code - 1073741502 . after quick search, exit code seems related generic error when program not load required dll (aka. status_dll_init_failed ). when run without -credential $credential program runs correctly. in order isolate problem, manually launched some.exe in prompt target credential , runs smoothly. so problem seems come way start-process cmdlet launch process. i found potential solutions problem tried apply no luck : link , link . would have idea of what's going on here ? edit 1: run proc mon monitoring program activities when launched directly or via powershell script. problem seems occur when loading kernelbase
Read more

twig - Using Twigbridge in a Laravel 5.1 Package -

i'm trying create laravel 5.1 package uses twigbridge instead of .php views. can normal .php views display fine, when try make use of .twig templates i'm met error. possible use twigbridge templates when creating views laravel package or pretty stuck blade , php ? edit 1: loading twig template without extending works. it's when start using {% extends 'file' %} error comes out. i error error loading /virtual/laravel/packages/username/mypackage/src/views/sample.twig: template "templates.master" not defined () in "/virtual/laravel/packages/username/mypackage/src/views/sample.twig" @ line 1. my folders username |_ mypackage |_ src |_ controllers - samplecontroller.php |_ views |_ templates - master.twig - sample.twig twig template sample.twig {% extends 'templates.master' %} {% block content %} {{ parent()
Read more

c# - LINQ join Entities from HashSet's, Join vs Dictionary vs HashSet performance -

i have hashset's each stores t, have written test application compares different relation algorithms can think of, i'm not pleased results getting. does there exist more efficient ways of achieving object relations, once testing? using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using system.diagnostics; namespace linqtests { class program { static void main(string[] args) { hashset<user> usertable = new hashset<user>(); hashset<userproperty> userpropertytable = new hashset<userproperty>(); #region lets create dummy data. console.writeline("please wait while creating dummy data, can take while..."); console.writeline(""); int rows = 1000000; for(int x = 0; x < rows; x++) { random rnd = new random(); // ad
Read more