Code that clears CB and TB fields also deleting sheet data

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Code that clears CB and TB fields also deleting sheet data

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code that clears CB and TB fields also deleting sheet data

     
    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Noir

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com