Is it possible to make a cell display, or equal, a value typed into an ordinary text box?

[Atlantis101]

Hello,
I would like to be able to display a value, typed into an "ordinary" text box, in a cell. I can see how I can click into a text box and type into the formula bar to make the text box equal a specific cell (eg. "=A1"). Whatever is typed in A1 then appears in the text box. It is the opposite, or reverse of this that I wish to achieve. Whenever I type a numeric value into a text box, I want that value to appear in a designated cell. The text boxes will be used for numeric (in mm) input by the user of geometric dimensions on to a schematic engineering drawing inserted as a picture (jpg) within the sheet. Twenty or thirty of these text boxes would be used in this way, with the cell values being subjected to further formulae and calculation before final display of the results in a table. So, the text box values will be for future print-out of the drawing along with its dimensions, the cell values necessary for onward calculation and print-out of derived geometric results. I know userforms may be one way to achieve this, but I cannot see a way of rotating the text boxes for correct orientation against my schematic engineering drawing. Also, the sheet with the drawing and all text boxes already exists, so this is by far the most desireable way for me to proceed, if possible. Thankyou in advance for any help or advice anyone can give.

[Leith Ross]

Hello Atlantis101,

Welcome to the Forum!

A regular TextBox, one that is created using the Drawing Toolbar, can be orientated either horizontally (0 Degrees) or Vertically (90 Degrees). No other positions are available. These are usually used to display text that will be mostly static. They are not really designed for user interaction. An ActiveX control would be a better choice.

It would be beneficial to see a workbook with a few before and after examples. If your workbook contains any sensitive information, please redact it before your post the workbook.


To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[martindwilson]

don't the text boxes from the control toolbox work that way around?

[Atlantis101]

Hi Leith & Martin,

Thankyou for your interest and trying to help me.

To recap, I am trying to use regular (Drawing Toolbar) Text Boxes to input some dimensions, and have these values appear in cells that I can use for onward calculation. There are two reasons why this is desireable:
Firstly, the technical / engineering drawing along with the relevant Text Boxes, already exists within an Excel Workbook. The dimensions are simply typed into the boxes and a printout is made for handout and study. Any calculations thereafter are done with a calculator and pen and paper. It would be great to have the whole process automated.

Secondly, due to the nature of dimensioning a drawing, about half of my Text Boxes are horizontal and half vertical. and it would be good if I could keep it that way.

Leith....you mention that Drawing Toolbar Text Boxes are not really designed for user interaction. The only interaction I require is the transfer of its contents to a cell. Am I correct in understanding now that this is simply not possible?

Martin....you ask "don't the text boxes from the control toolbox work that way around"? Yes, they do...but I can't find a way of orientating them vertically. If I could, my problem is solved. Maybe the question I should be asking is....How do I rotate or orientate Control Toolbox (Active X) Text Boxes vertically"?

It may be that I'll have to make a compromise. If there is no way of orientating Control Toolbox (Active X) Text Boxes vertically, I could still use them but have them all appearing horizontally on my drawing.

Please accept my apologies for a long post, and I appreciate how difficult it must be to "imagine" what I'm trying to do. I thought I would try with dialogue first, but if need be I will rig up a couple of example workbooks and upload them...a picture paints a thousand words!

In Summary....I need a Text Box that can output to cells AND be rotated to a vertical position....

....Can a Drawing Toolbar (regular) Text Box have its text or numeric value "transferred" to a cell?

&

Is it possible to orientate a Control Toolbox (Active X) Text Box vertically? Any of these two scenarios would solve my problem.

If neither of these is possible, the best way forward for me will be to compromise and use all Control Toolbox (Active X) Text Boxes horizontally.

Again...your interest and help is much appreciated.

[Leith Ross]

Hello Atlantis101,

The Drawing TextBox will work perfectly for what you to do. Here is a macro you can assign to each TextBox. You will need to right click each TextBox, click Format TextBox... > Web > Alternative Text and enter cell's address where you want the text displayed. Add a new VBA module to your project and paste the code below into it. You will then need to right click each TextBox again and assign this macro to them.

Please Login or Register  to view this content.

[Atlantis101]

Hi Leith,

Thanks for your swift reply...1st class. Things are looking up, then. You reckon I can do this with a macro assigned to each Drawing Tool Text Box and youv'e even given me the code...thankyou. The bad news is that I am totally new to Visual Basic (only started reading a little about it last week), and so it is going to take a little while for me to learn how to do what you're asking. (It's only easy when you know how!)

I just had a go at adding a new module and pasting your code into it. Assigning it to each Text Box in turn I think I have also managed. However, when I right click a Text Box, the only path option I have is Format Shape... > Alt Text and not Format TextBox... > Web > Alternative Text as you specify. So I can see nowhere to enter my cell reference.

If you can think what I may be doing wrong, great...if not, I will endeavour to read up a bit and experiment, and post again in a few day's time.

Kind regards and thanks again,

Atlantis101.

[Leith Ross]

Hello Atlantis101,

I really do not have an answer for why your context menu does not show Format TextBox... when you right click on it. Can you post a copy of the workbook for review?

[Atlantis101]

Hello Leith,

I have created and uploaded a workbook which will hopefully explain clearly what I am trying to do.

Regards and thanks,

Atlantis101

[Leith Ross]

Hello Atlantis101,

From the picture in your the workbook, it appears you have added a Rectangle Shape to your worksheet and not a TextBox. This fits with the context menu shown. To add a TextBox you need to activate the Drawing Toolbar. There are 2 icons for the TextBox: Horizontal and Vertical.

[Atlantis101]

Leith...I inserted these boxes by clicking Insert Tab > Insert Text > Text Box. When I do this the ribbon has a tab or group that changes to Drawing Toolbar. The Name Box identifies the box as a Text Box also. This made me think that I was inserting a Drawing Toolbar Text Box. But I think you must mean something slightly different. If I right click to customise the ribbon, I think I can see what you mean....under Drawing Tools > Format > Insert Shapes > Text Box I can see command buttons for horizontal and vertical Text Boxes but they are greyed out. Trying to make them appear on ribbon but no joy as yet.

[Atlantis101]

Hi Leith,

I have tried to customise my ribbon so that I can select Horizontal and Vertical Text Boxes from it. I created new custom groups to do this….the Horizontal Text Box command seems ok, but the Vertical Text Box command is greyed out.
However, when I insert a Horizontal Text Box from this command button, then right click it, I still get Format Shape in the context menu.
Could this be a version (Excel 2010) issue rather than anything I am doing wrong? Your thoughts greatly appreciated.

[JosephP]

Leith's code should still work for you using the format shape and alt text

[Atlantis101]

Hi JP,

If I go Format Shape > Alt Text, there are two fields..."Title" and "Description". Do I enter a cell reference into one of these? Thankyou.

[Atlantis101]

Hi JP,

If I go Format Shape > Alt Text, there are two fields..."Title" and "Description". Do I enter a cell reference into one of these? Thankyou.

[JosephP]

ayup - in the description field

[Leith Ross]

Hello Atlantis101,

I have attached a file that describes how to add a text box and modify it for 2010. Perhaps there is some information that will help you with this problem.

[Atlantis101]

That is brilliant. Just tried Leith's code as you suggested JP and it is a great job. I am so grateful, but if you don't mind....before I sign the thread off as solved (which technically speaking it now is)...I have a couple of final questions.

1......I notice that I have to right click, and then left click into a text box to edit it. Is it possible to be able to go straight in with a left click?

2......Upon exiting the text box, it is necessary to hovver over the box, the mouse pointer changes to a "hand", and a left click then transfers the value to the designated cell. Is it possible to have code that transfers this value as soon as the text box is clicked out of (exited)?

Please don't think I'm being lazy or ungrateful. But when I design my engineering drawing sheets now, they are going to be edited very, very frequently by several different users/engineers, and I want to try and cut down on the risk of a mistake being made (eg. a user changing the value in a text box but forgetting to hovver over it and "send it" for calculation with another click).

Even if I can't be helped any further, I am very, very pleased with the solution I now have. Thank you.

[JosephP]

you could alter the code so that it prompts for the text

Please Login or Register  to view this content.

[Atlantis101]

Hello again, Leith,

Thanks very much for the Word doc and information. As you will be able to see from my last post, following advice from JP, your code works brilliantly (using Format Shape > Alt Text and entering my cell reference in the "Description" field. It didn't look like a place you should be typing a cell reference, but it obviously is and works great. I have asked a couple of final questions, which, if solved, would make for a completely finished job for me...my workbook and sheets could get designed exactly as I envisaged. Thanks everyone so much.

[Atlantis101]

Hiya JP,

That code you gave me does exactly what I was after. It makes the data input totally user friendly and will minimise the possibility of mistakes...thankyou.

I will leave this thread open for a day in case anyone has any further comment, and then will be marking it as well and truly SOLVED.

For now, to Leith, Martin and JP.....CHEERS and THANKS EVER SO MUCH!