Userform with validation...

[Mark \(InWales\)]

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)

 

[Bob Phillips]

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.

 

[Jake Marx]

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\)]

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...