Concatenate Text From Multiple Cells Into TextBox

  • I have a text document disaggregated into sentences each in different cells. I need a way of selecting different cells with a mouse, and through a Macro, have the text from those cells viewed in concatenated form into new textbox generated by the Macro.


    help?

  • Re: Concatenate Text From Multiple Selected Cells In textbox


    Sorry, a dropped my crystal ball and it needs to go to the shop? So I’ll have to ask some questions:


    Do you want the textbox on the worksheet or in a userform?


    Is the textbox created each time a series of cells are to be concatenated or is there a permanent textbox cleared each time?


    How will the textbox be cleared or deleted and rebuild when needed?


    Are the cell strings to be separate lines in the textbox or concatenated and word wrapped?

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Concatenate Text From Multiple Cells Into TextBox


    For an ActiveX TextBox on a Worksheet and an ActiveX CommandButton on the same sheet.

  • Re: Concatenate Text From Multiple Cells Into TextBox


    hi thanks for initial thoughts.


    Bill:


    1. Do you want the textbox on the worksheet or in a userform? - Textbox
    2. Is the textbox created each time a series of cells are to be concatenated or is there a permanent textbox cleared each time? - Each time, no permanent box. The idea is for it to be like a window you only get when you want to see text put together in different ways.
    3. How will the textbox be cleared or deleted and rebuild when needed? Open to suggestions. Probably another macro button to close this 'window', or maybe an event like doubleclicking.
    4. Are the cell strings to be separate lines in the textbox or concatenated and word wrapped? Lets start easy, just separate lines and we'll see how that goes.


    Dave:


    1. Does your code assume a permanent textbox1 is already created?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Dave,


    I get an error message:


    "invalid use of me keyword'

  • Re: Concatenate Text From Multiple Cells Into TextBox


    So where am i going wrong?




  • Re: Concatenate Text From Multiple Cells Into TextBox


    What error message shows? What happens?


    Always use Option Explicit. Did you select a cell range to insert into the box and then play the macro? What are the values of your offset cells?

  • Re: Concatenate Text From Multiple Cells Into TextBox


    yes, i selected a few random cells, pressed the macro button linked to that script, and this is
    what happens:


    first, error 438. object doesn't support that method error. message. close that popup and...
    second, then the textbox is created at the offset location specified in the code (ie textbox starting at bottom right of last selected active cell.


    Dave's code works if a textbox was already on the worksheet, but not with this code that creates a textbox.

  • Re: Concatenate Text From Multiple Cells Into TextBox


    You need to use the methods and properties for the object. The Compile button is your friend. It will tell you if you have syntax errors. Itellisense will show you what methods and properties an object can use.


    Recording an Autoshape or a Control Toolbox textbox would have given you something to work with.


    Modifying your code I would use:

  • Re: Concatenate Text From Multiple Cells Into TextBox


    Update: this code does work. just had to do a tweak and also have the textbox created after the code to combine the cell strings.


    [hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]thanks kenneth and dave for tips! all is sorted now. now for a different thread[hr]*[/hr][hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]quick followup.


    is there a .fontsize attribute i can add to change the fontsize of the created textbox?

  • Re: Concatenate Text From Multiple Cells Into TextBox


    In the attached file, clicking the button will display the selected cells in a box. Selecting another cell or cells will delete the box.


    The height and width of the box is automatically set by the text in the cells selected.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]In the attached file "DispalyBoxToggle" the box remains visible until you click the button.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Concatenate Text From Multiple Cells Into TextBox


    Thanks bill. Did you know how to change the font attributes in those text boxes?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]sorry found it. ".font.size ="