Code that clears CB and TB fields also deleting sheet data :: MrExcel Message Board
With Macromedia Shockwave Player, you can enjoy multimedia games, learning applications, and product demonstrations on the Web, using exciting new 3D technology. Get it at: http://www.macromedia.com/shockwave/download



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Code that clears CB and TB fields also deleting sheet data
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Noir
Board Master


Joined: 25 Mar 2002
Posts: 362

Flag: Usa

Status: Offline

 Reply with quote  

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

Post Thu Nov 13, 2003 9:55 pm 
 View user's profile Send private message

Noir
Board Master


Joined: 25 Mar 2002
Posts: 362

Flag: Usa

Status: Offline

 Reply with quote  

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

Post Fri Nov 14, 2003 2:20 pm 
 View user's profile Send private message

Noir
Board Master


Joined: 25 Mar 2002
Posts: 362

Flag: Usa

Status: Offline

 Reply with quote  

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

Post Fri Nov 14, 2003 8:13 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

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.

Post Sat Nov 15, 2003 8:00 am 
 View user's profile Send private message

Noir
Board Master


Joined: 25 Mar 2002
Posts: 362

Flag: Usa

Status: Offline

 Reply with quote  

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

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

Noir

Post Sun Nov 16, 2003 5:47 am 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

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.

Post Sun Nov 16, 2003 12:06 pm 
 View user's profile Send private message

Noir
Board Master


Joined: 25 Mar 2002
Posts: 362

Flag: Usa

Status: Offline

 Reply with quote  

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

Post Mon Nov 17, 2003 2:44 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.