Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
i dont know what file to name when registering Calender Control | Excel Discussion (Misc queries) | |||
Formulas dont work | Excel Discussion (Misc queries) | |||
Error Control no work :( | Excel Programming | |||
Error control no work :( | Excel Programming |