Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when button is selected?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when buttonis selected?
On 13/09/2011 2:54 PM, RJQMAN wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when button is selected?
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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when button is selected?
On Sep 13, 1:14*pm, GS wrote:
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.ColorInde x = 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when button is selected?
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 --- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when button is selected?
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 --- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should I activate workbook before activating sheet when button is selected?
On Sep 14, 10:43*am, GS wrote:
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 athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc --- Posted via news://freenews.netfront.net/ - Complaints to --- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When Activating workbook always goes to left most sheet | Excel Programming | |||
creating a button to activate another worksheet within a workbook | Excel Programming | |||
worksheet.activate not activating | Excel Programming | |||
Activate button when row selected. | Excel Programming | |||
Activate Macro on Activating a sheet | Excel Programming |