Private Sub xxxx_Change

[OscarAlberto]

I wrote this code to enter information on cells and to provide the user with inmediate feedback regarding his-her input: negative numbers, non numeric input, empty textbox when input required, etc. I found that the input is made, but after I hit the OK button, the data dissapears from the worksheet. Please provide help. Thank you in advance.

Private Sub TextBox60_Change()
Call ObjectVarDeclar (declares object variables)
Dim Msg As Variant

If ((TextBox60.Value > 100) Or (TextBox60.Value < 0) Or (IsEmpty(TextBox60.Value))) Then
Msg = MsgBox(vbCrLf & vbCrLf & vbTab & _&quot;INVESTMENT PLAN - SECTION #2&quot; & vbCrLf & vbCrLf & _
&quot;You must ENTER a NUMERIC VALUE, GREATER than zero, and LESS or EQUAL to 100&quot;, vbOKOnly, _

Else: Assumptions.Range(&quot;InvestmentPlanSecc1&quot.Offset(59, 1) = TextBox60.Value / 100
Assumptions.Range(&quot;InvestmentPlanSecc1&quot.Offset(59, 1).NumberFormat = &quot;#0.00%&quot;
End If
End Sub

 

[Zathras]

I can't reproduce your problem. Since you mentioned an OK button, I assumed your text box is on a form.

Further assuming that the range name &quot;InvestmentPlanSecc1&quot; is assigned to cell A1, when the form is closed. the value entered remains in cell B60 with the format as set.

I had to tweak the code by replacing the comma following vbOKOnly with a right parenthesis to allow it to compile (in addition to inventing the ObjectVarDeclar sub to create a worksheet object named &quot;assumptions&quot; and hitting return after the underscore in front of &quot;INVESTMENT PLAN&quot.

Perhaps if you provided a little more information (and/or a little more code), I would be able to reproduce your problem.