Re: $$Excel-Macros$$ Help required to use GetConcat(A13) & macro using
Here, give this a try.It was a simple matter to have it select the text box after completing. The other problem was because basically, there are 16,384 columns in each row.When you add or delete the row, you're "changing" 16,384 cells. so, the macro is running 16,384 times! I added a line in which I calculated the number of values in the header and only processed that number of columns. give it a try.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 Thursday, March 3, 2016 1:07 PM, big smile wrote: Respected Mr. Paul your idea of creating text box is really very nice .. But when i started using this i found two issues 1] When ever i change any cell -- crusor should automatically goes to that box only & not as normalas next cell or down ... 2] If i delete any raw or insert any raw then that file become hang. It stop works .. why it happen & what is the solution of this. When ever u have time kindly give some way for this On 3 March 2016 at 22:42, big smile wrote: 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 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 wrote: RespectedAll Last time some brothers havehelp to make this file perfect I want some clarification /suggestion on this file I have approx more than 1000entries to do in this file 1] When I start inserting lines © paste data from othe
Re: $$Excel-Macros$$ Help required to use GetConcat(A13) & macro using
Respected Mr. Paul your idea of creating text box is really very nice .. But when i started using this i found two issues 1] When ever i change any cell -- crusor should automatically goes to that box only & not as normal as next cell or down ... 2] If i delete any raw or insert any raw then that file become hang. It stop works .. why it happen & what is the solution of this. When ever u have time kindly give some way for this On 3 March 2016 at 22:42, big smile wrote: > 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 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 >> 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
Re: $$Excel-Macros$$ Help required to use GetConcat(A13) & macro using
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 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 > 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 copyrigh
Re: $$Excel-Macros$$ Help required to use GetConcat(A13) & macro using
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 wrote: RespectedAll Last time some brothers havehelp to make this file perfect I want some clarification /suggestion on this file I have approx more than 1000entries to do in this file 1] When I start inserting lines © 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 thenall the formula in D column change automatically as #value# Asshown in the screen shot 3] Ican not use Ctr Z – redo entry in this file it is due to macro 4] Iwant to use the formula of =GetConcat(A13) in the other files too But I cannot get the help command for this . so how to change in some details if I wantin this Or canu give some help to use this formula sothat 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, Formul
$$Excel-Macros$$ Help required to use GetConcat(A13) & macro using
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. MASTER ENTERY FILE - TRIAL.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
