+ Reply to Thread
Results 1 to 12 of 12

Help with formatting VBA code when concatenate 2 cells into a Txt box

  1. #1
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    5

    Help with formatting VBA code when concatenate 2 cells into a Txt box

    I am seeking help! Lol. I have the following, which I'm using to concatenate 2 cells into a text box. When the cell updates it also updates the txt box. However, the formatting is wrong when it concatenates the 2. B5 needs to be a number and C5 should be a percent. Does anyone know a quick solution for this?

    Current example: B5=50, C5=10%
    Answer = 500.10

    What I would like it to look like:
    Answer = 50 - 10%

    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
    Last edited by 6StringJazzer; 09-13-2019 at 02:40 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,761

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Welcome to the Forum ExcelNoob223 !

    Try this. I did not test it, but if you have trouble attach your file*.

    Please Login or Register  to view this content.
    *The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    5

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    I've attached my excel file as instructed. It partially works as it has % but no number.

  4. #4
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Quote Originally Posted by 6StringJazzer View Post
    Please Login or Register  to view this content.
    You could have also used VBA's FormatPercent function...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    5

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    This solution gives me " 50 - " and removes the percent and #.

  6. #6
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    103

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    ME BY THIS MACRO CAN HELP YOU
    Please Login or Register  to view this content.
    Last edited by salim ali; 09-13-2019 at 04:00 PM.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,761

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Quote Originally Posted by ExcelNoob223 View Post
    I've attached my excel file as instructed.
    I don't see it....

  8. #8
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    5

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Huh, I don't know why. I thought I followed the directions. But it doesn't seem to do anything after I click "close the window"

  9. #9
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Quote Originally Posted by ExcelNoob223 View Post
    This solution gives me " 50 - " and removes the percent and #.
    Are you referring to the code line I posted in Message #4? If so...

    1) What version of Excel are you using?

    2) What exactly is in cell C5 when that code line is executing?

  10. #10
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,761

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Quote Originally Posted by ExcelNoob223 View Post
    Huh, I don't know why. I thought I followed the directions. But it doesn't seem to do anything after I click "close the window"
    Sometimes this happens if the file exceeds the size limit. It's not obvious. .xlsx files can be up to 1MB. If you zip it, it can be 9.77MB.

  11. #11
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    5
    1) What version of Excel are you using?

    2016

    2) What exactly is in cell C5 when that code line is executing?[/QUOTE]

    It will be just a % like OI as a % of total sales. I will use this to have a txt box update in my graph. Im trying to automate this process to update as new data is pulled from Hyperion Planning.

  12. #12
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Help with formatting VBA code when concatenate 2 cells into a Txt box

    Given your version of Excel and the fact that you have a number in cell C5, I can see no reason why the code I posted in Message #4 would not have worked for you. Given that, I don't know what to tell you other than to use one of the other solution if it works for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1