I have a user form with a command button that users enter data to a table.
How can clear the user form with the same button click that enters the data
so someone can continue to the next record? i know this must be simple but
it is eluding me.
--
Bucky F

To answer your question, you will need to supply the following information:

1. What controls are used on the UserForm i.e. listboxes, comboboxes,
textboxes?

2. Post the code that is behind the form. i.e. initialize and click event
codes.

"willfeld" wrote:

> I have a user form with a command button that users enter data to a table.
> How can clear the user form with the same button click that enters the data
> so someone can continue to the next record? i know this must be simple but
> it is eluding me.
> --
> Bucky F

One way is to put the UserForm.Show in a loop like:

Sub UFloopDemo()
'Declarations, etc.
'Some code
Do
UserForm1.Show
again = MsgBox("Show UserForm again?", vbYesNo, "More?")
Loop while again = vbYes
'some more code
End Sub

You could possibly do it without having to unload the UserForm
but it is difficult to tell without knowing what is on the
form and what the code for the controls is.


"willfeld" wrote:

> I have a user form with a command button that users enter data to a table.
> How can clear the user form with the same button click that enters the data
> so someone can continue to the next record? i know this must be simple but
> it is eluding me.
> --
> Bucky F

I have several combo boxes and about 10 text boxes. the code is:
Private Sub fconfirm_Click()
Dim NewRow As Integer
NewRow = Worksheets("main").Range("B3").Value + 1

If Len(MyForm.fregion.Value) = 0 Then
MsgBox "The Region field can not be left Blank!", vbOKOnly, "IPA Training -
Input Form"
MyForm.fregion.SetFocus
Exit Sub
End If

Worksheets("Table").Cells(NewRow, 1).Value = MyForm.fregion.Value
Worksheets("Table").Cells(NewRow, 2).Value = MyForm.ftrainer.Value
Worksheets("Table").Cells(NewRow, 3).Value = MyForm.ftrainee.Value
Worksheets("Table").Cells(NewRow, 4).Value = MyForm.fstriation.Value
Worksheets("Table").Cells(NewRow, 5).Value = MyForm.ftrainingdate.Value
Worksheets("Table").Cells(NewRow, 6).Value = MyForm.fappts.Value
Worksheets("Table").Cells(NewRow, 7).Value = MyForm.fconfirmedleads.Value
Worksheets("Table").Cells(NewRow, 8).Value = MyForm.fcontacts.Value
Worksheets("Table").Cells(NewRow, 9).Value = MyForm.fpresentations.Value
Worksheets("Table").Cells(NewRow, 10).Value = MyForm.ftalkbc.Value
Worksheets("Table").Cells(NewRow, 11).Value = MyForm.ftalkzone.Value
Worksheets("Table").Cells(NewRow, 12).Value = MyForm.ffieldsales.Value
Worksheets("Table").Cells(NewRow, 13).Value = MyForm.fhybrids.Value
Worksheets("Table").Cells(NewRow, 15).Value = MyForm.fapptafter.Value
Worksheets("Table").Cells(NewRow, 16).Value = MyForm.fconfirmedafter.Value
Worksheets("Table").Cells(NewRow, 17).Value = MyForm.fcontactsafter.Value
Worksheets("Table").Cells(NewRow, 18).Value = MyForm.fpresentationsafter.Value
Worksheets("Table").Cells(NewRow, 19).Value = MyForm.ftalkbcafter.Value
Worksheets("Table").Cells(NewRow, 20).Value = MyForm.ftalkzoneafter.Value
Worksheets("Table").Cells(NewRow, 21).Value = MyForm.ffieldsalesafter.Value
Worksheets("Table").Cells(NewRow, 22).Value = MyForm.fhybridsafter.Value

MyForm.Hide
End Sub

And the button has the following
Sub Button1_Click()
Range("B3").Value = Worksheets("Table").Range("a20000").End(xlUp).Row
Range("C1").Value = ""
Range("d1").Value = ""
Range("e1").Value = ""
Range("f1").Value = ""
Range("G1").Value = ""
Range("H1").Value = ""
Range("I1").Value = ""
Range("J1").Value = ""
Range("K1").Value = ""
Range("L1").Value = ""
Range("M1").Value = ""
Range("O1").Value = ""
Range("P1").Value = ""
Range("Q1").Value = ""
Range("R1").Value = ""
Range("S1").Value = ""
Range("T1").Value = ""

MyForm.Show
MyForm.fregion.SetFocus
End Sub



--
Bucky F


"JLGWhiz" wrote:

> To answer your question, you will need to supply the following information:
>
> 1. What controls are used on the UserForm i.e. listboxes, comboboxes,
> textboxes?
>
> 2. Post the code that is behind the form. i.e. initialize and click event
> codes.
>
> "willfeld" wrote:
>
> > I have a user form with a command button that users enter data to a table.
> > How can clear the user form with the same button click that enters the data
> > so someone can continue to the next record? i know this must be simple but
> > it is eluding me.
> > --
> > Bucky F

I have several combo boxes and about 10 text boxes. the code is:
Private Sub fconfirm_Click()
Dim NewRow As Integer
NewRow = Worksheets("main").Range("B3").Value + 1

If Len(MyForm.fregion.Value) = 0 Then
MsgBox "The Region field can not be left Blank!", vbOKOnly, "IPA Training -
Input Form"
MyForm.fregion.SetFocus
Exit Sub
End If

Worksheets("Table").Cells(NewRow, 1).Value = MyForm.fregion.Value
Worksheets("Table").Cells(NewRow, 2).Value = MyForm.ftrainer.Value
Worksheets("Table").Cells(NewRow, 3).Value = MyForm.ftrainee.Value
Worksheets("Table").Cells(NewRow, 4).Value = MyForm.fstriation.Value
Worksheets("Table").Cells(NewRow, 5).Value = MyForm.ftrainingdate.Value
Worksheets("Table").Cells(NewRow, 6).Value = MyForm.fappts.Value
Worksheets("Table").Cells(NewRow, 7).Value = MyForm.fconfirmedleads.Value
Worksheets("Table").Cells(NewRow, 8).Value = MyForm.fcontacts.Value
Worksheets("Table").Cells(NewRow, 9).Value = MyForm.fpresentations.Value
Worksheets("Table").Cells(NewRow, 10).Value = MyForm.ftalkbc.Value
Worksheets("Table").Cells(NewRow, 11).Value = MyForm.ftalkzone.Value
Worksheets("Table").Cells(NewRow, 12).Value = MyForm.ffieldsales.Value
Worksheets("Table").Cells(NewRow, 13).Value = MyForm.fhybrids.Value
Worksheets("Table").Cells(NewRow, 15).Value = MyForm.fapptafter.Value
Worksheets("Table").Cells(NewRow, 16).Value = MyForm.fconfirmedafter.Value
Worksheets("Table").Cells(NewRow, 17).Value = MyForm.fcontactsafter.Value
Worksheets("Table").Cells(NewRow, 18).Value =
MyForm.fpresentationsafter.Value
Worksheets("Table").Cells(NewRow, 19).Value = MyForm.ftalkbcafter.Value
Worksheets("Table").Cells(NewRow, 20).Value = MyForm.ftalkzoneafter.Value
Worksheets("Table").Cells(NewRow, 21).Value = MyForm.ffieldsalesafter.Value
Worksheets("Table").Cells(NewRow, 22).Value = MyForm.fhybridsafter.Value

MyForm.Hide
End Sub

And the button has the following
Sub Button1_Click()
Range("B3").Value = Worksheets("Table").Range("a20000").End(xlUp).Row
Range("C1").Value = ""
Range("d1").Value = ""
Range("e1").Value = ""
Range("f1").Value = ""
Range("G1").Value = ""
Range("H1").Value = ""
Range("I1").Value = ""
Range("J1").Value = ""
Range("K1").Value = ""
Range("L1").Value = ""
Range("M1").Value = ""
Range("O1").Value = ""
Range("P1").Value = ""
Range("Q1").Value = ""
Range("R1").Value = ""
Range("S1").Value = ""
Range("T1").Value = ""

MyForm.Show
MyForm.fregion.SetFocus
End Sub

I don't know if you saw this from my last post so i sent it again.


--
Bucky F


"JLGWhiz" wrote:

> One way is to put the UserForm.Show in a loop like:
>
> Sub UFloopDemo()
> 'Declarations, etc.
> 'Some code
> Do
> UserForm1.Show
> again = MsgBox("Show UserForm again?", vbYesNo, "More?")
> Loop while again = vbYes
> 'some more code
> End Sub
>
> You could possibly do it without having to unload the UserForm
> but it is difficult to tell without knowing what is on the
> form and what the code for the controls is.
>
>
> "willfeld" wrote:
>
> > I have a user form with a command button that users enter data to a table.
> > How can clear the user form with the same button click that enters the data
> > so someone can continue to the next record? i know this must be simple but
> > it is eluding me.
> > --
> > Bucky F

> > I have a user form with a command button that users enter data to
> > a table. How can clear the user form with the same button click
> > that enters the data so someone can continue to the next record?
>
>I have several combo boxes and about 10 text boxes.

You can use this code to clear the ComboBoxes and TextBoxes...

Dim C As Control
......
......
For Each C In MyForm.Controls
If TypeOf C Is MSForms.ComboBox Then
C.ListIndex = -1
ElseIf TypeOf C Is MSForms.TextBox Then
C.Text = ""
End If
Next

Just put the Dim statement with the other Dim statements and insert this
code wherever you need it at with your other code. By the way, you can
simplify the code you posted for Button1 as follows...

Private Sub Button1_Click()
Range("B3").Value = Worksheets("Table").Range("A20000").End(xlUp).Row
Range("C1:T1").ClearContents
End Sub

Although I would put the worksheet references in for these ranges.

Rick

See inline comments...

> I can use this constantly - if I understand it

Before you go and use this everywhere, remember that it was designed for
your specific case... in effect, find the first digit that is not a zero
(notice that the array and listing of digits do not have a zero in them),
which is also why I said this formula only applies IF the first character
after the leading zeroes is a digit (it will fail to work correctly if a
non-digit ever follows the leading zeroes).

> and Gosh I hate to ask but could you explain how it works -

I'll try.

> I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe
> it uses the &"123456789" constant
> but I am really confused as to the MIN function and the 99 value and
> just basically how the formula flows.

I guess the best place to start is by giving an overview of what the formula
does. It finds the location of the first non-zero digit within the text and
uses that to find the starting point for pulling out the text you wanted. It
uses the MID function to get pull out a sub-string from the main piece of
text. The format of the MID function is...

MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint)

We will describe how to get the StartingPoint (the location of the first
non-zero) in a moment; but, once you have it, you wanted that digit along
with the remainder of the text after it. We don't know how many characters
that will be as the number of leading zeroes can vary; however, there is no
problem in asking for more characters than exist, so I took a guess that
your text will never be longer than 99 characters (which is why the last
value is 99... it is the 3rd argument of the MID function). Okay, so how do
we get the StartingPoint. We use ths code...

MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))

And how does that work. First off, we note that the FIND function cannot
process an array (values contained within curly braces); however, the MIN
can and so, embedding an array or a function call with an array (even if
that function can't normally handle arrays) within it forces the array to
get processed. This happens be feeding each array element into its encasing
function (the FIND function in this case) one at a time so that the MIN
function can determine which evaluated value is the smallest. So, the MIN
function is forcing these evaluations to take place...

FIND(1,A1&"123456789")
FIND(2,A1&"123456789")
FIND(3,A1&"123456789")
FIND(4,A1&"123456789")
FIND(5,A1&"123456789")
FIND(6,A1&"123456789")
FIND(7,A1&"123456789")
FIND(8,A1&"123456789")
FIND(9,A1&"123456789")

The returned values from the above are the positions of the digit within the
text, so when the MIN function looks at all of these values in order to find
the smallest value, that value is the location in the text... that is, the
location of the first non-zero digit within the text (which is our starting
point for the MID function). Okay, now the concatenated "123456789" is
placed there to ensure that FIND will in fact find the digit it is looking
for at the moment.

Well, that's it. I'm not so sure my explanation is as clear in words as the
concepts are in my head, but hopefully there is enough for your to fight
your way to an understanding of what is happening.

Rick



> > Is there **always** digit after the last leading zero (that is, never
> > something like 000ABC)? If so...
> >
> > =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99)
> >
> > >I have an item number field which I use a formula to pull the last
> > > segment of the item number field
> > > ( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A )
> > >
> > > the results would be a text field such as 00000094 or 0001A-A
> > > or 0230-B
> > >
> > > I need to find a way to remove the leading 0's
> > >
> > > does anyone have any ideas?

*** Sorry about the incomplete previous posting ***
*** I accidentally sent it before I had finished. ***

See inline comments...

> I can use this constantly - if I understand it

Before you go and use this everywhere, remember that it was designed for
your specific case... in effect, find the first digit that is not a zero
(notice that the array and listing of digits do not have a zero in them),
which is also why I said this formula only applies IF the first character
after the leading zeroes is a digit (it will fail to work correctly if a
non-digit ever follows the leading zeroes).

> and Gosh I hate to ask but could you explain how it works -

I'll try.

> I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe
> it uses the &"123456789" constant
> but I am really confused as to the MIN function and the 99 value and
> just basically how the formula flows.

I guess the best place to start is by giving an overview of what the formula
does. It finds the location of the first non-zero digit within the text and
uses that to find the starting point for pulling out the text you wanted. It
uses the MID function to get pull out a sub-string from the main piece of
text. The format of the MID function is...

MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint)

We will describe how to get the StartingPoint (the location of the first
non-zero) in a moment; but, once you have it, you wanted that digit along
with the remainder of the text after it. We don't know how many characters
that will be as the number of leading zeroes can vary; however, there is no
problem in asking for more characters than exist, so I took a guess that
your text will never be longer than 99 characters (which is why the last
value is 99... it is the 3rd argument of the MID function). Okay, so how do
we get the StartingPoint. We use ths code...

MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))

And how does that work. First off, we note that the FIND function cannot
process an array (values contained within curly braces); however, the MIN
can and so, embedding an array or a function call with an array (even if
that function can't normally handle arrays) within it forces the array to
get processed. This happens be feeding each array element into its encasing
function (the FIND function in this case) one at a time so that the MIN
function can determine which evaluated value is the smallest. So, the MIN
function is forcing these evaluations to take place...

FIND(1,A1&"123456789")
FIND(2,A1&"123456789")
FIND(3,A1&"123456789")
FIND(4,A1&"123456789")
FIND(5,A1&"123456789")
FIND(6,A1&"123456789")
FIND(7,A1&"123456789")
FIND(8,A1&"123456789")
FIND(9,A1&"123456789")

The returned values from the above are the positions of the digit within the
text, so when the MIN function looks at all of these values in order to find
the smallest value, that value is the location in the text... that is, the
location of the first non-zero digit within the text (which is our starting
point for the MID function). Okay, now the concatenated "123456789" is
placed there to ensure that FIND will in fact find the digit it is looking
for at the moment.

Well, that's it. I'm not so sure my explanation is as clear in words as the
concepts are in my head, but hopefully there is enough for your to fight
your way to an understanding of what is happening.

Rick



> > Is there **always** digit after the last leading zero (that is, never
> > something like 000ABC)? If so...
> >
> > =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99)
> >
> > >I have an item number field which I use a formula to pull the last
> > > segment of the item number field
> > > ( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A )
> > >
> > > the results would be a text field such as 00000094 or 0001A-A
> > > or 0230-B
> > >
> > > I need to find a way to remove the leading 0's
> > >
> > > does anyone have any ideas?

No, we don't need this information, Bucky does.

Bucky -

You simply need to write code that changes each control to its default
value, most likely false for checkboxes and "" for textboxes. Do this right
after you place the current values of the controls into the worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:78062EBC-EAC0-442E-BEE0-(E-Mail Removed)...
> To answer your question, you will need to supply the following
> information:
>
> 1. What controls are used on the UserForm i.e. listboxes, comboboxes,
> textboxes?
>
> 2. Post the code that is behind the form. i.e. initialize and click
> event
> codes.
>
> "willfeld" wrote:
>
>> I have a user form with a command button that users enter data to a
>> table.
>> How can clear the user form with the same button click that enters the
>> data
>> so someone can continue to the next record? i know this must be simple
>> but
>> it is eluding me.
>> --
>> Bucky F

I don't know if you picked up on Rick's reply or not. You can insert his
code near the end of your code for the fconfirm button like so:

For Each C In MyForm.Controls
If TypeOf C Is MSForms.ComboBox Then
C.ListIndex = -1
ElseIf TypeOf C Is MSForms.TextBox Then
C.Text = ""
End If
Next
MyForm.Hide
End Sub

This should reset all of your combo boxes and text boxes to blank. Then
when you show the form again, all controls will require a new entry.


"willfeld" wrote:

> I have several combo boxes and about 10 text boxes. the code is:
> Private Sub fconfirm_Click()
> Dim NewRow As Integer
> NewRow = Worksheets("main").Range("B3").Value + 1
>
> If Len(MyForm.fregion.Value) = 0 Then
> MsgBox "The Region field can not be left Blank!", vbOKOnly, "IPA Training -
> Input Form"
> MyForm.fregion.SetFocus
> Exit Sub
> End If
>
> Worksheets("Table").Cells(NewRow, 1).Value = MyForm.fregion.Value
> Worksheets("Table").Cells(NewRow, 2).Value = MyForm.ftrainer.Value
> Worksheets("Table").Cells(NewRow, 3).Value = MyForm.ftrainee.Value
> Worksheets("Table").Cells(NewRow, 4).Value = MyForm.fstriation.Value
> Worksheets("Table").Cells(NewRow, 5).Value = MyForm.ftrainingdate.Value
> Worksheets("Table").Cells(NewRow, 6).Value = MyForm.fappts.Value
> Worksheets("Table").Cells(NewRow, 7).Value = MyForm.fconfirmedleads.Value
> Worksheets("Table").Cells(NewRow, 8).Value = MyForm.fcontacts.Value
> Worksheets("Table").Cells(NewRow, 9).Value = MyForm.fpresentations.Value
> Worksheets("Table").Cells(NewRow, 10).Value = MyForm.ftalkbc.Value
> Worksheets("Table").Cells(NewRow, 11).Value = MyForm.ftalkzone.Value
> Worksheets("Table").Cells(NewRow, 12).Value = MyForm.ffieldsales.Value
> Worksheets("Table").Cells(NewRow, 13).Value = MyForm.fhybrids.Value
> Worksheets("Table").Cells(NewRow, 15).Value = MyForm.fapptafter.Value
> Worksheets("Table").Cells(NewRow, 16).Value = MyForm.fconfirmedafter.Value
> Worksheets("Table").Cells(NewRow, 17).Value = MyForm.fcontactsafter.Value
> Worksheets("Table").Cells(NewRow, 18).Value =
> MyForm.fpresentationsafter.Value
> Worksheets("Table").Cells(NewRow, 19).Value = MyForm.ftalkbcafter.Value
> Worksheets("Table").Cells(NewRow, 20).Value = MyForm.ftalkzoneafter.Value
> Worksheets("Table").Cells(NewRow, 21).Value = MyForm.ffieldsalesafter.Value
> Worksheets("Table").Cells(NewRow, 22).Value = MyForm.fhybridsafter.Value
>
> MyForm.Hide
> End Sub
>
> And the button has the following
> Sub Button1_Click()
> Range("B3").Value = Worksheets("Table").Range("a20000").End(xlUp).Row
> Range("C1").Value = ""
> Range("d1").Value = ""
> Range("e1").Value = ""
> Range("f1").Value = ""
> Range("G1").Value = ""
> Range("H1").Value = ""
> Range("I1").Value = ""
> Range("J1").Value = ""
> Range("K1").Value = ""
> Range("L1").Value = ""
> Range("M1").Value = ""
> Range("O1").Value = ""
> Range("P1").Value = ""
> Range("Q1").Value = ""
> Range("R1").Value = ""
> Range("S1").Value = ""
> Range("T1").Value = ""
>
> MyForm.Show
> MyForm.fregion.SetFocus
> End Sub
>
> I don't know if you saw this from my last post so i sent it again.
>
>
> --
> Bucky F
>
>
> "JLGWhiz" wrote:
>
> > One way is to put the UserForm.Show in a loop like:
> >
> > Sub UFloopDemo()
> > 'Declarations, etc.
> > 'Some code
> > Do
> > UserForm1.Show
> > again = MsgBox("Show UserForm again?", vbYesNo, "More?")
> > Loop while again = vbYes
> > 'some more code
> > End Sub
> >
> > You could possibly do it without having to unload the UserForm
> > but it is difficult to tell without knowing what is on the
> > form and what the code for the controls is.
> >
> >
> > "willfeld" wrote:
> >
> > > I have a user form with a command button that users enter data to a table.
> > > How can clear the user form with the same button click that enters the data
> > > so someone can continue to the next record? i know this must be simple but
> > > it is eluding me.
> > > --
> > > Bucky F