How do I clear a form without a button

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