Excel form textbox control to accept only numbers?

I have 15 textbox controls in my Excel form that should accept only numbers and I found the following code to help me do so:





Private Sub TextBox1_Change()


OnlyNumbers


End Sub





Do I have to have these three lines for all 15 textboxes or is there a less cumbersome way to go about it? VB newbie here. Thanks again to others that have responded to my newbie questions.





Thanks.Excel form textbox control to accept only numbers?
Try this macro in a commandbutton click event on your form. Change the CommandButton reference number to your command button.





The macro will force entry in all 15 textboxes before the user can proceed.





Private Sub CommandButton1_Click()


Dim fCont As Control


For Each fCont In Me.Controls


If TypeName(fCont) = ';TextBox'; Then


If fCont.Value = ';'; Then


Me.Hide


MsgBox fCont.Name %26amp; '; is a required field.';, _


vbOKOnly, ';Entry Required';


fCont.SetFocus


Me.Show


Exit Sub


End If


If Not IsNumeric(fCont.Value) Then


Me.Hide


MsgBox ';You entered a non-numeric value, try again.';, _


vbExclamation, UCase(fCont.Name)


fCont.SetFocus


Me.Show


Exit Sub


End If


End If


Next fCont


Unload Me


End Sub





To remove the 'X' at the top of a userform, so the user must exit via a command button, use these functions and macro. I use this on all my userforms.





Open your userform in the VBE, and in the coding section before all other macros, copy and paste these functions in.





Private Declare Function FindWindow Lib ';user32'; _


Alias ';FindWindowA'; (ByVal lpClassName As String, _


ByVal lpWindowName As String) As Long


Private Declare Function GetWindowLong Lib ';user32'; _


Alias ';GetWindowLongA'; (ByVal hWnd As Long, _


ByVal nIndex As Long) As Long





Private Declare Function SetWindowLong Lib ';user32'; _


Alias ';SetWindowLongA'; (ByVal hWnd As Long, _


ByVal nIndex As Long, ByVal dwNewLong As Long) As Long


Const GWL_STYLE = (-16)


Const WS_SYSMENU = %26amp;H80000








Then copy and paste this macro below the functions.





Private Sub UserForm_Initialize()


'this hides the X on the caption line


Dim hWnd As Long, a As Long


If Application.Version = 8 Then


hWnd = FindWindow(';ThunderXFrame';, Me.Caption)


ElseIf Application.Version %26gt; 8 Then


hWnd = FindWindow(';ThunderDFrame';, Me.Caption)


Else


End If


a = GetWindowLong(hWnd, GWL_STYLE)


SetWindowLong hWnd, GWL_STYLE, a _


And Not WS_SYSMENU


End Sub








Note: this routine works for all Excel versions through Excel 2003. I am not sure about Excel 2007... haven't tried it. Each form must have these functions and macro to remove the 'Red' X.