vba user input box

Microsoft Office excel 2007
September 7, 2010 at 00:17:28
Specs: Windows XP
Hi all
i am trying to do a user input box which will actually allow me to enter a range of figure and then copy and paste this range to a new workbook...

for example, my input box should prompt : please enter a range
and then i will proceed to enter say -0.01 to 10,000
and the code is smart enough to pull this range of amount and paste it to another new workbook.,

any help especially on the user input box is greatly appreciated:)


See More: vba user input box

Report •


#1
September 7, 2010 at 03:58:34
re: "-0.01 to 10,000"

While you could certainly write code to extract the values - and leave out the "space to space" - I think the possibility error looms large.

Is there any reason you can't use 2 input boxes, one asking for the low end of the range and another asking for the high end?

re: "pull this range of amount and paste it to another new workbook"

What do you mean "pull it and paste it"?

Paste just the 2 values or paste a list of incremental numbers between the 2 values?

What do you mean by "another new workbook"?

Is the code supposed to create a new workbook and paste the values in? If so, paste them where?

We need a lot more details before we can offer any solutions.


Report •

#2
September 7, 2010 at 04:55:32
Hi,

Wouldn't it be better to ask the user for the low end of the range in one input box and then the upper end of the range in a second input box.
Then both inputs can be tested to see that they are valid numbers, and that the upper value is > lower value.

You could parse a single input, but you don't have any control how users will input the range:
1 to 2
1:2
1,2
etc.
Do you then boot the user out if they haven't used the format you expect, or do you add code to test for all possible variants of inputting a range.

Regards


Report •

#3
September 7, 2010 at 05:45:51
Hi,

Here is some code to parse two values from a single InputBox.

The code allows for "to" to separate the two numbers or ":" or a comma ",".
Accepted inputs are:
1 to 2
1:2
1,2

I just pasted the two values into two cells on another worksheet - as DerbyDad03 pointed out, we need a lot more information about what you are trying to do with these values, to be able to offer anything else.

Private Sub GetRange()
Dim strInp As String
Dim varLower As Variant
Dim varUpper As Variant

'get input
strInp = InputBox("Enter range as x to y", "Range data entry")

'range separators to , :
If InStr(1, strInp, "to") <> 0 Then
    varLower = GetLower(strInp, "to")
    varUpper = GetUpper(strInp, "to")
    ElseIf InStr(1, strInp, ",") <> 0 Then
    varLower = GetLower(strInp, ",")
    varUpper = GetUpper(strInp, ",")
    ElseIf InStr(1, strInp, ":") <> 0 Then
    varLower = GetLower(strInp, ":")
    varUpper = GetUpper(strInp, ":")
End If
'test for errors
If IsError(varLower) Or IsError(varUpper) Then
    'error message
    MsgBox "You did not enter two valid numbers" & _
            vbCrLf & "Please try again"
    ElseIf varLower >= varUpper Then
    MsgBox "First value must be less than second value"
    Else
    'move values
    Worksheets("Sheet2").Range("A1").Value = CDbl(varLower)
    Worksheets("Sheet2").Range("A2").Value = CDbl(varUpper)
End If

End Sub

Private Function GetLower(InpText As String, Delim As String) As Variant
Dim intDelim As Integer
Dim strLower As String
intDelim = InStr(1, InpText, Delim)
strLower = Trim(Left(InpText, intDelim - 1))
If Not IsNumeric(strLower) Then
    GetLower = CVErr(xlErrNum)
    Else
    GetLower = CDbl(strLower)
End If
End Function

Private Function GetUpper(InpText As String, Delim As String) As Variant
Dim intDelim As Integer
Dim strUpper As String
intDelim = InStr(1, InpText, Delim)
strUpper = Trim(Right(InpText, Len(InpText) - intDelim - Len(Delim) + 1))
If Not IsNumeric(strUpper) Then
    GetUpper = CVErr(xlErrNum)
    Else
    GetUpper = CDbl(strUpper)
End If
End Function


Report •

Related Solutions

#4
September 7, 2010 at 06:12:12
As I stated earlier, asking for a specifiic string (e.g. x to y) is asking for trouble.

However, if that string is needed for reasons unknown to us, I wouldn't even bother checking for any of the multitude of other possible combinations.

If the string doesn't contain " to " then tell the user and move on.

Sub GetInput()
getString:
 strInp = InputBox("Enter range as x to y", "Range data entry")
  If Not strInp Like "*? to ?*" Then
    MsgBox "Invalid Input" & _
            vbCrLf & "Please try again"
    GoTo getString
  End If
'etc.
End Sub


Report •

#5
September 7, 2010 at 19:12:22
yea actually you guys are correct.I should have put two text box one for upper limit and one for lower limit..

i manage to get it work out with the help of others.
below are my codes for reference purpose.My auto filter used to be like

ActiveSheet.Columns("AF:AF").AutoFilter Field:=1, Criteria1:= _
">=-20000", Operator:=xlAnd, Criteria2:="<=-0.01"

Dim Lower As Single
Dim Upper As Single

If IsNumeric(TextBox1.Value) Then
Lower = CSng(TextBox1.Value)
Else
MsgBox "Lower limit not valid"
Exit Sub
End If
If IsNumeric(TextBox2.Value) Then
Upper = CSng(TextBox2.Value)
Else
MsgBox "Upper limit not valid"
Exit Sub
End If




Columns("AF:AF").Select
Selection.AutoFilter
ActiveSheet.Columns("AF:AF").AutoFilter Field:=1, Criteria1:= _
">=" & CStr(Upper), Operator:=xlAnd, Criteria2:="<=" & CStr(Lower)


Report •

#6
September 8, 2010 at 04:30:13
Hi,

I wonder why you are using text boxes for data entry. Why not use input boxes that you can display to the user and 'ask' for data entry.

That allows you the opportunity to be interactive.

Regards


Report •

#7
September 8, 2010 at 18:31:38
mmm my text box does prompt when i execute my userform, allowing user to input the lower number and upper number,,

btw how do i allow my userform to show on the excel worksheet itself so that users can see them
currently, i have to run my code behind the screen before it gets prompted out.


Report •

#8
September 9, 2010 at 04:28:26
Hi,

You didn't mention that you had a user form.

Do you have UserForm1 or similar under Forms in the VB Project Explorer pane.

The general idea with a user form is that it is displayed and the user fills out fields, then clicks an 'OK' or 'Enter' button on the form, and the data is processed and entered into cells in the workbook.

The form is displayed using code such as this (in a standard Module):

Sub showform()
UserForm1.Show vbModeless
End Sub

The User Form has two text boxes for entering data, some associated Labels and Enter and Cancel command buttons. It might also have a Clear button, to clear the contents of the text boxes.

However for simple input - just two values you can use the visual basic code provided, that pops-up two Input boxes - each with a message - no need for a form.

In either case you need some trigger to show the form or start the visual basic code to display the input boxes.

The simplest is to embed a command button on the worksheet that the user clicks.
The code behind the button is either the call to display the user form or it is the visual basic code, suggested before.

currently, i have to run my code behind the screen before it gets prompted out
How do you get this code to run - what is the trigger to start it.
What do you mean by 'prompted out' - what actually happens.
If the code runs a User Form, are you using the Load command:

Load UserForm1
which loads the form but does not display it.

Further information on your User Form, especially how the user gets to start it and enter the Upper & Lower limit data, would help.

Regards


Report •


Ask Question