excel macro

[Guest]

I have created a form in excel which consists of fill ins, list boxes and
check mark options. I am using the database wizard to collect the data onto
one spreadsheet. Does anyone know if l can create a macro to clear all the
previous data entry data from each of these fields. Everytime the form is to
be used l would like the user to start with a blank form. It is my
understanding by using the template wizard l am not able to start fresh
everytime. I am inexperienced with complicated macros. I have only created
simple ones. Any assistance would be greatly appreciated.

Lynda S

 

[Guest]

Hi,
I'm not going to be much help with a database wizard but...
If you created a user form using vb then a simple macro like...
sub cleardata()
userform.listboxname=""

end sub
will clear the items. Attach it to a button that is labeled "clear data"???

Hope this is going in the right direction.

peter

 

[Guest]

Thank you Peter,
I have not gone into the vb world as yet. Is this easy to do and how can l
get started. Can you point me in the right direction ie instructions,
manuals, etc.

Any assistance would be greatly appreciated.

Lynda

 

[Guest]

Hi,
Here is a quick intro.
open the vb editor
alt-f11

insert userform

add controls (command button, checkbox, COMBOBOX, etc)

add a combobox
double click control to add code that will run when button is clicked
(changed or exit)
( range(“a1â€).value=userform1.comboBox1.value )


insert module

sub start()

UserForm1.ComboBox1.List = Range("e10:e13").Value
(this will fill combo box with selection-values in e10:e13)

userform1 show

end sub

put a command button on your ss and assign it to macro start

buy a good book
Excel 2003 Power Programming with VBA
John Walkenbach

have fun, good luck.

peter

 

[Guest]

Hi Peter, Thank you for the responses. Sorry for the delay in thank yous. I
am taking small steps here. I have purchased an Excel VBA in easy steps
book. First l have created a button on my database that clears the contents
for specific cells. I have one problem with that in that it clears my
formulas as well. How do l get around just clearing the data in these cells
and not the formulas?. Second l have list boxes in numerous cells ie one in
c9 and c10. How do l clear the selection in those as well? They are linked
to a cell that displays the response that was chosen. Ie in cell c9 l have a
list box for gender with the choices of female and male. In my linked cell
0=blank, 1=female and 2=male. So if the auditor selects female l have a
formula that states if the auditor selects female then female displays in the
cell. My question is how can l get the list box to display a blank for the
next auditor to input from this same button that clears the data in the other
cells. This is what l have writen so far to clear my text cells:
Private Sub CommandButton1_Click()
Range("c5:c39") . ClearContents
End Sub

Thank you for any assistance
Lynda S

 

[Guest]

Hi,
If your still having trouble or just want help, you can email the ss and
I'll take a look
peter@"remove"vanbeurden.com

peter