Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » VBA - Private Sub Routines

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

VBA - Private Sub Routines

Resolved · High Priority · Version 2007

Reply Mon 6 Jun 2011, 18:31Delegate 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

For upcoming training course dates see: Pricing & availability

Reply Wed 8 Jun 2011, 10:07Trainer 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

Access articles

· Discover The Easy Way To Create And Modify A Table In Access 2010
· So You Still Haven't Learned How To Use Access Yet?
· Microsoft Access 2007: New Features
· Innovation with Microsoft Access
· How To Use Tabs To Show Lots Of Related Data On One Form In Access

Please browse our web site to find out more about
access+training+in+london and other Microsoft training courses.

Access tip:

Random sampling

Data is th key to a good database, therefore checking is essential.

A good way of checking data is by random sampling. You can do this in a query, by typing the follow:
1) In the Field box create a RandonID field eg. Randon Id: Rnd(fieldname])

2) sort the field
3)Right click and chage the properties for To value to be the number of randon records you want to see.

4) Change the set to Show row for Randon ID to be False, add all the other fields you want to see and the run the query.

View all Access hints and tips