I have a textbox on my userform that I am trying to add validation to i.e.
only allow a whole number between 1 - 21. I have recorded a macro to show
me the correct(?) code but I don't seem to be able to make the textbox the
active control apart from to take the focus?

With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="21"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = _
"You are trying to exced the total cube of the container 65,000 m3?
Please enter a figure between 1 - 21"
.ShowInput = True
.ShowError = True
End With

Please help if your able

Mark(InWales)

That isn't a textbox that you are adding validation to, it is a data
validation . This is getting added to the active cell. Try entering 22 in
the current cell, you will get the error.

--

HTH

RP

"Mark (InWales)" wrote in message
...
I have a textbox on my userform that I am trying to add validation to i.e.
only allow a whole number between 1 - 21. I have recorded a macro to show
me the correct(?) code but I don't seem to be able to make the textbox the
active control apart from to take the focus?

With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="21"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = _
"You are trying to exced the total cube of the container 65,000
m3?
Please enter a figure between 1 - 21"
.ShowInput = True
.ShowError = True
End With

Please help if your able

Mark(InWales)

Hi Mark,

As Bob noted, you recorded code related to Data Validation, which controls
data entry for Worksheet cells. If you are truly accepting input via a
TextBox on a UserForm, some code like this may help:

Private Sub txtDemo_BeforeUpdate(ByVal Cancel As _
MSForms.ReturnBoolean)
With txtDemo
If CInt(.Text) < 1 Or CInt(.Text) 21 Then
MsgBox "You have entered an invalid value " & _
"(must be Int between 1 and 21)."
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
End If
End With
End Sub

Private Sub txtDemo_KeyPress(ByVal KeyAscii As _
MSForms.ReturnInteger)
If KeyAscii < Asc("0") Or KeyAscii Asc("9") Then
Interaction.Beep
KeyAscii = 0
End If
End Sub


This assumes your TextBox is named txtDemo.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mark (InWales) wrote:
I have a textbox on my userform that I am trying to add validation to
i.e. only allow a whole number between 1 - 21. I have recorded a
macro to show me the correct(?) code but I don't seem to be able to
make the textbox the active control apart from to take the focus?

With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber,
AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween,
Formula1:="1", Formula2:="21" .IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = _
"You are trying to exced the total cube of the container
65,000 m3? Please enter a figure between 1 - 21"
.ShowInput = True
.ShowError = True
End With

Please help if your able

Mark(InWales)

Thank you both for your input. Yes I did use data validation and the trusty
old macro recorder to generate the code, I was hoping to make the textbox
become the selection instead of the worksheet.cell (just so you both know
where I was coming from!) as the recorder has been an absolute god send in
generating code in the past!
Thanks for your help

Mark (InWales)

P.S. Jake the code is excellent - thank you very much. Still so much to
learn...