Thank you very much for explaining in details. God Bless you & all members of our wonderful groups.
Excel can do so much wonders that we can not have think it before. On 3 March 2016 at 19:10, Paul Schreiner <schreiner_p...@att.net> wrote: > I can make the changes, but you will still need to rely on others to make > changes! > > I'll try to explain: > > Macros have "scope": > That is: Macros (Subroutine or Function) are stored in VBA "Modules" > There are several types of Modules. > The most common are "Sheet" modules, "Standard" modules and "Form" modules. > > Macros written as Functions can be used in the Excel sheet. > > A macro stored in a "Sheet" module is only available to the specific sheet. > That means that you can store a function called "Test" in the Sheet1 > module and another in the Sheet2 module and they can do different things. > If fact, if you had a function called "Test1" in the Sheet1 module, you > cannot use it in Sheet2. > Because the "scope" is restricted to the sheet. > > Now, if you take that same function and put it in a "Standard" module, it > will be available to all sheets IN THIS WORKBOOK, but not in other > workbooks. > > If you were to define the function in the Standard module as: > > Public Function Test1() > > Then the function is available to all workbooks open in the current Excel > "instance". > (it is possible to open Excel once (an instance) and have multiple > workbooks open in this instance, or open excel multiple times, with one > workbook in each instance) > ------------- > > Whenever you do something in Excel, an "event" takes place. > You open an Excel workbook, select a sheet, select a cell, change the > cell, save the workbook, Close the workbook. > > Many of these tasks have an associated macro called an "Event" macro. > that means that when you change a cell on a worksheet, the > Worksheet_Change() event macro executes. > As with other macros, an Event macro stored in the sheet module only > applies to the specific sheet. > > there's a different version of the sheet macros called things like > "Workbook_SheetChange" > that would apply to ALL sheets, so that you don't have to duplicate the > macro in each sheet. > > '---------------------------------------------------- > > So... in your workbook, someone created an Event macro in your "Policy" > sheet. > You can look at it by right-clicking on the "Policy" tab and selecting > "View Code". > > Your original Change event macro simply updated the merged cell at > location C25(?) > If you're adding 1000 rows, this macro will cause some problems for you. > > Instead, I inserted a text box, named it "TextBox_Current", and changed > the macro to update this text box. > Now, you can insert additional records. > > I also changed the event macro to use the GetConcat function to put the > summary value in column D. > > > *Paul* > ----------------------------------------- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > ----------------------------------------- > > > On Wednesday, March 2, 2016 2:03 AM, big smile <bigsmile...@gmail.com> > wrote: > > > > Respected All > > > Last time some brothers have help to make this file > perfect > > > I want some clarification / suggestion on this file > > I have approx more than 1000 entries to do in this file > > > 1] When I start inserting lines & copy paste data from other > files > then it does not work properly --What is the logic of > this ? > > > 2] When I make the entry in this file & some other files open > at the same time then > all the formula in D column change automatically as #value# > As shown in the screen shot > > 3] I can not use Ctr Z – redo entry in this file it is due to > macro > > 4] I want to use the formula of =GetConcat(A13) in the other > files too > But I can not get the help command for this . so how to change in some > details if I want in this > Or can u give some help to use this formula so that I can modify the > details as per my requirement. > > > > Thanks a lot & God Bless you all .. > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at https://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at https://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
