Error control dont work!

[Adam]

Hi

I have a userform with a textbox on it.

On loading it picks up a number from a cell on the spreadsheet. I want
a % sign in the textbox with the number, ie 15%. I use the following
code to achieve that.

UserForm6.TextBox5.Value = Format(Sheets("Rate
Table").Range("f4").Value, "###,##0.00""%")

When the user edits the textbox and press Enter the infomation is the
checked to make sure its a number and the entered back into the
spreadsheet. I use the following code to achieve that.

If IsNumeric(UserForm6.TextBox5.Text) & "%" = False Then GoTo
errorhandler:

ActiveCell.Offset(0, 5).Value =
Application.Substitute(UserForm6.TextBox5.Value, "%", "")

My problem is if the textbox is left blank or a letter is entered, it
does not error. Infact i dont think the error code works at all.

Please advise, i have run out of idea's

Thanks

 

[Nigel]

You could try taking the Value of the textbox. If it is <> 0 then you have
number, unless of course zero is a valid user entered value?

 

[Dave Peterson]

I'd load the userform with something like:

with worksheets("Rate table").range("F4")
if isnumeric(.value) then
me.textbox5.value = format(.value,"###,##0.00%")
'or if the cell is already formatted
'me.textbox5.value = .text
else
me.textbox5.value = "Invalid???"
end if
end with

And dump the userform with something like:

Dim myVal As Variant
Dim myStr As String
Dim FoundPct As Boolean

FoundPct = False
myStr = Me.TextBox5.Value
If Right(myStr, 1) = "%" Then
FoundPct = True
myStr = Left(myStr, Len(myStr) - 1)
End If

On Error Resume Next
myVal = CDbl(myStr)
If Err.Number <> 0 Then
'not numeric
Err.Clear
myVal = "Invalid"
Else
If FoundPct Then
myVal = myVal / 100
End If
End If

MsgBox = myVal 'or plop it into a cell