Oops! I forgot to edit my copy/paste for module level vars. Correct as
follows:

GS explained on 9/14/2011 :
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_wkbScores As Workbook
Public g_wksScores 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 ---