Running a macro crashes excel -

i have excel spreadsheet containing around 15 excel dct. on sheet, there drop down menu allowing selecting username, , when changing value, macro updates dtc present on spreadsheet username , refreshes data. problem reason, crashes excel except on high perf computer. don't know do

here macro :

dim pt pivottable dim pi pivotitem dim strfield string  strfield = "cslts"  on error resume next 'application.enableevents = false 'application.screenupdating = false      if target.address = range("h1").address               each pt in activesheet.pivottables                 pt.pagefields(strfield)                     each pi in .pivotitems                         if pi.value = target.value                             .currentpage = target.value                     exit                          else                             .currentpage = "(blank)"                          end if                     next pi                 end             next pt      end if  end sub 

regards,

there no need loop through each of pivotitems, set pi object required value , validate afterwards.

it seems not disabling application.events (by lack of application.enableevents = true @ end of procedure), if worksheet event triggered again every time pivotfield page changed , cause of excel crashing.

be aware if pivotfield(strfield) not set pagefield you’ll "run-time error 1004: unable pagefields property of pivottable class" therefore pivotfield(strfield) need validated pagefield (see below in code).

assuming procedure worksheet change event try (see comments in code):

private sub worksheet_change(byval target range) dim pt pivottable dim pf pivotfield dim pi pivotitem dim strfield string      strfield = "cslts"      application.enableevents = false    'this avoids worksheet event restarting again every time pivottable page changed.     application.screenupdating = false      if target.address = "$h$1"         each pt in target.worksheet.pivottables              rem set pagefield             set pf = nothing             on error resume next             set pf = pt.pagefields(strfield)             on error goto 0              rem validate pagefield             if not (pf nothing)                 pf                     .enablemultiplepageitems = false                      rem set pagefield item                     set pi = nothing                    'initialize item object                     on error resume next                'this avoid error if item not present                     set pi = .pivotitems(target.value2) 'no need loop through items set required item                     on error goto 0                     'clears error object                      rem validate & set pagefield item                     if not (pi nothing)                         .currentpage = target.value2                     else                         .currentpage = "(blank)"      end if: end with: end if: next: end if      application.enableevents = true     application.screenupdating = true      end sub 

additionally, suggest refresh pivottables ensure working latest update source data, aware of different values pivotitem properties value , sourcename have.

suggest read following pages gain deeper understanding of resources used:

on error statement,

pivotcache object (excel), pivotfields object (excel), pivotitems object (excel)