$$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 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 > 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&
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 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=wall&ref=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=wall&ref=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=wall&ref=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=wall&ref=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=wall&ref=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 wrote: > 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
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 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 wrote: > > oops, nevermind. > > I think there is a file in a previous post. > > > > I'll look. > > > > Paul > > > > > > > > > > > > >From: Chandru > > >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 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 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
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 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 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 > 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 > >>> 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
$$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 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 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 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 >> 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. >> > >> > >> > >>
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 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
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 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
Dear Vijayakumar Thanks for the reply. My point is that when I enable the track changes option in the Excel 2007 (Review > Track changes > Highlight changes), the editing of text box is locked by excel. (I am not sure if I have conveyed the exact point). Here I am not talking about the worksheet protect or unprotect in the shared work book. But I also tried changing the option "Protect Shared Workbook" and yet excel is not allowing the editing of text box, when I enable the Track changes > Highlight changes. Can you please try to edit the text in the text box after doing the following. Click Review > Track changes > Highlight changes. Then the "Highlight changes" form will open. Check "Track changes when editing". Further there will be "When", "Who" and "Where". You may keep the default options and click OK. Now try editing the text box. Thanks for your valuable time. Regards Chandru On Thu, Sep 9, 2010 at 12:41 PM, P.VIJAYKUMAR wrote: > Hello, > In the protect and share work book in the review menu the allow sharing > option was selected .When I deselected the option I was able to make > changes in the text box. I am attecing the cheged sheet for your reference . > > Regards, > P.Vijaykumar > > > > On Thu, Sep 9, 2010 at 11:40 AM, Chandru wrote: >> >> 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 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 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
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 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 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$$ Track changes inside text box in Excel
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 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 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
