Excel VBA: On Change of an amount on text box from an UserForm to Update onto Excel Worksheet

MissZabrina

New Member
Joined
Feb 18, 2009
Messages
1
Good Morning, I need some directions and ideas on how to write the codes to input data onto certain text boxes, then transfer the input data from the UserForm onto worksheet1. ANy input will be greatly appreicate and making my day!!!

If I have these text boxes: txtE, txtI, txtK.

By clicking the "OK" button "cmdOK" which will check if:

Any change to txtE.value, then the new value will be update onto cell E2 on worksheet1.
Any change to txtI.value, then the new value will be update onto cell I2 on worksheet1.
Any change to txtK.value, then the new value will be update onto cell K2 on worksheet1.

If I only changed the input value on txtE, but no change on txtI & txtK, will only update txtE and do nothing with txtI & txtK. The problem is, I am going to have more than 20 text boxes to do something like that, and I wan to find out a smart way to do this.

So, how do I write an if statement for the "OK" button to just perform the updating only when there is a change of the value on the specific textbox?

Would the codes be something like this? I have no idea...



Sub cmdOK
Sheet("worksheet1").Range("E2").Value = UserForm.txtE.Value.change
Sheet("worksheet1").Range("I2").Value = UserForm.txtI.Value.change
Sheet("worksheet1").Range("K2").Value = UserForm.txtK.Value.change
End Sub​




</PRE>



Thanks a bunch!!!!
Zabrina
<!-- / message -->
 
Last edited:

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Make a new module. Insert this in it:
Rich (BB code):
Public oldtxtE As String
Public oldtxtI As String
Public oldtxtK As String
In your txtE_Change() code write:
Rich (BB code):
oldtxtE = txtE.Value
Do the same for txtI/txtK.

For the button:
Rich (BB code):
If oldtxtE <> txtE.Value Then
    Worksheets("Sheet1").Range("E2").Value = txtE.Value
End If
Add the same code for txtI/txtK.

What it will do:
The module will declare global variables that can be used in any function.

Whenever you change one of the text boxes, it will store its value in the appropriate variable.

When you click the button, it will check to see if the old value is equal to the current value, and if it's not it will copy to the worksheet.

This is all quite roundabout though. You could do away with the button, and simply make the rowsource for the textboxes equal to the appropriate cells -- any time you change the textbox, the cell would update automatically.
 

Forum statistics

Threads
1,081,513
Messages
5,359,226
Members
400,521
Latest member
smarty1995

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top