How do I clear a form without a button

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

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 wrote:
>
> 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

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" <petersod@verizonXSPAM.net> wrote in message
news:44B38E01.A1260975@verizonXSPAM.net...
> 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.
>

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

Pam Field wrote:
>
> 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" <petersod@verizonXSPAM.net> wrote in message
> news:44B38E01.A1260975@verizonXSPAM.net...
> > 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.
> >

--

Dave Peterson

OK Thanks again


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44B39142.D7A8744E@verizonXSPAM.net...
>I think I'd just reassign the values, but that's just me.
>