InputBox vs. UserForm for Numerical Data Input

[Chuckles123]

Thanks everyone -- I apologize for my confusing post, especially th
last paragraph -- I must have been fairly tired.

There is definitely a "handshaking" problem between my main macro an
my Private code. Here is my Private code:

Private Sub grpCUT_OFF_LEVEL_Click()
End Sub
_________________________________________
Private Sub inptCUT_OFF_LEVEL_Change()
Dim MIN, MAX
Dim Str As String
MIN = ActiveCell.Offset(-4, 4).Value * 100
MAX = ActiveCell.Offset(-2, 4).Value * 100

Again:
Str = inptCUT_OFF_LEVEL.Value
Level = Val(Str)
'If Level = False Then Exit Sub
If Level > MAX Or Level < MIN Then
MsgBox "Percentage must be between MIN and MAX"
GoTo Again

End If

MsgBox Level
End Sub
________________________________________________
Private Sub UserForm_Click()
End Sub

This is the relevant part of my main code:

frmCUT_OFF_LEVEL.Show
Range("PERCENT").Value = Level

PERCENT is a named cell in my WB.

First, things that are working:
By moving the cursor over the MIN and MAX variables when steppin
thru the Private code, I see the correct numbers. MsgBox Level i
displaying the correct number. That's the end of this list.

Things that are not working:
When the Form pops up, I am not able to input a number (th
Enabled Property is set to 'True'). What I was attempting to explai
in the last paragraph of my preceding post is that I would assign th
Value Property to the string "999"; when the Form popped up, I woul
scrape the rightmost 9 off the string with the BS key, then the macr
would continue stepping thru the Private code with the string "99" i
the text box. Keying the Enter key has zero affect on the text box.
Also, the Form does not 'go away' -- I have to click on the 'X' in th
upper right corner; I tried inserting "Unload Me" in the Private cod
for the Form itself, but that did not help.

Sharad,
I added a new module and inserted 'Public Level As Integer' in
macro by itself. This did not appear to change the above negativ
results.

Thanks again,
Chuckles12

 

[Sharad Naik]

1. Add a "command button" in the userform - CUT_OFF_LEVEL.
Name this command button as cmdCUT_OFF_LEVEL.
Change it's Caption to 'OK'.
2. Delete your code inpt_CUT_OFF_LEVEL completely.
3. Add following code in cmdCUT_OFF_LEVEL_Click() procedure:
Notice that the label Again: and Go to Again: is removed. This is
not required. :---

Private Sub cmdCUT_OFF_LEVEL_Click()
Dim MIN, MAX
Dim Str As String
MIN = ActiveCell.Offset(-4, 4).Value * 100
MAX = ActiveCell.Offset(-2, 4).Value * 100

Str = inptCUT_OFF_LEVEL.Value
level = Val(Str)
If level > MAX Or level < MIN Then
MsgBox "Percentage must be between " & MIN & "and " & MAX
Exit Sub
End If

Unload Me
End Sub

4. Again, keep the variable ' Level ' at module level outside procedure.
The TOP Most line in the module should be Public Level As Integer

Public level As Integer
Sub your_main_Code()
xxx
xxx
frmCUT_OFF_LEVEL.Show (Modal)
Range("PERCENT").Value = Level
xxx
xxx
End Sub

After entering a value in to the input text box, the user has to click on
OK.
If value is not within limits, MsgBox appears and form is not unloaded.
When the user input is within limits the form will unload, and after the
form is unloaded, then only code will move to Range("PRECENT").Value=Level.

Sharad