"Excel has stopped working" when using VBA macro with on change event -


i experiencing consistent "excel has stopped working" error. have user form populates combo box dependent drop-down options based on user selects in 1st combo box.

the error consistently created any of on-change event subs when user types 2nd combo box letter corresponds existing value should in 2nd box. if user selects option drop-down list, not occur -- when user types in 1st letter of matching value.

example: if 1st combo box "fruit", 2nd combo options might "apple", "orange", etc. if user types in "a" or "o" in 2nd box (because matches corresponding values), crashes excel. why not "fill in" matching value on 1st combo box?

error handling in place, never throws error in code, "excel has stopped working", crash.

do need use enable/disable worksheet events within macros? if so, please offer suggestion of how implement , explanation of how works. have solved previous on change problems converting them click button, i'd rather learn how better implement on change events without errors.

thank you! here example code think associated excel crash:

private sub option1_change()    on error goto option1_change_error  'set cell h4 value entered in opt1 (adv. filter criteria)  sheet6.range("h4").value = me.option1.value  'run macro advfilter sets criteria dependent drop-down box  advfilter     me.option2.value = ""  'set row source output of adv filter  me.option2.rowsource = "advfilter_output"  'set list box content  'if no adv.filter output, blank listbox  if sheet2.range("bz3").value = ""      me.lstoptions.rowsource = ""  else      me.lstoptions.rowsource = "advfilter_output"  end if  exit sub    option1_change_error:  msgbox "an error has occurred.  " _      & vbcrlf & "" _      & vbcrlf & "" _      & "error " & err.number & " (" & err.description & ") in procedure option1_change_error of form frmoptions"  end sub          private sub option2_change()    on error goto option2_change_error  if me.option1 = "" , me.option2 <> ""      msgbox "select category first see corresponding options exist."      exit sub      else      me.option2.rowsource = ""  end if  exit sub    option2_change_error:  msgbox "an error has occurred.  " _      & vbcrlf & "" _      & vbcrlf & "" _      & "error " & err.number & " (" & err.description & ") in procedure option2_change_error of form frmoptions"  end sub

figured out, being able bounce off ideas tobriand , run through possible problems. the problem ended being conflicting rowsource between 2 events, result of inappropriate else clause within option2.

private sub option2_change() setting rowsource second combo box blank on change, preventing ability type in anything. match entry property set false, assume removal of rowsource combobox 2 caused match entry complete property freak out when user typed in , didn't find match. i removed else portion option2 event , seems work now.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -