Solved: only numbers

[white_flag]

hello

I found this code on net but in my case it is not working. this will not aloud to insert letters in an text box
with F8 I saw that on condition if :
[vba]If TypeName(Me.ActiveControl) = "TextBox" Then[/vba] go directly to end, End if ..so what it is wrong?


[vba]

Private Sub TextBox11_Change()
OnlyNumbers
End Sub

Private Sub OnlyNumbers()
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With

End If
End Sub
[/vba]
thx

[p45cal]

Works OK here (except potentially irritating to the user when all his input is scrubbed if he accidentally enters just one letter!). Where have you placed this code?

[white_flag]

on the new sheet, it is going..but on this one (see attach) not. why? no idea

[p45cal]

Because the active control typename turns out to be "MultiPage1" (!?)
For robustness use:[vba]Private Sub TextBox1_Change()
OnlyNumbers TextBox1
End Sub

Private Sub TextBox11_Change()
OnlyNumbers TextBox11
End Sub

Private Sub OnlyNumbers(TB)
If TypeName(TB) = "TextBox" Then
With TB
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With
End If
End Sub
[/vba] …but do you need to be testing if it's a textbox at all if you're only calling it from a textbox's event handler?:[vba]Private Sub OnlyNumbers(TB)
With TB
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With
End Sub[/vba]

[white_flag]

very nice thx (how can you see more of how the code is acting, more then F8)

and the second issue (if it is possible)

on the same sheet (attacht) the drop down list from combo1 is using the sheet "Catalog" if the code start in different sheet I have an error #13 (I do not know to tell to combo to take the populated data just from specified sheet).

[p45cal]

You can add a line such as:
Debug.Print TypeName(Me.ActiveControl)
which shows up in the Immediate pane.

As to the second problem[vba]Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
Dim rngNext1 As Range
Dim myRange1 As Range
Dim lCount As Long
Set wsSheet = ThisWorkbook.Sheets("catalog")
With wsSheet
Set rngNext1 = .Range("A5").End(xlUp).Offset(1, 0)
'rngNext1.Select'don't select stuff if it might not be on the active sheet
Set myRange1 = .Range("a59", rngNext1) 'dot added
'populate Group combobox list
End With 'this has been moved down (and because I've coloured it red is not showing properly indented at this website)

With ComboBox1
For Each rngNext1 In myRange1
If rngNext1 <> "" Then
.AddItem rngNext1
.Column(1, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 1).Value
.Column(2, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 2).Value
End If
Next rngNext1
End With
End Sub
[/vba]

[white_flag]

excellent ..thx a lot for your time and for sharing your knowledge

have a nice day