PDA

View Full Version : Text Box list order



Chris66
05-02-2006, 05:47 AM
I have a spread sheet that has a text box which requests the user to enter a starting number which will be in column B, then it ask for an ending number which will be in column B as well. Once it finds these to numbers, it runs some calculations and copies the range to another area. My concern is that if someone should accidentally enter the ending number first, the program will crash. How can I prevent that from happening. I would like to have a message box saying something like "Wrong order". My text box names are TXTSRCOP for the starting number and TXTERCOP for the ending number. Thanks

Sir Newbie
05-02-2006, 07:02 AM
I don't know a lot about text boxes.

I don't know a lot about a lot of things.

But I do know the following...

My help file tells me:

'A TextBox is the control most commonly used to display information entered by a user.'

I think 'display' is the operative word here :)

Maybe you could create a button and paste the following code in the code window.


Prompt = "Please enter the 1st number."
Title = "First number."
Response = InputBox(Prompt, Title)
Worksheets("sheet1").Cells(1, 2).Value = Response

Prompt = "Please enter the 2nd number."
Title = "Second number."
Response = InputBox(Prompt, Title)
Worksheets("sheet1").Cells(2, 2).Value = Response


Keep in mind that if the user types a letter (as oppossed to a number) this may create headaches for you.

You may need to validate, or check, the user's 'response' before any further calculations are made.

Then again, you might want to use a drop down list/combo box (or whatever they're called) if you already know the numbers that the user can select/input.

Hope this helps!

See ya later :)

Chris66
05-02-2006, 07:08 AM
I basically have that, I am concerned about the exception to the rule occurring. I need to be able to tell the user that order they entered the release is not in the same order it appears on the schedule. (These are not sequential numbers, 4A350-18 can come before 4A320-18).

Bob Phillips
05-02-2006, 07:44 AM
Why do you not just test the input values before running the rest of your code, and use the lower as the start, the higher as the end number?

Sir Newbie
05-02-2006, 07:48 AM
Whoops.

It might help the both of us if I think before opening my mouth :)

At the moment, I'm thinking that if there is some consistent correlation or connection between the first and second number (or any other numbers or even other variables, such as the date) then these correlations/patterns can then be used to verify the user's data.

This may be a dumb question to ask, but why are the numbers not sequential?

Can you give us some more info?

Chris66
05-02-2006, 07:49 AM
the lower is not always the starting or ending, I can have a schedule that runs from 4A320-18 to 4A310-18 then the next schedule can run 4A321 to 4A325-18. But they do appear on the spreadsheet in the order I am running them.

Chris66
05-02-2006, 07:54 AM
I have often asked why they are not sequential myself...the short answer is we use multiple databases so the scheduler will run down the intial demand, assign a release number then group them to optimize production. The only thing that is consistent it the order they appear on my spreadsheet is the order I am building them in. I could, I guess loop a formula into the spreadsheet 1 through whatever and use that to test, I don't know if that is the most effcient way though. I really do appreciate everyone's time on this. I feel like a leach on this board in that I can only take info. Hopefully some day I can contribute something.

OBP
05-02-2006, 08:14 AM
Why not use two Combo boxes for the selection, when the first selection is made it resets the second combo's selection to start it's list from the previoulsy selected value?

Chris66
05-02-2006, 08:58 AM
I have a box that askes for the starting release and then another box for the ending release. Is there a way to say, if the end release value comes before the starting release in column B, say "hey wrong order". I have attached an abbreviated version of my spreadsheet. You will be able to tell from the way it is coded, that I have no experience. Sorry if this gives you a headache

Norie
05-02-2006, 09:12 AM
Chris66

The codes you are working with are not sequential, but are the rows they are in sequential?

If they are I think you could use OBP's suggestion.

You would populate 1 combobox with all the codes.

You would then use it's change event to populate a 2nd combobox but based on the ListIndex of the 1st one.

OBP
05-02-2006, 09:13 AM
Chris free form text boxes are not a good idea for inputting requested data due to users making spelling mistakes, entering the non existent releases etc. It is far better if they can select the value(s) from a list.
Looking at your Worksheet this is a very good application for and Access Database.

Sir Newbie
05-02-2006, 09:15 AM
Well I've gotta go to bed.

But before I go...

A quick shout out to OBP :) and some thoughts...

Am I missing something?

Initially, I was assuming that the numbers were on a piece of paper and (sadly) that thought stuck with me.

But...

If I've got the numbers on a spreadsheet, all I would do is put the "start" and "end" numbers in separate columns and then run a loop so that the user's data can be verified (and hopefully matched) with either the original "start" or "end" number. This may not be suitable, because those original numbers may be doubled up (i.e. they are used more than once and thus appear on the spreadsheet in more than one location, or cell).

However, this solution (if you can call it that) seems too simplistic.

I'm sure I must be missing something.

Oh, that's right... I need some sleep :)

Good luck!