Textbox loses focus?

[mike0123m]

I wasn't sure what to call this thread. I have a userform with several textboxes on it. The info that is typed into the textbox is copied into a cell in a spreadsheet where it is used to perform some calculations. After the single line of code to make that work, I have a reference to a sub in a module that will load the results to the userform. Every time a textbox is clicked on, the sub is called to update the results, like doing an automatic update. All this works fine on my computer at home with Excel 2010 but when I try to run it on my computer at work which also has Excel 2010, if I click in a textbox and type a single character, I have to click back into the textbox to type another character, then again, and then again which is really annoying. I thought it might be a reference that might not be loaded but they are all there. Does anyone know what the problem could be??

[p45cal]

You're probably using a TextBox_Change event. Try a TextBox_Exit instead?

[mike0123m]

I tried textbox_exit but I get a compile error. This is the code I have to take the information from the textbox and place it in a cell on a spreadsheet:

<Private Sub TextBox336_change()>
<Sheets("sheet3").Range("e2") = TextBox336.Text>
<End sub>

Again, this works perfectly on my computer at home but when I try on my computer at work, I have to click inside the textbox after every character.

[frank_m]

Can you submit more of your code, such as sheet event code if any, and perhaps a smaple workbook with sensitive data removed?

There's probably some personal setting on the pc at your work that is causing that, maybe calculations being set to manual on you home machine.

Is there more than one computer at work that you can try it on ?

Try turning calculation mode to manual before your code and back to automatic after.

Or you you might try experimenting with screen updating.
[vba]
Private Sub TextBox336_Enter()
If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
End Sub

Private Sub TextBox336_Change()
If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
Sheets("Sheet3").Range("e3") = Me.TextBox336.Text
Application.ScreenUpdating = True
Me.TextBox336.SetFocus
End Sub

Private Sub TextBox336_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.ScreenUpdating = False Then Application.ScreenUpdating = True
End Sub
[/vba]

[mike0123m]

Frank - Attached is a stripped down version of what I am working with. I tried screen updating but that didn't seem to work either. I also tried selecting manual calculation but that didn't solve the problem either.

[p45cal]

I tried textbox_exit but I get a compile error.

Could I see the code you tried? I really think this is going to be your answer.

[mike0123m]

I am not familiar with textbox_exit so I may not have used it correctly, p45cal. This is the code I currently have:

Private Sub TextBox336_Change()
Sheets("sheet3").Range("e2") = TextBox336.Text
Call UpdateAll

Further down in the userform is the UpdateAll sub which copies information from the spreadsheet back to the userform so that if the information in textbox336 changes, the results will change at the same time on the userform:

Private Sub UpdateAll()

TextBox7.Value = Round(Val(Sheets("sheet3").Range("w51").Value), 1)
TextBox302.Value = Round(Val(Sheets("sheet3").Range("x51").Value), 1)
If TextBox7.Value = 0 Then
TextBox7.Value = ""
TextBox302.Value = ""
End If
End sub

There are many many more textboxes but they all have the same code and reference different cells.

[p45cal]

My suggestion was to simply replace:
Private Sub TextBox336_Change()
with:
Private Sub TextBox336_Exit(ByVal Cancel As MSForms.ReturnBoolean)

You may have a lot of change events taking place in that code of yours! (if each textbox has its own change event)

[mike0123m]

That solved it! That has been driving me nuts. Thank you so much p45cal!

[mike0123m]

I went through and changed all of the change events associated with the textboxes to textbox_exit and that works great. My new problem is that some of the textboxes are located in a multipage on the same userform. When the information is entered in the last textbox on the multipage, the only way it is copied to the cell in the spreadsheet is if another textbox on the multipage control is clicked on. If I don't click on a textbox on that multipage control, the program crashes because the results that are copied into other textboxes on the userform rely on all the information on that multipage control. What is the trick to fixing this crisis?

[p45cal]

Include a 'Go' or 'Update' button on the userform which checks for the existence of values in all the right places, and if they're not there, fetch them, or if the field is empty on the userform, pop up a message to say which field is empty.
If you don't want a Go button, is there some other action the user might normally carry out which could fire an event? Perhaps closing the form could be interrupted?

[mike0123m]

I added an Update button to the userform which checks to see if there are values in each of the cells on the spreadsheet. I also took the other textboxes on the userform and put them in a frame so that the program will not crash if those cells are emty. The idea is that if there are values missing from the cells, the frame will not be enabled and the textboxes cannot be touched. Is there a way to do this using select case? I have 10 cells that must have information in them before the frame on the userform will be enabled.

[p45cal]

A worksheetfunction.Counta would help:
application.counta(range("$E$37,$F$39,$G$37,$H$39,$I$37"))
for example, if that is equal to the number of cells that need to be full, all is OK.