Solved: Run macro before a worksheet is shown

[Rejje]

Hi again vbax Forum!

I need a macro to be run when one clicks to look at a specific worksheet.

What does it look like?

[Bob Phillips]

Worksheet_Activate event.

[Rejje]

Worksheet_Activate event.

Many thanks!

Only thing one sees the change that macro AdjustWorksheet_SJR makes for a fraction of a second when the worksheet openes. It does bother my struggle for perfection :rotlaugh:

Public Sub Worksheet_Activate()

Call AdjustWorksheet_SJR

End Sub

Maybe it is possible to add a small delay or something before worksheet openes? I think 1/2 second would do it...

[Bob Phillips]

Set Application.ScreenUpdating to False before, True after it.

[Rejje]

Allready hade done so. I've learned it from you xld once.

However - it's not a big deal with the delay, one barely notices.

[Paul_Hossler]

'pauses processing
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


and then


Call Sleep (500) ' 500 milliseconds


Paul

[Rejje]

'pauses processing
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


and then


Call Sleep (500) ' 500 milliseconds


Paul

Do I put Declare Sub Sleep in a module, ThisWorkbook or elsewhere?

[Rejje]

And by the way: In case I would like a message box to appear in the meantime the time sub passes ones time then where do I put it? I made som attempts but the short structured sub "Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)" and its logic is beyond my vba script skills...

[Paul_Hossler]

The 'Declare' can go at the top of a standard module, but a 'Progress Bar' for just 1/2 second doesn't seem worth while

I use a Userform to show a progress bar, but it can be a little complicated

If you still want to see, I'll extact the code into a small workbook for you

Paul

[Rejje]

My interest in this is really to learn these type of things - not only for these occasional 500 milllisecs (but also I see a good use in this function for some code I will need add to this project later).

Yes - I would really appreciate it Paul!

[Paul_Hossler]

May not be perfect (actually I'm sure it's not)

But works for what I need it to

Paul

[Rejje]

I'm trying to understand all what is happening in these codes. However it's not easy with my limited vba skills. Allthough I've managed to make it work in a forerunner to the function I will later need this code for in my project (progress bar while a macro is writing multiple pages Excel>Word>Acrobat).

I might need some further support with this code later Paul...

Thanks a lot!

[Bob Phillips]

That seems an incredibly complex progress bar Paul. What is it all doing, for instance, what is SocketsInitialize for?

[Paul_Hossler]

XLD -- You're 100% correct. A number of those are not required for a progress bar

I just copied the modules from my 'Toolbox' workbook where I keep my own 'library' of subs and functions to use as 'building blocks'. When I find a nifty bit of code, even if I have no immediate use for it, I'll include it in one of my 'toolbox' modules so that I don't lose it (like the SocetSet stuff). Many times, I'll have to 'generalize' the code in order to make it general purpose for my library. Helps with re-use, but I also find it helps me with understanding and learning.

I should have cleaned it up before posting a sample progress bar, which as you said has much more than just the code required for a progress bar.

My approach to project development is to try to emulate a link library for the VBA modules by copying toolbox modules into the project workbook.

My 'version Control' is by the _xx suffix. So when a 'toolbox' module is fixed and/or expanded, I increment the _xx, and then I can 'refresh' projects that have earlier versions

So 'Util_System_04' is from my Utilities WB with System functions, 4th update.

I do know that one thing I'm a little (or a lot) lax on is documenting the purpose and parameters and assumptions of the modules. I've seem some very well documented professional code with full and complete calling information


Obviously, this means that a project workbook does contain unneeded code, but for the sort of things I do, it rarely is a problem. And IMHO, the advantages (or at least what I see as advantages) of the toolbox (faster development, single point fixes, some version control) usually out weigh the disadvantages

Now ... if any of the professional developers in the forums have alternative suggestions, I really would be interested.

To avoid confusion for the OP with any unneeded functions, I've cleaned up the sample WB to just the VBA for the Progress Bar

Apoligies for any confusion

Paul

[Rejje]

Oh yeah - was as my initial theory... :help