Error control dont work!
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 |
Error control dont work!
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 Adam wrote: 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 -- Dave Peterson |
All times are GMT +1. The time now is 02:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com