none
vba code automatically bold and underscore text RRS feed

  • Question

  • Hello,

    I'm a completely new with VBA, and have very limited experience in coding beside college projects. I ran into an issue that need your help. I wanted to search for the content of a cell (in this case, "C6") within the whole spreadsheet, and then replace the results with a line of text to be entered in an InputBox. I made this because this spreadsheet has so many text boxes, and they can't be changed using the normal find and replace (Ctrl + H) function.

    However, when I execute the module, the texts in places where the search function found them change their format. The changes were very random, some text boxes were bold and underscored, some of them were just bold, and some changed color of the replaced text (i.e. the new text doesn't have the same color as the old one.) Is there a way that I run my module without it changing the format of my spreadsheet? I have no clue what to do or how the changes happened.

    Any help would be greatly appreciated. 

    Below is my code:

    Sub Replacetext()
        Dim shp As Shape
        Dim sOld As String
        Dim sNew As String
        Dim wks As Worksheet
        sOld = Worksheets("PFC").Range("C6").Text
        sNew = Application.InputBox("Enter new text", Type:=2)
        On Error Resume Next
        For Each wks In ActiveSheet.Parent.Worksheets
            For Each shp In wks.Shapes
                With shp.TextFrame.Characters
                    .Text = Application.WorksheetFunction.Substitute( _
                      .Text, sOld, sNew)
                End With
            Next shp
        Next wks
        For Each wks In ActiveWorkbook.Worksheets
            wks.Cells.Replace What:=sOld, Replacement:=sNew, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Next wks
        
    End Sub

    Thursday, March 9, 2017 3:50 PM

All replies

  • Hank N,
    re:  using the Replace function

    It appears xl2016 added  the SearchFormat and ReplaceFormat options to the Replace function.
    I don't have xl2016 to actually check,  They are not there in xl2010.
    To put it mildly, xl2016 has problems.  You may be seeing one of them.

    Many arguments for functions are optional, try omitting the two 'format' arguments and see what happens.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Thursday, March 9, 2017 7:12 PM
  • Hi Hank N,

    I try to test your code on Excel 2016 and it's working correctly.

    I try to replace "abc" with "ab".

    output:

    you can see that there is no bold or underscore or different color.

    here I want to ask you that is there is any other code in this workbook that get execute on worksheet_Change event or something like that?

    if so then try to check for it and correct it.

    if your workbook contains only this code and if possible for you then try to upload your workbook and post link here. we will try to test it on our side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 10, 2017 4:57 AM
    Moderator
  • Sorry for the late reply. Thank you for your help! The text spreadsheet I'm using is pretty big, and contains many textboxes, all filled with different format and colors. Unfortunately the spreadsheet is confidential so I cannot show you. The code is identical. It took me about 30 seconds to execute this macro. Have you heard of anything similar to that?

    • Edited by Hank N Monday, March 13, 2017 12:33 PM
    Monday, March 13, 2017 12:31 PM
  • Hi Hank N,

    we don't need your confidential data.

    but if you provide any demo workbook that can reproduce the issue on our side then we can try to correct the problem.

    as I already tested your code on my side , and you can see the result it's working properly.

    so without looking in to your original code we can't suggest any solid suggestion to solve the issue.

    then also you can try to check that when you replace the value at that time any other code get execute or not.

    like any code written on worksheet_change or something similar like this, or any code that you had written for formatting the value of cell.

    the other thing I want to ask you that , is there is any code that copy the data from that textboxes to sheet with the formatting like color and bold , italic?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, March 14, 2017 6:15 AM
    Moderator
  • I'll update you with pictures of what actually happens on my end once my account is verified. Thanks Deepak!
    • Edited by Hank N Wednesday, March 15, 2017 5:41 PM
    Wednesday, March 15, 2017 5:41 PM
  • Hi Hank N,

    you can try to upload pictures and demo file on any free file hosting websites.

    then you can put the link as text in your post.

    then we can visit the link and can access the pictures and files.

    and try to test on our side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 5:31 AM
    Moderator
  • Thank you for your suggestion, Deepak. 

    --------------------------------------------

    Here is an example of the results of my macro on my end (no confidential data in my example.)

    The "LAXXX" in red at the top is in a text box, and everything in the "introduction" box is also in a different text box. However, the "LAXXX" text box doesn't change the format, whereas the "Introduction" box completely changed the format. There are many other places where the code randomly color all the text in the cell (but not underscore), and some work as intended. Again, the code is identical to what I have in my spreadsheet, just copied and pasted it here.

    I hope this help explain my issue. Thank you again for your help! I really appreciate it.

     -------------------------------------------------------------

    Before (This is the way it should look. I know it's totally not the optimum way to make the spreadsheet at all, but it was made a long time ago by someone else, and some parts of the spreadsheet do need to have text boxes.):

    http://imgur.com/qHbnQpE

    After executing the macro to change the content of "LYXXXXXXX" cell: 

    http://imgur.com/VlxCUAM

    Continue to execute the macro to change content of "LAXXX" cell:

    http://imgur.com/UNQ5dMR

    Thursday, March 16, 2017 11:29 AM
  • Hi Hank N,

    what if you try to remove all the colors and formatting manually and then again try to run the code.

    if you did not test that then I suggest you to test it.

    also want to confirm with you that there is no any other code available in the workbook.

    just for testing purpose try to create a new workbook and test the same code on that workbook and let me know about the results.

    if you find the same result then try to remove all your confidential data from the workbook and fill it with dummy data and try to post your workbook.

    we will try to test it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 17, 2017 7:52 AM
    Moderator
  • Hello Deepak,

    I'll test that when I get to work today. I'll try to make time to create a dummy spreadsheet to post here. There is no other code in the workbook.

    Thanks!

    Hank

    Friday, March 17, 2017 10:23 AM
  • Hi Hank,

    if you try to make a test then please try to post the testing result with dummy spreadsheet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 20, 2017 9:29 AM
    Moderator