0

Is there a way to run different macros on several worksheets but, always show sheet1 when the macros are running?

My sheet1 is a cover sheet (a kind of title page) with a command button. Pressing the command button runs macros on a further 5 sheets. Each sub starts by moving to the correct sheet ...

e.g.

Sheets("CDS Data").Select

... but this sheet is shown whilst the macro is running, can I stop this being shown and just show the sheet1 until all macros are finished?

Thanks

pnuts
  • 58,317
  • 11
  • 87
  • 139
SMORF
  • 499
  • 6
  • 13
  • 30
  • 2
    You almost never need to select a sheet to work with it in code, and it is less efficient to do so. Really you ought to rewrite your code so that it explicitly refers to the correct sheet rather than manipulating the active sheet. – Rory Nov 20 '14 at 08:55
  • 1
    http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Siddharth Rout Nov 20 '14 at 09:02

1 Answers1

-1

Actually ... I have found a way. By starting my macros with;

Application.ScreenUpdating = False

This seems to work.

SMORF
  • 499
  • 6
  • 13
  • 30
  • it depends on the code... even if you turn off the screen updating and use `Sheets("Sheet2").Select` then Sheet2 will in fact get swapped to be the active one –  Nov 20 '14 at 08:55
  • @SMORF that's a terribly fragile solution. I recommend that you spend some time to [do it right](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) now rather than spend twice the time fixing it later when it breaks. – RubberDuck Nov 20 '14 at 11:07