VBA - Private Sub Routines

Resolved · High Priority · Version 2007

Mon 6 Jun 2011, 18:31 Delegate Michael said...

Michael has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

VBA - Private Sub Routines

I am trying to use the 'Workbook_Activate ()' function in the private sub routine in order to open a 2nd workbook that contains data which feeds into my Activework book. I would like this to happen automatically when the ActiveWorkBook is opened. However i am coming up against a few issues.

I tried recording a macro to show the steps i wanted to take. Unfortuantely it seems that the macro is repeating itself every time i click away from the ActiveWorkBook and then back into it. I just wanted the update to occur once upon opening. Is this due to the 'Activate' function? It seems to be going in a loop.

I dont want other users of this spreadsheet to have to click edit links, update source

The second issue is that the Data workbook always recommends that it be opened as 'ready only' and gives me a user form box choice. Is there a way automate my selection on these user forms or stop them appearing?

This would mean that all updates are complete and the form is no longer complicated to any inexperienced user.

Any help would be much appreciated.

best regards
Mike Tuke

Wed 8 Jun 2011, 10:07 Trainer Stephen said...

RE: VBA - Private Sub Routines

Hi Mike

Thanks for your question

Your first issue is easily solved. The activate event fires whenever the workbook is selected. Thus it will in principle occur many times subsequent to the workbook opening. The correct event for this is the open method. This occurs once when the workbook opens.

As to your other issues, I would need to see your code to be able to help here. Could you copy and paste it into this thread and I will try to sort your issues out

Thanks

Stephen

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Change The Default Font in Access 2003

You can change the default font in Access 2003 so that whenever you create a new database your preferred font is automatically set.

To change the default font:

1.From the menu bar select Tools.
2.Click Options.
3.Go to the Datasheet tab:
4.In the Default font section select your preferred font from the list of fonts e.g. Arial.
5.Select your preferred size from the size menu e.g. 12.
6.Click Apply.

Click on the Tables/queries tab:
1.In the Query Design font section select your preferred font from the list of fonts e.g. Arial.
2.Select your preferred size from the size menu e.g. 12.
3.Click Apply.
4.Click OK.

The default font for Access has now been changed.