• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

How to create a input form and only allow numeric values

I am creating a form in Excel 2003 and I want to restrict the input to two things.

1. a fixed length and decimal length
2. Only allow numeric values.

Example.

343.22 not 343.2345

Thank you

Jim
0
jray9242
Asked:
jray9242
  • 9
  • 4
1 Solution
 
patrickabCommented:
Try this sub:

Sub inputform()
Dim x As Double

start:
x = InputBox("Enter value in the form xxx.xx", 123.45)

If Left(Right(x, 3), 1) <> "." Then GoTo start

MsgBox x

End Sub
0
 
patrickabCommented:
To install a sub in a module:

1) Press ALT+F11
2) Find the workbook name, right-click on it
3) Select Insert/Module
4) Double click on the new Module
5) Paste the code into the Module under Option Explicit
6) ALT + F11 to return to the worksheet
7) ALT + F8 to select and run the macro
0
 
patrickabCommented:
Miserable number of points, but the way.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
patrickabCommented:
This is perhaps a little better:

Sub inputform()
Dim x As Double

While Left(Right(x, 3), 1) <> "."
    x = InputBox("Enter value in the form xxx.xx", 123.45)
Wend

MsgBox x

End Sub
0
 
jray9242Author Commented:
Here is a little more information. I am hard working on it and this is what I have thus far. You can see I have a form I will call with 4 input options so, I am not sure the InputBox will work. Can something be done within the form I have here.

I wish I could attach the form because the visual would show more goodies. I also, can't find a place in the properties section to define this field as numeric.

Thanks again for the help thus far.

Private Sub CommandButton1_Click()
Dim Response

If txtKilos > 0 Then
    txtGrams = Round(Application.WorksheetFunction.Convert(txtKilos, "kg", "g"), 2)
    txtPounds = Round(Application.WorksheetFunction.Convert(txtKilos, "kg", "lbm"), 2)
    txtOunces = Round(Application.WorksheetFunction.Convert(txtKilos, "kg", "ozm"), 2)
ElseIf txtPounds > 0 Then
    Response = MsgBox("Do you want to continue txtPounds?")
ElseIf txtGrams > 0 Then
    txtGrams = Application.WorksheetFunction.Convert(txtKilos, "kg", "g")
    Response = MsgBox("Do you want to continue txtGrams?")
ElseIf txtOunces > 0 Then
    Response = MsgBox("Do you want to continue txtOunces?")
Else
    Response = MsgBox("Do you want to continue ?")
End If

End Sub
0
 
patrickabCommented:
I have the time but... I trust someone else will pick up this one.
0
 
Rory ArchibaldCommented:
You can post the workbook at www.ee-stuff.com. In order to upload to ee-stuff you need to do the following:
1. Zip your file.
2. Login with the same details as here
3. Switch to the Expert tab, and choose to upload a new file.
4. Enter the question number - 22753102 in this case - browse to your *zipped* file, then you must enter a comment of some sort (it doesn't matter what) and press Upload.
5. You should then be taken to a page with two links to the file on it - copy the second one and post it back here. If you do not see the links, then your file did not upload.
Regards,
Rory
0
 
patrickabCommented:
jray9242,

Here's a file that might meet your needs:

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/4312-jray9242_01.zip

Let me know how you get on with it.

Meanwhile I would be interested to know why, as a Premium Service Member, you offer such a miserable number of points for a question. Is it because the membership costs change if you 'spend' more points (I don't believe that's the case) or is it because the question is really very unimportant to you? 50 points are only offered for Unimportant Questions. If you look at the list of questions awaiting answers you'll see that the points you are offering are out of kilter with those offered by other people.

Perhaps you're not familiar with how the points system works. It works as follows:

Let's assume it's for a question for which 50 points have been offered by the Questioner:

Points offered    Grade         Multiplier      Answerer receives    Cost to Questionner
50                       A                4                 200                             50
50                       B                3                 150                             50
50                       C                2                 100                             50
      
As you can see it does not cost you more to award an A. However if you feel that your question has not been answered fully or for example you have only been given a partial answer then a B grade is fair. However it is only right that you say in what way your question has not been fully answered.

Hope that helps

Patrick

0
 
jray9242Author Commented:
partrick,

Your last post is more of the truth and I have always increased the points based on the answer. As I have several times increased the final points based on the complexity of the answer because it was harder than I expected it was and I have always given an A for the answer because I appreciated the help.

This wasn't a burning must have answer of more is it possible to do and I did reply that your answer wasn't quite what I was looking for and that could have been because I didn't explain it right. Still I would give an A if the answer given at a later date.

There is a method to my madness but I and fair with my points and always will be. That being said I appreciate your input and will take it to heart and consider it next time I post here. This place has a wealth of knowledge and is worth the price and have posted that statement many times before in my answers. At the same time, if the expert feels insulted by the points I am awarding for the answer, they have the right not to do so, but now I understand the process a bit better.

Thank you for taking the time out tho explain the process to me. I will close the question without the complete answer and go on from here.

Jim
0
 
patrickabCommented:
Jim,

Thank you for your courteous response. I suppose I tend to offer the maximum points as it grabs the attention of the experts more than a 50 point question - since I'm interested in getting the maximum input from as many people as possible - but then that's just me.

Meantime thanks for the grade.

Patrick
0
 
jray9242Author Commented:
You're welcome Patrick and I understand even more of the process and maybe I need more understanding of this so I can be even more fair.

Do Experts get paid for answering the questions and how do the points help you. Meaning what reward to you get by answering questions? Does this give you some sort of a higher rating or something? I know I pay a monthly fee to be a premium member and thought that was it, but if this helps those that helps us, I want to once again make sure I am be fair with the points I dish out.

This would be help and has given me another idea for when I post questions and that is be more specific in my questions and letting them know how important this answer if. If I need it right away then POW, a higher point will be given. If it is just a gee can this be done, well that is something else.

Thanks again for all that you do here.

Jim

0
 
patrickabCommented:
Jim,

You have taken my breath away - literally. Absolutely none of the Experts get paid anything at all for answering any question. We do it because of lots of different reasons. Without exception we are all volunteers. I do it because I like to learn and I like helping people. I feel I want to learn something new everyday - I'm 62.

It's worth noting that many of the top Experts are IT industry experts who just love helping people or who just want to display their skills - to good effect.

The points we earn give us a ranking which you will see in the forum in which we are reasonably good at. My area of interest is Excel, so my name is not in the visible list on this page. However if you look in the Excel forum you will find my name in the list on the right. We get our ranking only from the points we earn. That's the only visible reward we get.

If we earn enough points, 10,000 and then earn a minimum of 3,000 points per month thereafter we get Free Premium Membership. That entitles one to ask as many questions as we like and offer as many points as we like. For me like many others it's a pretty good option as the points are easy to earn. But I always remember that there are people who use the solutions/answers so I feel it's a fair exchange. Points for help. It'd be great if I earned something by it all but I can at least dream of such a possibility!

What it has done for me is got me to the stage where I am now contributing to a public organisation on a voluntary work basis and using my Excel knowledge to completely change how they are analysing their data. It's very satisfying and I get a kick out of it.

I suppose I look at points from a very generous stand. It costs me nothing and they're appreciated by the experts - so why not offer as much as I can and get the best possible attention from the experts. I know from personal experience that offering 50 points is almost self-defeating. I want questions that I ask to receive immediate attention and to get good answers - 500 points improves those chances. Of course if it is a truly simple question I offer fewer points - but that's rare. But 50 points - don't even go there - it's risible! And I mean that kindly. You have no need to be miserly about offering points - so don't be - that's my advice.

I hope all my ramblings give some pointers and explains a bit about this site - and perhaps about me!

Patrick
0
 
patrickabCommented:
&so2bed - it's 02:05 here!
0
 
jray9242Author Commented:
Excellent Patrick and I have a new found respect for those experts there and will never again take them for granite.

Your ramblings is helpful and hope others that read this will see the same as I now do.

Thanks again!

Jim
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now