Should I activate workbook before activating sheet when button is selected?

[RJQMAN]

I had two Excel programs open on my desktop, and I was working with
one, and then jumped to the other, pressing a Command Button on the
2nd program. The 2nd program crashed, as the macro tried to operate
on the first program.

I send this program out for 'soccer moms' to use to keep score at
competitive high school events, so I want it to be as foolproof as
possible. The first event of the season is in eight days.

I realized right away what had taken place, but the users of my
program probably will not and would just see the program display jump
over into the VBA section and not know what to do. In order to
prevent them from having this experience and going into a state of
panic, how can I best prevent the problem from happening to them?
Should I insert a statement at the start of my macros to active the
workbook each time? That would be easy enough to do, but I am not
sure if it would be the best approach, or how to word it so that it
does not cause a problem greater than the one it is supposed to solve.

If my macros apply to another page in the workbook, they already say
'thiswookbook.sheets(" at the beginning. If they apply to the sheet
that is open, they all presently start with 'activesheet'/

Should I modify that statement somehow, or should I have some kind of
'activate workbook' statement first, in case they do what I did - work
on one spreadsheet, and then jump over and click a button on my
program?

I appreciate the advice very much.

 

[Vacuum Sealed]

I had two Excel programs open on my desktop, and I was working with
one, and then jumped to the other, pressing a Command Button on the
2nd program. The 2nd program crashed, as the macro tried to operate
on the first program.

I send this program out for 'soccer moms' to use to keep score at
competitive high school events, so I want it to be as foolproof as
possible. The first event of the season is in eight days.

I realized right away what had taken place, but the users of my
program probably will not and would just see the program display jump
over into the VBA section and not know what to do. In order to
prevent them from having this experience and going into a state of
panic, how can I best prevent the problem from happening to them?
Should I insert a statement at the start of my macros to active the
workbook each time? That would be easy enough to do, but I am not
sure if it would be the best approach, or how to word it so that it
does not cause a problem greater than the one it is supposed to solve.

If my macros apply to another page in the workbook, they already say
'thiswookbook.sheets(" at the beginning. If they apply to the sheet
that is open, they all presently start with 'activesheet'/

Should I modify that statement somehow, or should I have some kind of
'activate workbook' statement first, in case they do what I did - work
on one spreadsheet, and then jump over and click a button on my
program?

I appreciate the advice very much.

It would be much easier if you post the codes you are using, then we
could point to where the problem is.

Mick

 

[GS]

To add to Mick's suggestion, it would just be good programming practice
to set object refs in code to each open workbook your code acts on.
Usually, you would then not need to activate any workbook or worksheet
because your code knows which is which when you use object refs!

 

[RJQMAN]

To add to Mick's suggestion, it would just be good programming practice
to set object refs in code to each open workbook your code acts on.
Usually, you would then not need to activate any workbook or worksheet
because your code knows which is which when you use object refs!

--
Garry

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

I do not know how to set those object refs. My code always is
intended to act only on the workbook that is open - there is no cross
referencing to any other workbooks. However, it is common for a user
to make a 2nd copy of the program and have it active at the samne time
if they are scoring two different events (that are not simultaneous -
always the events follow one another). They receive the program from
me, they may and probably do rename it, and then use it.

As tp setting the object ref, could you give me an example? I am a
self-taught programmer. I have worked on this program for 3 years,
and it is finally running well and meeting all the needs of the
users. I just do not want a nuisance crash.

As to posting my code, there are over 100 command buttons in the
program - all on the pages that the user can use to set the program up
for their specific contest. It is hard to find an exact example that
will explain the problem, but I am including a typical macro below, if
that is of help. I think your specific suggestion, Garry, is what I
need to do. I just do not understand how to do it.

Here is my code for run of the command buttons - it is fairly
typical. Some of the subs are much more complex, of course, but they
all open with something similar - either a statement that calls a
subroutine that unprotects a sheet, or something like the following;

Sub FMBC92_A91G_OK_PlaceScore()

activesheet("Scores");range("E2").value=5

End Sub

here is am actual sub;

Sub FMBC02_W03G_SP_Deactivate()
'
' ================== UNPROTECT SHEET ====================
'
Call FMBC02_Activate_and_Unprotect_Contest_Data
Application.ScreenUpdating = False
'
' ============ DEACTIVATE SPECIAL CLASSES ===============
'
ActiveSheet.Rows("115:129").Hidden = True
ActiveSheet.Range("BP110:BQ112").VerticalAlignment = xlBottom
ActiveSheet.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
'
' ========== POSITION CURSOR AND PROTECT SHEET ==========
'
ActiveSheet.Range("AU100").Select
ActiveSheet.Protect
'
End Sub

I hope this is the information you need. But I think what I need is
to know better what would be good programming practice here.

Thank you for responding. It is very much appreciated.

 

[GS]

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
**

 

[GS]

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

 

[RJQMAN]

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 codeyou 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: hidingwhat
data?; formatting what data?) Someone maintaining this project down theroad
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 containno
code/buttons and all runs via a custom toolbar/menus of the addin. Thiswill
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 athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

--- Posted via news://freenews.netfront.net/ - Complaints to (e-mail address removed) ---

Thank you very much. I have a pretty good idea as to what you are
suggesting. I need to study it and trial-and-error it a bit to fully
grasp this. As I said, I am self-taught, one step at a time, and have
been working on the program for several years to get it to this stage
where it meets the needs of all the people who use it.

The first event that uses the program this season takes place eight
days from now, and I do not have the time to rewrite it quickly if the
changes are extensive, but I am willing to spend many hours on it this
week if the program is in danger of crashing next weekend, and if this
would resolve it. In the long run, I would like to speed it up, but
right now I just need it to work!

I will gladly send you the program, and I appreciate your help and
suggestions. I do not mind doing the work at all - my shortfall is my
knowledge base, which comes from this newsgroup, and John Walkenbach's
book. I had purchased several other books over the years, but his was
the only one that was written in a manner that I could understand as a
beginning programmer with big ideas and a lofty goal.

I did the program as a learning exercise for me and a favor for the
organization that I assist. I am not a paid programmer or anything
like that. So any and all help is appreciated - I just have to work
to understand it!

I will forward the code, and study the response you posted. Many
thanks to all who responded, and especially to Garry for taking the
time to write everything out for me. This is an amazing group of
people.