$$Excel-Macros$$ Other than Active Cell clear. ClearContents
Dear all, I have basic Query , i have to enter D: D column only, D2 has some vlaue , D8 has value i am going to enter cell D5 . if going enter Any cell of D Column above and below Cell should be ClearContents automatically . could you please help me . Chandru -- 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.
$$Excel-Macros$$ Need Help
I need Excel formula or VBA for the following. *) To set cell width(for headers). *) To make hyper link text blue. *) Font Formatting(for headers). Regards, Chandru -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Introduce Yourself !!
Hello All, I Am Chandru from Chennai and I work at Logitek. Regards, Chandru On Mon, Oct 6, 2014 at 12:04 PM, Vaibhav Joshi v...@vabs.in wrote: Hey Sanjay.. warm welcome.. Cheers!! + *I did not do this for you. God is here working through me for you.* On Sun, Oct 5, 2014 at 11:07 PM, sanjay kumar sanjay.kr2...@gmail.com wrote: hello, My name is Sanjay and I work at HCL in Noida. I use Excel, VBA and some other tools for daily tasks. Glad to see all the contributors and posts. Let the learning begin. On Saturday, June 9, 2012 12:51:59 AM UTC+5:30, Ayush Jain wrote: Hey all new and current posters, Welcome to excel group,one of the largest online community of excel Fans! I hope you enjoy your time here find this forum to be a friendly and knowledgeable community. Please feel free to post a small introduction, a friendly hello or tell us a bit about yourself. Why not tell us where are you from, what you do, what your interests are, how old you are, which is your favourite excel site or blog is or anything else that comes to mind! Thanks for your time Ayush Jain Group Manager Microsoft MVP -- 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 http://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 http://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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Multiple Find and Replace in Textboxes - Issue with multilines
Dear Roberto Mensa First, many thanks for your valuable help. (sorry for the late reply). The code was really helpful but with one issue. The code successfully splits the multi line test to individual texts. But there was some issue with the text replacement. I solved it with the following code. -- If Right(oTxtRng1, 1) = Chr(10) Or Right(oTxtRng1, 1) = vbLf Then oTxtRng = Mid(oTxtRng1, 1, Len(oTxtRng1) - 1) Else oTxtRng = oTxtRng1 End If -- Thanks a lot. Wish you and all a Merry Christmas and Happy New Year Regards Chandru On Fri, Dec 17, 2010 at 5:15 AM, roberto mensa robb@gmail.com wrote: I'm not sure of having understood, try: Sub MFR_Textbox() Dim Wks As Worksheet Dim rngSrch As Range Dim rngFind As Worksheet Dim rngFound As Range Dim oShp As Shape Dim oTotalRng As Variant Dim oTxtRng As Variant Dim oTxtRng1 As Variant Dim oTxtRng2 As Variant Dim intLength As Integer Dim intLineCount As Integer Dim intCnt As Integer Dim s As String Dim v On Error Resume Next Set Wks = Sheets(Sheet1) Set rngFind = Sheets(Glossary) For Each oShp In Wks.Shapes s = oShp.TextFrame.Characters.Text v = Split(s, Chr(10)) For intCnt = 0 To UBound(v) oTxtRng2 = v(intCnt) oTxtRng1 = Application.WorksheetFunction.Trim$(oTxtRng2) If Right(oTxtRng1, 1) = Then oTxtRng = Mid(oTxtRng1, 1, Len(oTxtRng1) - 1) Else oTxtRng = oTxtRng1 End If Cells(intCnt, 6).Value = oTxtRng Cells(intCnt, 7).Value = Len(oTxtRng) With rngFind Set rngFound = .Range(A:A).Find(What:=oTxtRng, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False) oShp.TextFrame.TextRange.Lines(intCnt).Text = rngFound.Offset(0, 1) End With Next intCnt Next oShp Set rngSrch = Nothing Set rngFind = Nothing Set rngFound = Nothing End Sub in the Excel object model, there are many problems for text into shape. regards r Dear Experts I am trying to find and replace the text from various textboxes (part of shape object) in sheet1 from the glossary in sheet2. The code works fine if there is single line of text in textbox. I am getting an additional carriage return when reading the text in case the textbox has multiple lines. This happens even after trimming the text. You can see that by running the code in the procedure named MFR_textbox. The problem is when there is an extra carriage return from the text picked, it does not return a match from the glossary. Any ideas on how to trim them additional carriage return. Any alternate suggestions are also welcome. I have attached the file along with the samples. Regards Chandru -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: On Time
Hi, Any help please Chandra Shekar wrote: Hi, When I run the program excel starts flicekring how to avoid this. Thanks Chandra Shekar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ How to attach file
Hi, How to attach file in this group Thanks Chandra Shekar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ VBA Code Required
Hi All, Can anyone help me providing the code for sending an excel attachment to be automatically updated in the Lotus notes in a Text format(Not as a Pictorial image or bit map. Thanks, Harish -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Openings in Bangalore
Hi I am looking for reporting job in Bangalore. Let us know if any. Chanra Shekar B 98459 76015 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Help: Removing duplicate data
Hi, See the attached file for details. Chandru On Thu, Sep 23, 2010 at 2:07 PM, janet dickson janetdicks...@gmail.comwrote: Hi All, Team, see attached file, please help me on how to remove duplicates in .xls file. .on love -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Copy of 2_DONExls.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Extract the text from Textbox and return back after editing
Dave/Paul Can you look into this one and help me. Regards Chandru On Sun, Sep 19, 2010 at 5:25 PM, Chandru chandr...@gmail.com wrote: Dear Experts I have now obtained the code the extract text from shapes and comments in a workbook to a new sheet with all the details of the shape object. However this code does not extract the text from the shapes that are grouped. I also got a piece of code that ungroups the shapes but not able to integrate it to the main code. Resending the text to the shape needs to be coded. If it is possible then the ungrouped shapes shall be regrouped Any help is greatly appreciated Regards Chandru On Thu, Sep 16, 2010 at 6:02 PM, Chandru chandr...@gmail.com wrote: Dear Paul Thanks for your reply. The code I got by googling is from http://excel.tips.net/Pages/T002388_Placing_Textbox_Text_Into_a_Worksheet.html -- Sub ExtractText() Dim shp As Shape Dim sLoc As String For Each shp In ActiveSheet.Shapes With shp If Left(.Name, 8) = Text Box Then sLoc = .TopLeftCell.Address Do Until Range(sLoc) = sLoc = Range(sLoc).Offset(1, 0).Address Loop Range(sLoc) = .TextFrame.Characters.Text .Delete End If End With Next End Sub -- The above code extracts the text from the text box and place it in the near by cell. The issue is that it is not extracting the text boxes that are grouped. I don't see any tracking (like the text box name) to send the text back to the text box. I have located a word macro that performs this text extract from power point files. I have attached that word template file for your reference. But this macro is too complicated for me to grasp the code and convert it for excel. http://www.volny.cz/ddaduc/werecat.html Any help is greatly appreciated Thanks Chandru On Thu, Sep 16, 2010 at 5:47 PM, Paul Schreiner schreiner_p...@att.net wrote: You didn't include your code. Did you want us to rewrite it too? the code you have for extracting the contents is very close to what is needed to replace it. Paul - Original Message From: Chandru chandr...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, September 16, 2010 7:03:28 AM Subject: $$Excel-Macros$$ Extract the text from Textbox and return back after editing Hello experts I wish to have an excel macro that can extract text from all the text boxes from one sheet and then list out that text in another sheet. I can find some macros for this from the internet. Now my requirement is to return back that text to the same text box after editing. I am not able to find macro for this after googling as of now. The first sheet contains the original text with some text boxes. Please note that some text boxes are grouped. Now a macro should extract the text and list that in the column B in a new sheet (or in a new workbook based on programmers convenience). I will now edit the text and type the same in column C. The editing work may take some time based on the amount of text. If possible, the address may be displayed in column A. After editing, I need another macro to send the text back to the same text box from where it was extracted. Hope I have explained my point. I have attached a sample excel file. If I am not asking for more, I would like the same for the comments in the cells also. All comments to be extracted to the new sheet after the text box text. Then editing will be done. Then it needs to be pushed back to the same place. Thanks in advance Chandru -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Extract the text from Textbox and return back after editing
My file contains various text boxes, shapes (rectangle, call out etc), comments and grouped shapes in the second sheet that is named Original sheet. Extraction of text is successful for the stand alone shapes and it is not extracting the grouped items. You can find out by running the macro named ExtractTextfromShapes. This macro will create a new sheet and dump all the datas along with the ID of the shape to send it back. I have started the code for returning it back but got stuck. Some sort of Find and replace code needs to be written and Vlookup may also be helpful. There is a small piece of code for ungrouping the shapes but I am not able to integrate it with the main code. Thanks Chandru On Mon, Sep 20, 2010 at 11:21 PM, Paul schreiner_p...@att.net wrote: Let's try again. Please post (or send) a file that contains your work so far, and with grouped shapes. Perhaps I can interrogate group members. Paul On Sep 20, 1:48 pm, Paul Schreiner schreiner_p...@att.net wrote: oops, nevermind. I think there is a file in a previous post. I'll look. Paul From: Chandru chandr...@gmail.com To: excel-macros@googlegroups.com Sent: Mon, September 20, 2010 6:47:24 AM Subject: Re: $$Excel-Macros$$ Extract the text from Textbox and return back after editing Dave/Paul Can you look into this one and help me. Regards Chandru On Sun, Sep 19, 2010 at 5:25 PM, Chandru chandr...@gmail.com wrote: Dear Experts I have now obtained the code the extract text from shapes and comments in a workbook to a new sheet with all the details of the shape object. However this code does not extract the text from the shapes that are grouped. I also got a piece of code that ungroups the shapes but not able to integrate it to the main code. Resending the text to the shape needs to be coded. If it is possible then the ungrouped shapes shall be regrouped Any help is greatly appreciated Regards Chandru On Thu, Sep 16, 2010 at 6:02 PM, Chandru chandr...@gmail.com wrote: Dear Paul Thanks for your reply. The code I got by googling is from http://excel.tips.net/Pages/T002388_Placing_Textbox_Text_Into_a_Works. .. -- Sub ExtractText() Dim shp As Shape Dim sLoc As String For Each shp In ActiveSheet.Shapes With shp If Left(.Name, 8) = Text Box Then sLoc = .TopLeftCell.Address Do Until Range(sLoc) = sLoc = Range(sLoc).Offset(1, 0).Address Loop Range(sLoc) = .TextFrame.Characters.Text .Delete End If End With Next End Sub -- The above code extracts the text from the text box and place it in the near by cell. The issue is that it is not extracting the text boxes that are grouped. I don't see any tracking (like the text box name) to send the text back to the text box. I have located a word macro that performs this text extract from power point files. I have attached that word template file for your reference. But this macro is too complicated for me to grasp the code and convert it for excel. http://www.volny.cz/ddaduc/werecat.html Any help is greatly appreciated Thanks Chandru -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Extract the text from Textbox and return back after editing
Hello experts I wish to have an excel macro that can extract text from all the text boxes from one sheet and then list out that text in another sheet. I can find some macros for this from the internet. Now my requirement is to return back that text to the same text box after editing. I am not able to find macro for this after googling as of now. The first sheet contains the original text with some text boxes. Please note that some text boxes are grouped. Now a macro should extract the text and list that in the column B in a new sheet (or in a new workbook based on programmers convenience). I will now edit the text and type the same in column C. The editing work may take some time based on the amount of text. If possible, the address may be displayed in column A. After editing, I need another macro to send the text back to the same text box from where it was extracted. Hope I have explained my point. I have attached a sample excel file. If I am not asking for more, I would like the same for the comments in the cells also. All comments to be extracted to the new sheet after the text box text. Then editing will be done. Then it needs to be pushed back to the same place. Thanks in advance Chandru -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Textbox text extract and return back.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Track changes inside text box in Excel
Dear VijayKumar Thanks for taking good interest in the problem and sharing your valuable knowledge and time. As you said, track changes does not work on any formatting changes. But I am not able to accept that keeping a text in text box and changing that text as a formatting operation. Basically I am not trying to change the font, size or any other format option. What I am trying is just edit the text inside the text box - say for example making a spelling correction. I am wondering how editing the text is being considered as a formatting work by Microsoft. Thanks for your information on using the text box as the active X control in the developer tab. It is fine for when creating a new text box. I am facing this issue when I get a file that contains lots of text in text boxes. (For some additional information, my work involves making correction in the excel file containing text in various cells and text boxes. To show the client what I have changed, I use track changes in word and that is perfectly working. When it comes to excel, track changes is ok with the cells but not with the text boxes. The file I get has lots of text boxes) Regards Chandru On Sat, Sep 11, 2010 at 1:03 PM, P.VIJAYKUMAR vijay.4...@gmail.com wrote: Hello, When you do track changes your workbook, Excel track and highlights the changes made by you or everyone (as set) in the workbook.Others cannot use and make changes or corrections if the work book is not shared with them. By activating track changes excel by default shares the workbook.But track changes does not work on any formatting changes i.e any formatting changes are there then track changes will not work on them. As keeping text box(normal text box form Insert shapes) and making changes in it is a formatting change and track changes does not work on formatting changes your text box is geeting locked to prevent it from being shred with other people and from making any changes. But , there is a solution to your problem. Don't Insert text box from Insert shapes.You also have a text box in active X controls which can be found in the Insert tab of the developer menu bar.If the developer menu bar is not shown on the menu bar you can activate it by selecting the show Developer bar in the Ribbon in the Popular Tab of the Excel Options Menu.For Using the active X Controls you have to have knowledge about Active X controls and their properties. Regards, P.VijayKumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Track changes inside text box in Excel
Dear Vijaykumar As said earlier, I get excel files (mostly from Japanese clients) and they have a tendency to use lots of text boxes to make a good looking table or some forms to collect data. The method used by them is Insert Text box Horizontal text box. The text boxes are NOT created using the active X control through the developer tab. In fact I have seen them doing that often. Now the problem is we cannot tell all the clients not to use text box and to merge the cells and center the text for heading. They simply draw a text box and enter the title, make the font big etc. It may be hard to believe for you that, it is very very hard to see an excel file without the usage of text box. Now keeping the attitude of text box usage by the clients aside, my academic interest is why such a wonderful application like excel lacks a simple user functionality of allowing the editing of text box when the track changes option is selected. I think the track changes option was originally developed for word only. Later they added this function in Excel 2007 though excel is not an application intended to text editing. Now a days people use excel for entering lots of text because of its versatility. The the need for checking the text in excel file and editing arises. I even checked in Excel 2010 for any improvements but still it is the same. I may be wrong or missing some settings. I am expecting some improvements in Excel 2013 or later (Just guessing the version). Once again thanks for your interest. Regards Chandru On Sat, Sep 11, 2010 at 9:23 PM, P.VIJAYKUMAR vijay.4...@gmail.com wrote: Hello, I want to know how you created text box in excel.In that only real problem lies. please tell me how you create the text box in excel. Regards Vijaykumar On Sat, Sep 11, 2010 at 7:56 PM, Chandru chandr...@gmail.com wrote: Dear VijayKumar Thanks for taking good interest in the problem and sharing your valuable knowledge and time. As you said, track changes does not work on any formatting changes. But I am not able to accept that keeping a text in text box and changing that text as a formatting operation. Basically I am not trying to change the font, size or any other format option. What I am trying is just edit the text inside the text box - say for example making a spelling correction. I am wondering how editing the text is being considered as a formatting work by Microsoft. Thanks for your information on using the text box as the active X control in the developer tab. It is fine for when creating a new text box. I am facing this issue when I get a file that contains lots of text in text boxes. (For some additional information, my work involves making correction in the excel file containing text in various cells and text boxes. To show the client what I have changed, I use track changes in word and that is perfectly working. When it comes to excel, track changes is ok with the cells but not with the text boxes. The file I get has lots of text boxes) Regards Chandru On Sat, Sep 11, 2010 at 1:03 PM, P.VIJAYKUMAR vijay.4...@gmail.com wrote: Hello, When you do track changes your workbook, Excel track and highlights the changes made by you or everyone (as set) in the workbook.Others cannot use and make changes or corrections if the work book is not shared with them. By activating track changes excel by default shares the workbook.But track changes does not work on any formatting changes i.e any formatting changes are there then track changes will not work on them. As keeping text box(normal text box form Insert shapes) and making changes in it is a formatting change and track changes does not work on formatting changes your text box is geeting locked to prevent it from being shred with other people and from making any changes. But , there is a solution to your problem. Don't Insert text box from Insert shapes.You also have a text box in active X controls which can be found in the Insert tab of the developer menu bar.If the developer menu bar is not shown on the menu bar you can activate it by selecting the show Developer bar in the Ribbon in the Popular Tab of the Excel Options Menu.For Using the active X Controls you have to have knowledge about Active X controls and their properties. Regards, P.VijayKumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes
Re: $$Excel-Macros$$ Track changes inside text box in Excel
The final conclusion is text box gets locked during track changes. Thanks for the info. On Fri, Sep 10, 2010 at 9:50 AM, P.VIJAYKUMAR vijay.4...@gmail.com wrote: Hello, You can use change tracking to log details about workbook changes every time that you save a workbook. This change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) can help you identify any changes that were made to the data in the workbook, and you can then accept or reject those changes. Change tracking is especially useful when several users edit a workbook. It is also useful when you submit a workbook to reviewers for comments, and then want to merge the input that you receive into one copy of that workbook, incorporating the changes and comments that you want to keep. How change tracking works Change tracking is available only in shared workbooks. In fact, when you turn on change tracking, the workbook automatically becomes a shared workbook. Although a shared workbook is typically stored in a location where other users can access it, you can also track changes in a local copy of a shared workbook. When changes are made in the shared workbook, you can view the change history directly on the worksheet or on a separate history worksheet (History worksheet: A separate worksheet that lists changes being tracked in a shared workbook, including the name of the person who made the change, when and where it was made, what data was deleted or replaced, and how conflicts were resolved.). Either way, you can instantly review the details of each change. For example, you can see who made the change, what type of change was made, when it was made, what cells were affected, and what data was added or deleted. When you use change tracking, you should consider the following: Change tracking differs from undo and backupYou might expect that change tracking creates a backup copy of the workbook before changes are made, or that you can undo any of those changes. Unfortunately, you cannot use the change history to back out of changes by undoing them or by restoring an earlier version of the workbook. However, the history worksheet includes a record of all deleted data so that you can copy that data back to the original cells in the shared workbook. Because change tracking is not designed to help you return to earlier versions of a workbook, you should continue to back up workbooks that have change tracking in effect. Some types of changes are not trackedChanges that you make to cell contents are tracked, but other changes, such as formatting changes, are not tracked. Some Microsoft Office Excel features are unavailable in shared workbooks, and therefore cannot be tracked. So when you track changes the workbook automatically becomes shared and text book is a formatting change and formatting changes are not tracked, so when you do track changes text box becomes locked.Hopes this clears your doubt. Thanks Regards, P.VIJAY KUMAR -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Track changes inside text box in Excel
Thanks for the reply. I have checked and the sheet is not protected. I can make changes in the cell and not in the text box. Excel is locking the text box when I enable the track changes as ON. (This is still not a macro related question, but I am posting that some one may throw light in this issue) On Mon, Sep 6, 2010 at 8:14 AM, P.VIJAYKUMAR vijay.4...@gmail.com wrote: HELLO, Your sheet may be protected or reject any changes in your sheet may be enabled. to make any changes remove the protection or accept the changes you make by using the accept or reject changes in the review menu. On Sat, Sep 4, 2010 at 7:50 PM, Chandru chandr...@gmail.com wrote: Hello I am enabling the track changes option in Excel 2007 (Review Track changes Highlight changes). The option allows to track the changes made inside the cells but when I try to edit the text inside the text box, excel is not allowing to make the change. Similarly it is not allowing to edit the comments. Basically it is locking the text box and comments. Any thoughts on this strange behavior of excel. Regards Chandru -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Pivot Table
Hi, In the below code I have vba code which get student wise report using pivot table. Here I need chart with report also. Thanks in advance. Sub pivot2_creation() Dim pvt_che As PivotCache Dim pvt_tbl As PivotTable Set Rng = Sheet1.UsedRange Set pvt_che = ThisWorkbook.PivotCaches.Create(xlDatabase, Rng) Set pvt_tbl = pvt_che.CreatePivotTable(Worksheets(3).Range(A1)) With pvt_tbl .AddFields Array(Stud Nm, Class, Kannada, English, Maths, Science, Social) End With With pvt_tbl .CalculatedFields.Add Marks Total, =sum(Kannada+English+Maths +Science+Social) .CalculatedFields.Add Percentages, =Marks Total/600*100 End With With pvt_tbl.PivotFields(Marks Total) .Orientation = xlDataField .NumberFormat = #,##0.00 End With With pvt_tbl.PivotFields(Percentages) .Orientation = xlDataField .NumberFormat = #,##0.00 End With On Error Resume Next For i = 2 To pvt_tbl.PivotFields.Count a = pvt_tbl.PivotFields(i).Name With pvt_tbl.PivotFields(a) .LayoutForm = xlTabular .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False) End With Next On Error GoTo 0 With pvt_tbl .PivotFields(Stud Nm).LayoutBlankLine = True .NullString = 0 .ShowDrillIndicators = False .ColumnGrand = False .RowGrand = False .InGridDropZones = False .PivotFields(Sum of Marks Total).Caption = Marks Total .PivotFields(Sum of Percentages).Caption = Percentage .TableStyle2 = PivotStyleLight17 .DisplayFieldCaptions = True End With With pvt_tbl pitm_cnt = pvt_tbl.PivotFields(Stud Nm).PivotItems.Count For j = 1 To pitm_cnt pitem = pvt_tbl.PivotFields(Stud Nm).PivotItems(j) For i = 1 To pitm_cnt pvtitem = pvt_tbl.PivotFields(Stud Nm).PivotItems(i) If pvtitem = pitem Then pvt_tbl.PivotFields(Stud Nm).PivotItems(pvtitem).Visible = True Else pvt_tbl.PivotFields(Stud Nm).PivotItems(pvtitem).Visible = False End If Next .TableRange1.Offset(2, 0).Copy add_sht (pitem) For Each pvtfld In pvt_tbl.PivotFields(Stud Nm).PivotItems pvtfld.Visible = True Next i = i + 1 Next End With Set pvt_che = Nothing Set pvt_tbl = Nothing End Sub Sub add_sht(ByRef pitem As String) ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count) ThisWorkbook.Worksheets(Worksheets.Count).Name = pitem With ThisWorkbook.Worksheets(pitem) .Range(A3).PasteSpecial xlPasteValuesAndNumberFormats .Range(A2:I2) = Array(Stud Nm, Class, Kannada, English, Maths, Science, Social, Marks, Percentange) .Range(A1) = Progess Report of pitem .Range(A1:I1).Merge .Range(A1:I1).HorizontalAlignment = xlCenter .Range(A1:I1).Font.ColorIndex = 45 .Range(A1).Font.Size = 15 .Range(A1).Font.Bold = True .Range(A2:I2).Font.Bold = True .Range(A2:I2).Font.ColorIndex = 55 .Range(A1).Select .Columns.AutoFit End With With Worksheets(3) Application.CutCopyMode = False End With End Sub Regards, Chandra Shekar B -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Re: $excel-macros$ help
Dear Kishan Reddy This is a very good tip and many thanks for sharing the same. I knew about excel clip board for many years but this idea of using Paste all to combine all sheets is really a magic. Thanks Chandru On Thu, Jul 22, 2010 at 11:36 AM, Kishan Reddy, K kishanreddy.kethire...@gmail.com wrote: Hello, Open the Office clipboard: From Menu select Edit - Office clipboard. Office clipboard will appear on the right side. Goto sheet 1 and select Data, and Press Control+C, Data will appear on Office Clipboard. Repeat the above step for all sheets. Then use the Paste All Option on Office clipboard. Then use clear all to clear all copies. This is the easiest and simple method. On Jul 21, 8:40 pm, preeti vora preeti86v...@gmail.com wrote: Hi team i have one excel file there is 10 to 15 sheet different but i want to marge in one sheet is that possible to do??? -- Regards, Preeti Vora. data.xls 24KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Hi Help in Excel
Hi All, May I kindly request you to help me out on the below attached spreadsheet query.. I need it today as i need to submit this tomorrow morg in my class.. please help me.. Thanks, Harish -- Forwarded message -- From: Vinu vinu1sm...@gmail.com Date: Mon, Jan 25, 2010 at 9:48 PM Subject: xl To: harish.chan...@gmail.com -- Regards Vinod Kumar Bangalore +91-9986423819 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Copy of excel test2003.xls Description: MS-Excel spreadsheet
