Well, I'll give a shot at rewriting the code you posted to show an
example of setting refs to wkb's and wks's.

Option Explicit

'[Module Level Variables]
Public g_wkbTarget As Workbook
Public g_wksTarget As Worksheet

Sub FMBC92_A91G_OK_PlaceScore()
'Set fully qualified refs to working file/sheet
Set g_wkbScores = ActiveWorkbook
Set g_wksScores = g_wkbScores.Sheets("Scores")
g_wksScores.Range("E2") = 5
End Sub

**
The above sub sets a global ref to the current workbook and worksheet
being worked on. All your other procedures can use those refs because
they exist for as long as your project is running, OR until you reset
them to other wkb/wks.

Other ways to set refs to a specific workbook are...

'Open a file to work on:
Set g_wkbScores = Workbooks.Open "<FullPathAndFilename.xls")

'If the file contains its code:
Set g_wkbScores = ThisWorkbook
**

Sub FMBC02_W03G_SP_Deactivate()
Call FMBC02_ActivateAndProtect_ContestData
Application.ScreenUpdating = False
With g_wksScores
.Rows("115:129").Hidden = True
.Range("BP110:BQ112").VerticalAlignment = xlBottom
.Range("X110:BQ112").Interior.ColorIndex = 8

Call FMBC02_W03H_RO_Deactivate
Call FMBC02_W03I_RS_Deactivate
Call FMBC02_W03I_SH_Deactivate
Call FMBC02_W03V_CO_Deactivate

.Range("AU100").Select
.Protect
End With 'g_wksScores
Application.ScreenUpdating = True
End Sub 'FMBC02_W03G_SP_Deactivate

**
The With...End With construct reduces 'dot processing' and so makes
your code more efficient and run faster. Given the naming convention
you use for your procedure names, the code is fairly self-documenting
(good practice!) and so I didn't include the redundant comment lines.

Also, if the ranges being worked on here are 'fixed' cells, it might be
prudent to give them local scope defined names and use that name
instead of range addresses so your code is even more self-documenting.
(ie: hiding what data?; formatting what data?) Someone maintaining this
project down the road will have to look these ranges up to see what
cells are being affected.

Another consideration is whether the code needs to reside in the
working file or if it would serve better as an addin where the project
files contain no code/buttons and all runs via a custom toolbar/menus
of the addin. This will obviate any ambiguities as to which procedure
runs when a button is clicked. (For some reason, VBA has a nasty habit
of running the 1st opened file's code over the 2nd opened file's code
when the procedures are the same name, *regardless of which file
executed*!) Now, your working file can be a template used by the addin
for each contest.

Perhaps I could review your project to see if going this route is
feasible. If interested, send your file to me at...

gesansomATnetscapeDOTnet
**

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



--- Posted via news://freenews.netfront.net/ - Complaints to ---