PDA

View Full Version : Populate a useform textbox with text from Control Box form



Kranky
10-23-2012, 11:17 PM
Hi guys

Newbie here (learning slowly VBA but new your site :bug: )

I'm having trouble with a form. The basic premise is I open the form which has a text box in it. I want the text box in the form to display the contents of a text box on the worksheet.

The contents then get altered and the new text is written back to the text box on the worksheet. I've tried oodles of code from the web with no joy so far.

The worksheet textbox is called TrainComments and the userform textbox is called TrainCommentsEdit

Any suggestions would be gratefully recieved.

Bob Phillips
10-24-2012, 12:21 AM
If you have the textbox on the worksheet, why not get the user to input into there directly?

Kranky
10-24-2012, 12:46 AM
Hi xld

Thankyou for replying / asking the question


This sheet has users that are a bit overwhelmed by it all, the area where the text boxes are is a dogs breakfast visually. I'm simply trying to make it easier to use for them. I have created forms and macros for most functions in the workbook but this is one that they struggle with so I thought I'd make it easier for them. Didnt make it easier for myself though as this bit of code just eludes me even though it sounds simple enough.

The worksheet is my first real foray into serious excel. I like to see what can be done with excel and vba, I am new and its the learning process as whatever code is produced will be studied and rehashed everywhere I can think it will benefit, just for the practice :) ...

Bob Phillips
10-24-2012, 12:58 AM
Sorry to belabour the point, but I think forms are a dog's breakfast. They are klunky, and take a lot of code. If I were in your shoes, I would re-design the worksheet (even getting rid of textboxes on that), as most users I know far prefer to interact with a well-designed spreadsheet than a userform.

Kranky
10-24-2012, 01:15 AM
Sorry to belabour the point,.
Don't be sorry, your opinion is appreciated.


but I think forms are a dog's breakfast. They are klunky, and take a lot of code..
Yes a lot of code and a lot of time to set up. Everyone's happy with the forms I have in place so far.


If I were in your shoes, I would re-design the worksheet (even getting rid of textboxes on that), as most users I know far prefer to interact with a well-designed spreadsheet than a userform.

I am loath to redesign the whole workbook. I inherited most of it and it isnt designed the way I would have, I've simply tried to make it present better ( which it does despite looking like the dogs breakfast I mentioned) and be easier to use.
Am I vbaing for vbaing sake? I dont think so and would like to resolve the issue. Am I stubborn? Perhaps :rotlaugh:

Kranky
10-24-2012, 01:52 AM
Perhaps I should qualify some aspects of the sheet.

The location of the text boxes is on a report sheet. The report sheet has 15 mini charts, a handful of std charts and some images of summary cells captured with excel camera. All this makes for a busy A3 printout but its readable when printed. To view the sheet on your monitor is hard and to find the text boxes requires scrolling across and/or up and down.

The workbook has a front page from where most things can be done with a button click so there is generally no need to wade through the report page before printing. to call a form to fill in the text boxes would save everyone a heap of headaches.

Putting the comments on the front page and have the image (or just the cell) linked onto the report page may be a better option per your initial suggestion :yes

Bob Phillips
10-24-2012, 02:23 AM
I will make one more argument against a form (I really do dislike them, as well as previous comments, they just look tired and old-fashioned). So I think you last idea is best, capture the relevant data on that front page and as you say link the input to the text boxes, that should be a simple implementation with no change of the existing worksheets. With highlighting input cells, locking and protection, you effectively have a form on a worksheet (but with data validation, conditional formatting, filtering et al all built in).

Kranky
10-24-2012, 02:28 AM
thanks for your time xld

Kenneth Hobs
10-24-2012, 06:37 AM
If your worksheet textbox is an ActiveX control, use the LinkedCell property. In the textbox control in the Userform, use the ControlSource property.

e.g.
Put a value in A1 of Sheet3 and hide the sheet. For the two property values put Sheet3!A1.