Accessing user-input data in Excel text box. 2

[Pyrrhus]

Can anyone help, please. I want to copy the content of a text box which is filled in by the spreadsheet user, and copy the data into a cell (is that the way to do it?) in a separate sheet. There are actually a number of these text boxes to record user comments. I have given each text box a name by keying it into the left-hand top name field, but the names list doesn't seem to recognise the names. Can this be done without VBA? (The text boxes are scattered over 40 sheets, and I'm trying to condense the info by storing a copy of all comments into a single compact sheet for transmission back to "base".)

Thanks.

 

[Loomah]

You can used the LinkedCell property of the textbox.

Right click on the textbox and choose view properties (you'll need to be in design view to do this). Enter the reference of the cell you want the data to go into in the empty box next to "LinkedCell" - this will need to include the sheet name (or you could name all your destination cells and used the named range)

This will be no good if you need to have many people's comments in one workbook, at that stage I think you'll need to be looking at VBA to select the next cell.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Pyrrhus]

Thanks, Loomah, that works just fine. Well worth a star!

But maybe you (or someone else) can help me a bit further. I have 400 or so text boxes already set up. When I right click on any one of them in design mode, no properties option appears. The properties box contains the sheet properties. However, if I delete the box and draw a new one (which is a bit of a drag for 400 of them) the properties box displays the text box properties OK. Bit of a puzzle. Maybe you can shed some light.

Thanks again.

 

[Loomah]

Sorry, I'm at a loss to say what's going on here. I've never come accross that problem and can't recreate it.

Try opening the "Control Toolbox" command bar and select properties from there. Again you will have to be in design mode to even be able to select the Textbox, otherwise you'll only get the sheet properties.

Just one question - did you get the linkedcell to work?
sorry 2 questions - if so, how did you do it if you couldn't access the properties?

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Nikita6003]

Question: d'you get Format Control ... as one of the options when you right-click those textboxes? If so, you've got FORMS textboxes ... these DO have properties, but the porperties can't be viewed using the property window (they're available in VBA, tho)

If you DON'T see Format Control - is your sheet/workbook protected? Is the textbox enabled?

HTH! Good luck

Cheers
Nikki

 

[Loomah]

Nikki
Don't want to split hairs but there is no textbox on the forms toolbar but the earlier version the editbox!

Anyway, that's not my point here. Checking that option was one of the first things I considered but the edit box option wasn't available to me from the forms toolbar in a worksheet environment.

Is this normal or do I have a problem (no matter how insignificant it may actually be)? Anyone??

Using XL2k

Good call re protection!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Nikita6003]

You're right about the edit box - can't use that in a sheet, anyway

One more thing that u might try: double-click the text box to get you to the VBA editor; check if the PROPERTIES window is visible there (it should also be available at design-time). If not, press F4 to get is up & running

And if *that* doesn't work - you may indeed have a problems ...
But I'll ask around the team here

Cheers
Nikki

ps - LOOK what you did to my hair!

 

[Loomah]

Nikki
Hair looks good!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Pyrrhus]

G’day Loomah and Nikki!

First Loomah:
Last part of your post first: I could get the linked cell to work because I deleted the existing text box and drew another from the control toolbar, and the properties box appeared OK for the newly created box. My problem is that I don’t really want to replace 400 boxes, but it’s not such a big deal to delete and paste, just tedious and error prone. I’d have to go through them all to link them anyway.

First part of your post:
I tried using the Control Toolbar. If I select the (original) text box, the Design Mode and Properties buttons are blanked out. If I select the sheet, they come to life but the Properties box shows the sheet only and doesn’t change if I then select the box. But as I said, works OK with the replacement box.

Now Nikki:
Don’t get Format Control as an option when I right click on the text box, just Format Text Box. No, the sheet is not protected and the textbox is enabled (ie. It will accept input). However, I have unprotected the sheet for development / testing purposes. The sheet is normally protected in “production” mode, but the text boxes (for user input) are enabled (and I guess unprotected? by definition). So maybe I’ve done something I can’t remember to enable the text box in a protected sheet.

Thanks to you both. Help much appreciated. Any other thoughts very welcome. I'm all ears!

Kevin.

 

[Nikita6003]

Hi Kevin - all I can think of now is: try accessing the textbox properties trough the VBA properties window. Just open VBA, go to properties, and see if you can find the 400 or so textboxes in the dropdown list

What you could also do is:
use VBA to loop through the TextBox collection & set the LinkedCell property

Dim l_txtTextBox as TextBox

...
Dim l_txtTextBox As TextBox

For Each l_txtTextBox In ThisWorkbook.Sheets(1).TextBoxes
  l_txtTextBox.LinkedCell = l_txtTextBox.BottomRightCell.Offset(0, 1)
Next l_txtTextBox

Let us know how you get on - I'm still all hair

Cheers
Nikki

 

[Pyrrhus]

G'day Nikki,

Thanks again for your help. I'm in your debt. I'll definitely try the linking loop you suggested. I have to go ahead without the text boxes right now but I'll come back and clean up that problem later.

Thanks again,
Kevin.