excel - Get Worksheet_change event to execute macro automatically -

-

i have macro (applyfilter) filters through many worksheets based on date enter cell b1 on worksheet (grand totals). macro is:

sub applyfilter() 'filters worksheets except worksheet1 date entered _                   'grand totals!b1 dim ws_count integer dim integer dim filterrange variant filterrange = range("'grand totals'!b1") ' set ws_count equal number of worksheets in active ' workbook. ws_count = activeworkbook.worksheets.count ' begin loop.     = 2 ws_count     sheets(i).select     activesheet.autofiltermode = false 'remove existing filters     worksheets(i).range("a2").autofilter field:=1, criteria1:=range("'grand totals'!b1").text     next sheet1.activate end sub

when execute macro manually, executes , filters should. however, when call macro sub:

sub worksheet_change(byval target range) if not intersect(target, me.range("b1")) nothing _     call applyfilter end sub

i "macros" window offers list of macros available. can select "applyfilter" macro , click run , macro executes , filters worksheets desire.

i found many references automatically executing macro within sub, none refer "macros" window, must select macro run. instead, when enter date in cell b1 of "grand totals" worksheet , hit enter, sub worksheet_change(byval target range) should automatically call "applyfilter" , apply date filter many worksheets.

i have created button , used button_click call "applyfilter", , well. but, seems more intuitive enter date , press enter execute macro. put button_click method, i'm trying learn vba firstly, , i'm stubborn enough want learn how make work, , not want settle work.

sheet code must in grand totals sheet

  • right click grand totals sheet tab
  • view code
  • ensure code below pasted in here
  • excel

grand totals sheet code

sub worksheet_change(byval target range) if not intersect(target, me.range("b1")) nothing call applyfilter end sub

more efficient filter code

sub applyfilter() dim ws worksheet each ws in activeworkbook.sheets       if ws.name <> "grand totals"         ws.autofiltermode = false         ws.range("a2").autofilter field:=1, criteria1:=range("'grand totals'!b1").text       end if next end sub

Comments

Post a Comment

Popular posts from this blog

c++ - llvm function pass ReplaceInstWithInst malloc -

-
#include "llvm/pass.h" #include "llvm/ir/module.h" #include "llvm/ir/function.h" #include "llvm/support/raw_ostream.h" #include "llvm/ir/legacypassmanager.h" #include "llvm/ir/instrtypes.h" #include "llvm/transforms/ipo/passmanagerbuilder.h" #include "llvm/ir/irbuilder.h" #include "llvm/transforms/utils/basicblockutils.h" using namespace llvm; namespace { struct replacepass : public functionpass { static char id; replacepass() : functionpass(id) {} virtual bool runonfunction(function &f) { allocainst* insttoreplace = ??? basicblock::iterator ii(insttoreplace); replaceinstwithinst(insttoreplace->getparent()->getinstlist(), ii, new allocainst(type::int32ty, 0, insttoreplace)); return true; } }; } char replacepass::id = 0; static void registerreplacepass(const passmanagerbuilder &, legacy::passmanagerbase &pm) { pm.add(new re
Read more

java.lang.NoClassDefFoundError When Creating New Android Project -

-
i've tried installing (and re-installing) android studio several times now, , each time, studio isn't able create new android project. when attempt initial android studio window (ie. basic 'create', 'open', 'import' etc options), i'm asked of questions in wizard, brief progress dialog, followed nothing @ all. if open incomplete project process creates choosing 'open' list, can try choosing create new android project menu. this has same issue, error message in output console time... java.lang.noclassdeffounderror: com/sun/xml/internal/bind/v2/model/annotation/annotationreader (wrong name: com/sun/xml/internal/bind/v0/model/qnnotation/annotationreader) note: when installed android studio, installed incompatible jdk (1.8, not 1.7). have had go default project settings change jdk uses 1.8. does know how can avoid this? there place should downloading jxb classes? missing in java paths? this appears have been caused cor
Read more

Decoding a Python 2 `tempfile` with python-future -

-
i'm attempting write python 2/3 compatible routine fetch csv file, decode latin_1 unicode , feed csv.dictreader in robust, scalable manner. for python 2/3 support, i'm using python-future including imporing open builtins , , importing unicode_literals consistent behaviour i'm hoping handle exceptionally large files spilling disk, using tempfile.spooledtemporaryfile i'm using io.textiowrapper handle decoding latin_1 encoding before feeding dictreader this works fine under python 3. the problem textiowrapper expects wrap stream conforms bufferediobase . unfortunately under python 2, although have imported python 3-style open , vanilla python 2 tempfile.spooledtemporaryfile still of course returns python 2 cstringio.stringo , instead of python 3 io.bytesio required textiowrapper . i can think of these possible approaches: wrap python 2 cstringio.stringo python 3-style io.bytesio . i'm not sure how approach - need write such wrapper or 1 ex
Read more