Code that clears CB and TB fields also deleting sheet data

[Noir]

I have a Userform with 1 Combobox and 10 Textboxes. I use this form to update fields on my sheet using this Textbox code;

ActiveCell.Offset(0, 1).Value = TextBox1.Value

I use the following code to clear the Combobox and Textbox data before i enter new data into them;

Dim ctl As Control
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then ctl.Value = Empty
If TypeName(ctl) = "ComboBox" Then ctl.Value = Empty
Next ctl

This delete code works well but has a strange side effect. It also deletes the data i just entered onto my sheet (all 10 columns of it). It only deletes the last line i entered.

What is causing this?

Thx,
Noir

 

[Noir]

Re: Code that clears CB and TB fields also deleting sheet da

PS,
Troubleshooting this code is perferred but, any other Combobox/Textbox value deleting code is also welcome.

Noir

 

[Noir]

Re: Code that clears CB and TB fields also deleting sheet da

I figured it out. I noticed that if i kept the data in the Textboxes and closed then re-opened the Userform, all Textboxes and the Combobox would be empty. So, i relaced the delete code with " Unload Userform1
then Userform1.show". This clears all boxes and leaves my sheet data intact.

Crude but effective.


Noir

 

[Andrew Poulsom]

Re: Code that clears CB and TB fields also deleting sheet da

That is indeed an effective solution.

Your original problem probably arose because the Change event for the controls was firing when you updated them in your code. You would need to find some way of disabling the event procedure. One way is to use a public Boolean variable. Set it in the code that makes the changes and test it at the beginning of each event procedure, exiting if you don't want the event to fire.

 

[Noir]

Re: Code that clears CB and TB fields also deleting sheet da

Andrew,
Can you give me an example of a possible fix?

Noir

 

[Andrew Poulsom]

Re: Code that clears CB and TB fields also deleting sheet da

There's an example here:

http://www.mrexcel.com/board2/viewtopic.php?t=66883

In this case it's within the event procedure, but you could also declare the variable with Dim instead of Static at the top of the module. Set it to True in any procedure that changes a control whose event you don't want to fire, and False afterwards.

 

[Noir]

Re: Code that clears CB and TB fields also deleting sheet da

Thanks Andrew. If i run into this problem again, i will give your solution a try.

Noir