CODEWITHSUNDEEP
excelvbauserform
10101
10101

Reputation: 2412

Userform: Iniialize until value changes in TextBox

I have UserForm with TextBox. Then there is value in 'Other Data'!C6, in cell C6 on Other Data is formula =D6.

Then by this I am passing value to cell:

Private Sub TextBox3_Change()
ThisWorkbook.Worksheets("Other Data").Range("C6").Value = Me.TextBox3.Text
End Sub

Getting value from cell:

Private Sub UserForm_Initialize()
Me.TextBox3.Text = ThisWorkbook.Worksheets("Other Data").Range("C6").Value
End Sub

Private Sub UserForm_Activate()
Me.TextBox3.Text = ThisWorkbook.Worksheets("Other Data").Range("C6").Value
End Sub

Private Sub UserForm_Click()
Me.TextBox3.Text = ThisWorkbook.Worksheets("Other Data").Range("C6").Value
End Sub

The problem is that number from UserForm is overriding formula in 'Other Data'!C6 while opening UserForm. This is affecting other formulas and calculations in my Excel applivcation. How to prevent 'Other Data'!C6 override until I manually change value in TextBox on UserForm?

Upvotes: 0

Views: 72

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4714

Everytime you change the value in the textbox you are triggering the change event and over writing C6. The trick is to stop the triggering in your initialise and other events by turning off EnableEvents while you make your change

Private Sub UserForm_Initialize()
application.EnableEvents = false
Me.TextBox3.Text = ThisWorkbook.Worksheets("Other Data").Range("C6").Value
application.EnableEvents = true

End Sub

Upvotes: 1

Related Questions