none
Textbox greyed out RRS feed

  • Question

  • Hallo Everyone,

    I want to use a "minimal" Excel sheet to gather some data from users (i.e. no userform and no macros, simple xlsx) and need to add a text-box linked to a cell. This should not be ActiveX (i.e. I want to not add any processing code).

    Anyway, on the Form Controls menu (under developer), I can select and add some controls (e.g. checkbox) and link these to cells.

    I would like to do this same for a textbox but in the Form Controls textbox (and two other controls) are greyed out.

    I have spent an hour googling on this and have tried and checked all kinds of things (e.g. I have tried with a completely fresh, unprotected, unshared sheet) but the textbox is still greyed out.

    Arrggghhh!

    Can anybody help me? Or is it not possible? I hope that there is a simple explanation.

    Many thanks,

    Alan

    Thursday, May 9, 2019 6:35 AM

Answers

  • Alan,
    re:  textbox on worksheet

    I believe you are stuck with using an ActiveX textbox on a worksheet.
     1. Click Design Mode button on the Developer tab.
     2. Insert ActiveX textbox (click the icon and draw it on worksheet).
     3. Right-click the control and select Properties from the menu.
     4. Enter the cell address in the LinkedCell property (see picture - B2 was entered)
     5. Click the X in the top right corner to dismiss the properties window.
     6. Click the Design Mode button again (to return to normal).

    '---


    Custom_Functions add-in (19 new functions)
    Download from MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    Thursday, May 9, 2019 12:01 PM

All replies

  • I think that the reason is is that text controls are only enabled for "Dialog-Forms" (a relic from Excel 5.0). It worked when I created one of these.

    But this is extremely irritating because a textbox control, linked to a particular cell, is exactly what I need: I could then position the textbox (flexibly) and would know that whatever was written there would go into a particular cell which I could then access to "harvest" the data. This is possible with a checkbox but not with a textbox.

    I am bemused because I am certainly not the first person who would like to do this.

    Or is there something that I have missed? It would be great if someone has a trick that would allow me to do this.

    Many thanks,
    Alan

    Thursday, May 9, 2019 10:15 AM
  • Alan,
    re:  textbox on worksheet

    I believe you are stuck with using an ActiveX textbox on a worksheet.
     1. Click Design Mode button on the Developer tab.
     2. Insert ActiveX textbox (click the icon and draw it on worksheet).
     3. Right-click the control and select Properties from the menu.
     4. Enter the cell address in the LinkedCell property (see picture - B2 was entered)
     5. Click the X in the top right corner to dismiss the properties window.
     6. Click the Design Mode button again (to return to normal).

    '---


    Custom_Functions add-in (19 new functions)
    Download from MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    Thursday, May 9, 2019 12:01 PM
  • Great suggestion: I will use ActiveX objects. Many thanks.

    I had wanted to avoid them because I wanted our form to be "Total Vanilla Excel" without any risk of recipients not being able to open or work with it. But I suppose ActiveX is supported everywhere now(?)

    The one problem that remains is that the text in the control is somehow stretched and overlapping. It's readable but looks very strange. When you type the text (or return to the box to edit) it looks fine. But as soon as you tab away, it goes all "stretched" again.

    I tried a range of different fonts (e.g. Arial) and different settings but I can't seem to get the font to be "normal". Any ideas for this second problem?

    Yours,
    Alan

    Thursday, May 9, 2019 5:15 PM
  • Alan,
    re:  textbox on worksheet (2)

    Glad you got it working (somewhat).
    The problem (text is stretched) you describe is not one I have seen before.
    The only thing I can suggest is to experiment with the texbox TextAlign and Wordwrap properties.
    You also might check the format settings for the linked cell.  Good luck with it.
    Thursday, May 9, 2019 5:34 PM