How do I clear a form without a button

P

Pam Field

Hi there

I have the following cmdOK button for a form.


Private Sub cmdOK_Click()
Sheets("Band Members").Select
Range("Last_Name").Select
Selection.EntireRow.Insert

ActiveCell.Value = txtName.Text
ActiveCell.Offset(0, 1).Value = txtAddress.Text
ActiveCell.Offset(0, 2).Value = txtSuburb.Text
ActiveCell.Offset(0, 3).Value = txtPhone.Text
ActiveCell.Offset(0, 4).Value = cboType.Text
ActiveCell.Offset(0, 5).Value = "A"
ActiveCell.Offset(0, 6).Formula = "=IF(Status = ""A"",
VLOOKUP(Type,Fees_table,2),0)"
ActiveCell.Offset(0, 8).Value = cboMain.Text
ActiveCell.Offset(0, 9).Value = cboSecond.Text
ActiveCell.Offset(0, 10).Value = cboThird.Text
End Sub


What it does is updates the spreadsheet with a new line of data when you hit
the OK. I also want is for the form to clear itself but stay open when you
hit OK. I know I can add a 'clear form' command button but I want it to do
so automatically. It must be something simple as usual but it beats me.

Any help will be greatly appreciated.

regards
Pam
 
D

Dave Peterson

Just add some more lines before the end of your sub:

txtAddress.value = ""
txtSuburb.value = ""
...
cboThird.value = ""


But sometimes users want to keep the values for the next entry.

You may want something like:

Private Sub cmdOK_Click()

Dim Resp as long '<--added

Sheets("Band Members").Select
Range("Last_Name").Select
Selection.EntireRow.Insert

ActiveCell.Value = txtName.Text
ActiveCell.Offset(0, 1).Value = txtAddress.Text
ActiveCell.Offset(0, 2).Value = txtSuburb.Text
ActiveCell.Offset(0, 3).Value = txtPhone.Text
ActiveCell.Offset(0, 4).Value = cboType.Text
ActiveCell.Offset(0, 5).Value = "A"
ActiveCell.Offset(0, 6).Formula _
= "=IF(Status = ""A"",VLOOKUP(Type,Fees_table,2),0)"
ActiveCell.Offset(0, 8).Value = cboMain.Text
ActiveCell.Offset(0, 9).Value = cboSecond.Text
ActiveCell.Offset(0, 10).Value = cboThird.Text

resp = msgbox("Prompt:="Clear the values?", buttons:=vbyesno)

if resp = vbyes then
txtAddress.value = ""
txtSuburb.value = ""
'...
cboThird.value = ""
end if

End Sub

You could even choose to keep some existing values and clear the ones that
shouldn't be duplicated.
 
P

Pam Field

Hi Dave

Thanks for your help. A moment ago I added the following 2 lines of code
and it does what I want. Is this an ok way of doing it too or something
that would be frowned on by those who know better :) I'm just learning as
you would probably have guessed.

Unload frmNewMember
frmNewMember.Show

cheers
Pam
 
Top