textboxes
 

Hi
There are two parts to this question.

1.
Is there any way I can make it so that only a numeric
value (preferrably within certain limits) can be entered
into a textbox?

2.
If the above is possible, then I have 3 textboxes which
are used to perform a calculation, the results of which
will be displayed in a label.
I know what the formula is on an excel sheet, using the
functions Average() and trunc(), but how would I code this
in vba?

Any help much appreciated

textboxes
 

Hi Libby,

I assume you mean userform textboxes?

Here is a textbox event that only allows numeric input

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case 32 ' space
Case Else
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
Beep
End Select

End Sub

It also allows spaces, if you don't want that remove the line

Case 32 ' space

The simplest way to do the calc if you know the worksheet functions is to
use evaluate, for example

Evaluate("Average(1,2,3)/Round(123.456,1)")

although it is better to code it if you can.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"libby" wrote in message
...
Hi
There are two parts to this question.

1.
Is there any way I can make it so that only a numeric
value (preferrably within certain limits) can be entered
into a textbox?

2.
If the above is possible, then I have 3 textboxes which
are used to perform a calculation, the results of which
will be displayed in a label.
I know what the formula is on an excel sheet, using the
functions Average() and trunc(), but how would I code this
in vba?

Any help much appreciated

textboxes
 

Libby,

You can't limit the entry into a textbox, other than after the fact, but you
can check as the user enters values, using the change event of the textbox:

Private Sub TextBox1_Change()
Dim mytest As Double
On Error GoTo Bad:
mytest = CDbl(Me.Text)
Exit Sub
Bad:
MsgBox "You need to enter a number!"
End Sub

As for the formula, you should post your actual formula....

HTH,
Bernie
MS Excel MVP

"libby" wrote in message
...
Hi
There are two parts to this question.

1.
Is there any way I can make it so that only a numeric
value (preferrably within certain limits) can be entered
into a textbox?

2.
If the above is possible, then I have 3 textboxes which
are used to perform a calculation, the results of which
will be displayed in a label.
I know what the formula is on an excel sheet, using the
functions Average() and trunc(), but how would I code this
in vba?

Any help much appreciated

textboxes
 

Oops, I forgot to include .TextBox1 part:

mytest = CDbl(Me.TextBox1.Text)

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Libby,

You can't limit the entry into a textbox, other than after the fact, but
you
can check as the user enters values, using the change event of the
textbox:

Private Sub TextBox1_Change()
Dim mytest As Double
On Error GoTo Bad:
mytest = CDbl(Me.Text)
Exit Sub
Bad:
MsgBox "You need to enter a number!"
End Sub

As for the formula, you should post your actual formula....

HTH,
Bernie
MS Excel MVP

"libby" wrote in message
...
Hi
There are two parts to this question.

1.
Is there any way I can make it so that only a numeric
value (preferrably within certain limits) can be entered
into a textbox?

2.
If the above is possible, then I have 3 textboxes which
are used to perform a calculation, the results of which
will be displayed in a label.
I know what the formula is on an excel sheet, using the
functions Average() and trunc(), but how would I code this
in vba?

Any help much appreciated

textboxes
 

Bob,

The code would need to check for decimal points as well as number keypad
usage, so it would need three more cases: period, number keypad numbers, and
number keypad decimal.

Bernie

Here is a textbox event that only allows numeric input

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case 32 ' space
Case Else
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
Beep
End Select

End Sub

textboxes
 

Thanks Bernie, that is a more than good point. To be 'proper' it should also
cater for comma separators, and European usage of comma rather than decimal
point.

Might get around to all that one day<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

The code would need to check for decimal points as well as number keypad
usage, so it would need three more cases: period, number keypad numbers,
and
number keypad decimal.

Bernie

Here is a textbox event that only allows numeric input

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case 32 ' space
Case Else
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
Beep
End Select

End Sub