hiding excel sheet while a macro is running

[dalex17]

I've seen an ability with excel that hides (or removes from visibility) an excel workbook while a macro is running.

The purpose is so that a user will not see the macro as it runs.

FYI - I'm recording the macro by movements and not writing program language.

Any step by step assistance would be greatly appreciated.

Dalex

 

[VBAjedi]

Well, you could use:

Worksheets("Sheet1&quot.Visible = xlSheetHidden

at the beginning of your code, and:

Worksheets("Sheet1&quot.Visible = xlSheetVisible

at the end. However, it sounds like all you may need to do is disable screen updating while your code runs (so the user can't see things changing until they are done). That would be done with:

Application.ScreenUpdating = False
and
Application.ScreenUpdating = True

Let me know if that's what you are after!

VBAjedi

 

[dalex17]

YES!!! It worked.

Thank you so much!


Dalex

 

[dalex17]

Another question if you have a minute:

What if I wanted to assign the macro to a hyperlink that is not in the sheet that the macro will affect? For example. I want the user to click the macro that is in sheet 2, but I want sheet 1 to be modified by the macro.

Does this make sense?

dalex

 

[VBAjedi]

I trigger almost all of my macros from custom menu bars, but if you don't want to go to the trouble of creating/maintaining them, you could just use a button on sheet 2 that calls the macro.

If you used the macro recorder to create your macro it probably just refers to "ActiveSheet" instead of the actual sheet name, so you will need to edit your code to make it refer explicitly to the sheet you want it to affect. I.e. instead of every:

Activesheet.something

you need:

Worksheets("SheetName&quot.something

Otherwise Excel gets confused as to what sheet you want to do what to.

If you need more specific assistance you will need to post your code.

VBAjedi