How do I clear a form without a button

[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

 

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

 

[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

 

[Dave Peterson]

I think I'd just reassign the values, but that's just me.

 

[Pam Field]

OK Thanks again