Clear information from frames 1

[newprogamer]

Hello Everyone,

I have created a program that has one form. The form as different tabs and each tab consists of frames. I am very new at VBA. How do I clear the data from a particular frame? Any help would be appreciated.

Thanks,
New Programmer

 

[Killian01]

Hiya,

It's not really clear what you mean here... You have a form with a tab control and on each tab there are frames? what's in the frames? I might be helpful if you posted an example so I get the idea (i'm having a very slow start to the day here... not enough coffee)

K

 

[newprogamer]

I'm sorry,

I have a form that has 4 tabs. Each tab has at least 2 frames. The user enters information into textboxes or either they choose from a list box. When the "Clear Button" is clicked I would like to clear the entries the user has made in the textboxes and list boxes.

I put "ClearMe" in the tag property.
I tried the code below but it didn't work. It
stops on me.form (method or data member not found)
****************************
Dim ctrl As Control

For Each ctrl In Me.Form
If InStr(1, ctrl.Tag, "ClearMe") Then
ctrl = vbNullString
End If
Next ctrl

****************************
I thought my code was too wrong to post.
I appreciate any help you can provide.

NewProgrammer

 

[Killian01]

Hi again,
I've been out of the office for a while so maybe you've solved this already

Generally, the way to do this is to check each control in the controls collection (all the controls on a form), test for something and perform an action if required.
I would put this code in the clear button click event:

Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If Left(ctrl.Name, 3) = "txt" Then
ctrl.Text = ""
End If
Next

This assumes that you've followed good practice and named all your textboxes "txtSomething".

If you do the same for other types of control ("lstSomething" for list boxes, "cboSomething" for comboboxes) then an if...then for each of those too.
If your running more than a couple of tests, a Select...Case construct would be better

Hope this helps

K

 

[newprogamer]

Thanks Killian01,

I do not get the debug error any longer. I am using combo boxes instead of textboxes. I have a clear button on all the sheets. Is there a way to clear all the combo boxes on one sheet at once. Currently, when I click the clear button it only clears one combo box. I have to click the clear box once for every box I want to clear. I thought it would clear everything on that page with one click. Is there a way to do this? Any help would be greatly appreciated. My code is below.

Dim ctrl As Control
For Each ctrl In Quote.Controls
If Left(ctrl.Name, 3) = "cbo" Then
ctrl.Text = ""
End If
Next

 

[Killian01]

Hiya,

comboboxes don't have a Text property. When you load all the items you should set the first one (postion 0) as blank
ComboBox1.AddItem "", 0
then for each control, set the list index to 0
ComboBox1.ListIndex = 0

K

 

[newprogamer]

Hello Killian01,

The combo boxes do not have an .additem or .listindex property. Maybe because, they are "ShowDropButtonWhen" property is set to never. They do not have a drop down arrow.

I received the following error message when I ran the ComboBox1.ListIndex = 0. The message was . . .
"Could not set the ListIndex property, Invalid property value".

I'm sure you can tell I am new at this.

 

[Phaed]

why dropdown property set 2 never?

combobox controls (on UserForms) DO have listindex and AddItem property (actually method). if u don't hav 1, something not right here.

 

[PHV]

BTW, which kind of form are you using with which kind of VBA ?

 

[newprogamer]

Good morning, Sorry for not responding. I have been on vacation.

Phaed,
I created several combo boxes on a form. Then pointed to the text in the spreadsheet. If the box only had one option. I just changed the drop down arrow not to show.

PHV,
I am using VBA and MS Excel 2003.

 

[Phaed]

still not clear. is this a UserForm? when u say "form" u MUST be clear what u r meaning.

"pointed to the text in the spreadsheet" huh? I b dense here. i miss my uncle fumei

 

[PHV]

If the ControlSource of the combo is a cell in a spreadsheet, then to clear the combo just clear the corresponding cell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[newprogamer]

Thanks guys, I am back at work today!

********************************************
Hello Phaed,

Sorry, I apologize for being dense. I really don't know what I'm doing yet. I'm learning as I go. That's why I used this site to get some help.

I created a UserForm. I placed combo boxes on the user form. The information the user types into the combo box will be saved in a cell in excel. That's what I meant by pointing to a cell. I used the properties box to set the Control Source. For example: Contol Source = General!B9

***********************************************
Thanks PHV, I'll try it.