_3)
Originally Posted by
pongmeister
.... I would like to have one macro that will
update specific worksheet.items depending on which module was active when macro was invoked...
_3a)
Your choice of words has me a bit confused here ( Note: i am a bit thick – that is not my fault... ).
“worksheet.item() “ is, as i tried to explain, the number counting along from the left of a Worksheet in a workbook. (That is to say, As you see the Tabs below in the Excel Window counting from the left ). Maybe you meant to say “update specific worksheet things...” – I do not think you want to update ( change ) the item number of a sheet. ( This you can do with the following sort of code line:
Which in changing the position of the Tab will cause its item number to change, as well as changing others due to the total reorganised tab order. For example: Run this simple code on your last File:
....)
_.....
_3b)
Originally Posted by
pongmeister
....
My question is can I have a macro named "Controller" with an if statement so if it is invoked from the active sheet Worksheet 1 will run Makro2SHimpfGlified_1()
or if it is invoked from active worksheet 3 it will run Makro2SHimpfGlified_2() .......
As i mentioned i am still learning myself, and i think we are both maybe not quite grasping how VBA works and “thinks”. I am not sure if you really want a sheet to “evoke” a macro. A Worksheet can invoke a macro, - there is a special code type ( called “Event type” ) which i can do for you that springs in when you select a Worksheet. That is a little advanced.
_ There is a lot to take in now... but you asked!!!!
_ Each sheet in question would have a code of this form which MUST BE PUT IN THE SHEET MODULE, not a normal module. Then select the sheet in whose Sheet module you put the code in. Try it!
_......................
_ 3c) . when you get that working and understand what is going on, then try to take it one step further:
_3c) (i) Copy this code to the SHEET MODULE of both your first and Third Sheet ( Delete the initial code above first, or overwrite it with the following code )
_ 3c) (ii) Copy all these codes to ANY NORMAL MODULE:
'
_...
As I am trying to demonstrate as much as possible along the lines of the codes discussed so far, I modified your file so that the cells A1 and AD100 originally look like this
Using Excel 2007
| Row\Col |
A |
|---|
1 |
Originally Written in cell A1 sheet item 1 |
| Row\Col |
A |
|---|
1 |
Originally Written In cell A1 in Third Sheet |
_ ..
If now you select, for example, the first Worksheet, ( “ca” ) and then look at the second Worksheet ( “CA_RR1” ) then you should find that the second Worksheet now has changed to look like this
| Row\Col |
AD |
|---|
100 |
Originally Written in cell A1 sheet item 1 |
Similarlar results are obtained after selecting the Third sheet
_........
That was a lot to take in. I tried to keep it as simple as possible and as close to your request as possible. But VBA can be a pain in the ar__ sometimes. You will see for example i have a pair of extra lines in the SHimpfGlified codes:
For some reason ( which i do not understand) , the lines
.Paste
Or
.PasteSpecial
Caused the WorksheetActivate code to spring in again ( causing VBA to go into an endless loop!! ) . So the purposes of those two code lines is to temporarily disable codes of this “Event” type.
_...........
_3d) You may not want to do this bit until you have the above understood. But for my later reference, I thought i would do it now while i am “here”
This involves a further method to do something similar to Copy Paste, should only values and no Format be required to be copied. You see this is a step further, as it does not look anything like what a Macro recording gives, but can be the most efficient method:
The basic idea is just to put the values of a cell or cells, into other cell or cells. So for example, once you have all the codes from this Post working, then make the changes to the codes in the normal module Thus:
For
Sub DetermineActiveSheetThenUpdateParticularSheet(), make the following changes shown in red ( which simply Calls the new codes:
And
Copy these two new codes to the normal module
_. These codes give the same result as in 3c) ( And note i did not seem to need the .EnableEvents = False “Bodge” for these codes – i expect the Copy Paste stuff somehow activates the sheet “internally” whereas the assigning Values does not. - Possibly the codes
Sub Makro2SHimpfGlified_3()
And
Sub Makro2SHimpfGlified_4()
( Which incidentally by pass the Clipboard ) may also not need the bodge – A bit of Homework for you to try maybe. – As always before trying any VBA code, save your worksheet just before. You have 3 possibilities then ( in this order ) to stop a VBA infinite loop – 1 Esc ; - 2 Ctrl + Break ; finally – 3 if all else fails, restart computer!!! ) ).
( Note before testing make sure you empty/ clear what was already copied to the AD100 cells in codes in 3c) )
_3e) As regards
Originally Posted by
pongmeister
.....and would be invoked from the new "Controller" macro If that can be done:...
_ .. if you have followed what is going on so far, you may see that each
Private Sub Worksheet_Activate()
Is effectively your "Controller" macro. So if you choose yourself to run ( “evoke” ) this "Controller" macro when required rather than it being “ evoked” by selection of the sheet, then rename one of those two codes to:
Sub Controller()
And then copy that to a normal module. And delete the two Private Sub Worksheet_Activate Codes.
Then when you are in any sheet, run the macro
Sub Controller()
Then it will perform the same results. I expect you could possibly think or say that this is “evoking “ the macro from any sheet, as you could say or think when you are looking at an ActiveSheet then you are “there” so you have “evoked” it from there. But this comes back to the original point snb tried to get across. He is an expert who really knows how VBA works ( very few people do ) and I expect what he was trying to say is that because of how VBA “works” , when you run a normal macro it “runs” – period! It does not run from “where “ you were – when you run a macro the keyboard strokes you make in order to do that are independent of, and have nothing to do with, what sheet happens to be Active – For example if you are selecting / “evoking” from the ribbon at the top, that is the only ribbon – you do not have a ribbon for each sheet ( I think!?)
_4) I answer this in the next post due to Post size limitations. !!
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks