?
Solved

Update a worksheet cell from a textbox in real time

Posted on 2012-09-22
8
Medium Priority
?
542 Views
Last Modified: 2012-09-22
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
0
Comment
Question by:Terrygordon
  • 3
  • 3
  • 2
8 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38425008
Sure, Just do something like this


Private Sub TextBox1_Change()
Sheet1.Range("A1").Value = TextBox1.Text
End Sub
0
 
LVL 35

Expert Comment

by:Norie
ID: 38425012
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38425027
Terry, I'm sorry that I didn't completely read your question before I posted.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Terrygordon
ID: 38425127
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
 
LVL 35

Expert Comment

by:Norie
ID: 38425165
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
 

Author Comment

by:Terrygordon
ID: 38425200
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
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38425217
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
 

Author Comment

by:Terrygordon
ID: 38425249
Brilliant!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question