Update a worksheet cell from a textbox in real time

Hi All

Sorry if there is a very obvious solution to this question that I am missing.

I have a userform that uses textboxes to change the values of cells in a workbook before running a series of macros from a commandbutton. I am using ControlSource to link the cells and textboxes but I have noticed that with this property the cell does not get updated until you move out of the textbox and select another textbox.

The problem is that if I press the commandbutton to run the macros while a textbox still has the focus, the target cell has not yet been updated (because I am still in the textbox).

Is there any way to update the cell in real time, i.e. so that it changes as you type into the textbox?

I know this can be done using the textbox change event, but this would mean writing change event code for about 300 textboxes (about 60 per page across 5 tabs in a multipage).

So, is there any other (less code intensive) way of making sure the last textbox entry is written to the cell before I run the macros?

Regards

Terry
TerrygordonAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
You don't need the sheet name in the Tag  - we can get that from the Parent.

In the attached workbook I created 12 textboxes on the first page with the tags for the range A1:B6.

I then copied those 12 textboxes to the other 2 pages.

The code looks at the name of the textbox's Parent for the sheet name and it's Tag for the range.
UFTextboxClassEx4.xls
0
 
Martin LissOlder than dirtCommented:
Sure, Just do something like this


Private Sub TextBox1_Change()
Sheet1.Range("A1").Value = TextBox1.Text
End Sub
0
 
NorieVBA ExpertCommented:
Terry

You could handle the Change event of all 300 textboxes using a class module.

Here's a small example with just 24 textboxes.

The Tag property of each textbox contains the address on the worksheet their data should goto.
UFTextboxClassEx3.xls
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Martin LissOlder than dirtCommented:
Terry, I'm sorry that I didn't completely read your question before I posted.
0
 
TerrygordonAuthor Commented:
Hi Imnorie

This looks good. How would I code it for each page on a multipage, where page1 accesses sheet1, page2 accesses sheet2, etc.?

Regards
Terry
0
 
NorieVBA ExpertCommented:
Terry

The code loops through all the controls on the form on initialize, so there doesn't really need to be separate coding for each tab.

The main problem I can see is determining the destination worksheet and range.

I've used the Tag property of each texbox to indicate the sheet and range but that was hard-coded at design time which might not be ideal for 300 textboxes.

You've indicated that there's a way to determine the worksheet destination Page1-Sheet1, Page2-Sheet2 etc.

That can be used in the code, we can get the page the texbox is on from it's Parent property and from that the sheet.

Is there anything similar for the range?
0
 
TerrygordonAuthor Commented:
Each page in the multipage has the same textboxes. The only difference is that tab 1 writes to cells on sheet1, tab 2 to cells on sheet2, etc. The destination cells are also the same for every sheet. E.g. the first textbox on multipage tab 1 links to H2 on sheet1, the first textbox on multipage tab 2 links to H2 on sheet2, etc.

Is it possible in the class module to set the textgroup change reference to a tag that contains a sheet and cell reference, rather than a worksheet reference? I.e. rather than accessing "Sheet1" and getting the cell reference from the tag, it gets both the sheet and cell reference from a tag such as 'Sheet 1'!H2.
0
 
TerrygordonAuthor Commented:
Brilliant!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.