Input text from a Form

[sowetoddid]

I am starting to build a form that will help the user add three additional bits of information to the workbook.

I have named the three input boxes on the form fEngineMake, fEngineModel, and fHorsePower.

Sheet 1 already has a listing of the Engine Makes in column A (i.e. Caterpillar, Waukesha, etc.). Column B then lists the Caterpillar models, column C the Waukesha models, etc.

When the user inputs the make and model into the form, it should search for an existing make. If it finds the make, then it should add the inputted model into the appropriate column. It would be added to the very end of the that column.

However, if they enter a new make, then the make should be added to the first available row of column A and the model in the first cell of the first available column on sheet 1.


Tongue twister, I know. If you have any ideas, please help.

[theDude]

You didn't provide any details on where the Horsepower data is and how to update it.
Therefore, I took a slightly different approach to providing a solution.

First, I created a separate worksheet in the workbook for each Engine Make and added 2 fields "Engine Model" & "Horsepower" (HP) to each Engine Make worksheet. Second, I created a User Form with 3 Combo Boxes instead of Text Boxes (you can fill the Combo Boxes with existing data to select from and they also allow data entry as well) and one OK button and one Cancel button (see the sample file 'Engines.xls' in the .zip attachment).

Since I'm not familiar with your data/requirements, the following assumptions were made in the VB code logic:
1) Different Horsepower values are allowed for any given Engine Model for each Engine Make:
Engine Model HP
Model 1 100
Model 2 100
Model 2 150
Model 3 330

2) Only unique Engine Model/HP combinations should be added to the data for any given Engine Make (no duplicates allowed).

3) Every entry must have an Engine Make, Engine Model and HP value before it will update the file.

4) A new worksheet is added for every new unique Engine Make entered.

Here is the code to copy to a module that opens the User Form for data entry:

Please Login or Register  to view this content.

Here is the code to copy to the User Form code page that executes when the User Forms' various controls are selected/updated:

Please Login or Register  to view this content.

Hope this helps,
theDude

[sowetoddid]

Thank you for the thorough post. The sample file helps tremendously. I am going to go through it and try to understand the steps and if it will do everything I am trying to do. Impressive.


Thanks

Luke

[sowetoddid]

Hi theDude.


I see the approach you are taking, and it is much more effective and detailed than what I was attempting to do. Let me back up for a minute and explain what I am ultimately trying to reach....


Every (Engine + Compressor) unit is given a unique identification number. The number is made up of eight digits. The first two represent the Engine make and model, the third and forth represent the Compressor make and model, the fifth represents the number of throws, the sixth the number of stages, and, finally, the seventh and eight make up a unique identifier number.

Right now, I manually generate these numbers, but I got into using Excel more and it would be nice to do it with input forms. This is the route that I was taking...


First to create a database where every possible make + model combination would have a number assigned to it. So, an engine user form would prompt for make, model, and horsepower (note the engine id number is based soley on make and model. Horsepower is stored for reference.). This form would be used soley to add new digit identifiers to the engine sheet.

Column A would list Make, B list the model, C the horsepower, and D the two digit identifier.

Second, an identical form would store the two digit identifier number for Compressor make and model. This form would prompt for only a make and an associated model. Exactly like the form that you already showed me, I would be able to select Make from a list and input a new model with a new two digit ID.


Third, a final form would prompt for the information to generate the unit number. The Engine make and model, the compressor make and model, a single digit number of throws, a single digit representing number of stages, and a final double digit that would be automatically generated. These inputs would generate tthe unit number. The form you demonstrated tells the user when a duplicate entry is attempted. Instead, one this form, it should recognize the duplicate entry and add the next sequential number to make it a unique entry (i.e. 00, 01, 02, etc)


I am ultimately trying to have a workbook with three sheets and three forms. One stores engine information and associated two digit IDs. The second stores compressor information and associated two digit IDs. The third is populated will all of the information I have about each unit and a unique ID number.


Is this something so involved that I would be better of generating the numbers manually?

Once again, thank you.


Luke

[sowetoddid]

theDude, first thank you for adding all of the explaination text in the middle of the code. I am really trying to learn this, and that helps. I know that you are answering a lot of people's questions, but if you have a minute....


What is the significance of <> -1 ???? It shows up several times in the code.


Also, what does Chr(13) with the '&' symbol do?



Again, thank you!

[theDude]

In response to your first question from your most recent post:

What is the significance of <> -1 ????

A combo box has a property called ListIndex. It is a numeric value that refers to the item that was selected by the user. If you think of a combo box list as a one-column spreadsheet, the ListIndex equates to the row number in the spreadsheet, however the first "row" is numbered starting at -1. This is the text area in the combo box where you can enter a new value into it. The first item in the dropdown list is indexed as 0, the second item is indexed as 1, etc. So, in my code, if the index equals -1, then the user entered new data in the combo box; conversely if the ListIndex does NOT equal (<>) -1, then the user selected an item from the dropdown of the combo box instead of entering new data.

In response to your second question from your most recent post:

Also, what does Chr(13) with the '&' symbol do?

From Visual Basic help:

The Chr function returns a string containing the character associated with the specified character code.

Numbers from 0-31 are the same as standard, nonprintable ASCII codes. For example, Chr(10) returns a linefeed character. The normal range for charcode is 0-255. However, on DBCS systems, the actual range for charcode is -32768 to 65535.

I used character code 13, which equals a linefeed character (same as 8,9,10 as well). It is used with the ampersand symbol '&' to concatenate a string of text on separate lines in the alert dialogs for a more elegant presentation to the user when the dialog is displayed.

In response to your question from your previous post:

Is this something so involved that I would be better of generating the numbers manually?

It is involved, however not necessarily unattainable. If you could provide more detail on the following questions I have, maybe I can provide what you need:

1) What are the possible values for:

a. Number of Throws

b. Number of Stages

2) Are the IDs only numeric? What happens if you exceed 100 combinations?

theDude

[sowetoddid]

Possible values for throw = 2 through 9
Possible values for stages = 1 through 9


IDs are numeric only. Good question on the possibility of reaching 100 identical units. That is a limitation, but I think the chances that I have that many identical units is slim to none. So, the range of 00 to 99 is assumed sufficient. I currently have no more than ten identical units.


Thanks for such a quick reply!

[sowetoddid]

theDude, would you mind explaining another part of the code to me. The last explainations you gave helped a lot.

Dim engMake, f, g, myArray()

The code above designates the four variables that will be used throughout the private sub? If that is true, why are f, g, and myArray() not defined as equal to some parameter? Also, why does myArray need to be 'Dim'ed once and then later ReDim?

My other question is if you can explain briefly what is happening in the code below. I am getting lost in the numbers. Significance of the codes used, etc?


ReDim myArray(Sheets(engMake + 1).UsedRange.Rows.Count - 2)
For f = 0 To Sheets(engMake + 1).UsedRange.Rows.Count - 2
myArray(f) = Sheets(engMake + 1).Cells(f + 2, 1)
Next f
' Fill ComboBox2 w/Engine Models from array
For g = 0 To Sheets(engMake + 1).UsedRange.Rows.Count - 2
ComboBox2.AddItem myArray(g)
Next g
ReDim myArray(Sheets(engMake + 1).UsedRange.Rows.Count - 2)
For f = 0 To Sheets(engMake + 1).UsedRange.Rows.Count - 2
myArray(f) = Sheets(engMake + 1).Cells(f + 2, 2)
Next f
' Fill ComboBox3 w/Engine Horsepower from array
For g = 0 To Sheets(engMake + 1).UsedRange.Rows.Count - 2
ComboBox3.AddItem myArray(g)
Next g
End If
End Sub



I can't thank you enough for helping me understand this. I have very limited experience in programming language and you are the first person I have found who can break it down to the point that it is clear.


THANK YOU!!!

[sowetoddid]

The line of your code that gets into processing the user data....under the "Else" statement...


nextMake = ComboBox2.ListCount + 2


I searched in VB Help and ListCount is suppose to be 0 if there are no objects in the Engine Make pull-down box. Then it would consecutively count for each item in the list. I know what you are saying is right, because it works! But, why do you add "2" to the total count of the list.

If, for example, I had one item in the list, then it would count 1 and add 2. I am confused ~



....Let me know if I am exhausting you. :] Many many thanks!

[theDude]

In response to your question:

The code above designates the four variables that will be used throughout the private sub? If that is true, why are f, g, and myArray() not defined as equal to some parameter? Also, why does myArray need to be 'Dim'ed once and then later ReDim?

The Dim statement declares the variables to be used in the code. Ideally, you also assign a data type (integer, string, etc.) to the variable when you declare it, i.e. Dim f as Integer, because each data type requires a different allocation of memory. If you don't explicitly declare the data type, it defaults to type Variant, which allows numbers, strings, etc., but it requires more memory. Since your data sets are small, I didn't feel it necessary to declare explict data types (also, I'm lazy and it requires more typing on my part!). You then assign values (of the correct data type for the variable or an error will occur) to the variables later in the code.

The variable myArray is an array that can be dimensioned in the Dim statement or left 'empty' when declared - Dim myArray() - and then re-dimensioned during run-time of the code with the ReDim statement. Think of the array like a spreadsheet, with rows & columns. You can dimension it like this (x equals the number of rows, and y equals the number of columns if you want more than one column in your array - Dim myArray(x,y) - or don't specify columns and it defaults to 1 - Dim myArray(x). In my code, I leave it 'empty', then re-dimension it to the size I need at run-time (more on this later).

In response to your question:

But, why do you add "2" to the total count of the list
If, for example, I had one item in the list, then it would count 1 and add 2. I am confused ~.

No, you're not confused; it does exactly what you think it does. Hopefully I can explain the logic of the formula for the variable 'nextMake'...

ComboBox2.ListCount is the count of items in the dropdown, which was populated from the Engine worksheet, but the worksheet also contains a header row. I use the variable nextMake as the row number of the first empty row in the worksheet to populate w/data from what was entered/selected from the user form with this code:
Sheets(ComboBox1.ListIndex + 1).Cells(nextMake, 1).Value = engModel (more on this later as well...)

So the first empty row in the worksheet = The count of rows with data (ComboBox2.ListCount) + 1 (Header Row) + 1 (the empty row)...hence the '+ 2'.

In response to your request:

My other question is if you can explain briefly what is happening in the code below

I use the array to fill with data from the worksheet and then fill the combobox list with the data from the array. Arrays are like combobox/listbox lists in that they are 'indexed', however with an array you can specify the start of the 'index' referred to as its' Option Base. The default is 0 (you don't have to write any code, which I didn't because I'm lazy), or you can specify it to be 1 with the global declaration 'Option Base 1'. If you make this declaration, it applies to ALL arrays in the module.

So, I use a For...Next loop to run through the cells in the worksheet and add the cell's value to the array by referencing both the array's index and the worksheet's cell location with the variable 'f'...

The loop runs the number of times equal to the row count (less 2; - 1 to remove header row and subtract 1 more for the Option Base starting at 0) from the worksheet 'Sheets(engMake + 1).UsedRange.Rows.Count' assigning a value starting with 0 to populate the array index items - myArray(f) - with the value from a specific cell in the worksheet (adding 2 to the row number to skip the header row and add 1 more for the Option Base starting at 0 vs worksheet row index starting at 1) - Sheets(engMake + 1).Cells(f + 2, 1)...

A similar process is then used to populate the dropdown list in the combbox from the items in myArray.

Hope this makes sense,
theDude

[theDude]

I revised my original design to incorporate your specifications, but I used a MultiPage control to allow use of just one form instead of three. MultiPage1 of the form is where you enter engine model/make/horsepower configurations. MultiPage2 of the form is where you enter Compressor model/make configurations. MultiPage3 of the form is where you enter Throw/Stage configurations (Throws = 1 to 9, Stages = 2 to 9).

My revised solution now has three worksheets:

1) The 'Engines' worksheet contains unique Engine model/make/horsepower configurations with a two-digit ID number assigned automatically when new data is added.

2) The 'Compressors' worksheet contains unique Compressor model/make configurations with a two-digit ID number assigned automatically when new data is added.

3) The 'Configurations' worksheet contains all of the data for any configuration you create with an eight-digit ID number assigned automatically using your XXYYAB00 numbering methodology (XX = EngineID, YY = CompressorID, A = Throws, B = Stages, 00 = ConfigID).

The form will not allow data to be written to the worksheets unless values are selected/created in every field on the form. The code will only allow unique engine model/make/horsepower configurations to be added to the 'Engines' worksheet, and only unique compressor make/model configurations to be added to the 'Compressors' worksheet. You will get an alert in either/both cases if duplicate data was added in the form page(s) when form data is uploaded to the worksheets. It will create a new configuration in the 'Configurations' worksheet (sequentially numbered 00, 01, 02, etc.) even if a matching configuration has already been created.

See the attached .zip file 'Revised Solution' to extract the 'Engines2.xls' file.
(I left the original UserForm in the revised version for code comparison.) To execute the new version, run macro 'enterData2'.

NOTE: I always try to post the VB code directly in the thread, but the length of the code exceeds the limitation of the total number of characters allowed (10,000) in a thread, so you will have to extract the sample file to view it. To the best of my knowledge, no malicious spyware, viruses, etc. exist in the .zip file contents or the VB code.

Enjoy!
theDude

[theDude]

It appears that the attachment didn't make it in my previous post, so here's another attempt...

theDude

[sowetoddid]

I apologize for not responding sooner. I was pulled away to travel for work in a town that barely have phones, let alone a reasonable internet. I am going to look at the attachment now.


Thanks a million, Dude.

[sowetoddid]

My computer has been acting up lately...Is it just me, or the "bad achive" error occurring for others on the attachment. The attachment would not open.

[theDude]

No, it's not you...My apologies!!! I neglected to check if the posted zip file would open after I posted it. Here's another attempt...

Sorry for the inconvenience,
theDude

[sowetoddid]

theDude.


My apologies for not getting back to you sooner. That file is incredible! That is exactly what I was trying to do...only yours actually works.

The one thing that am I going to see if I can figure out is....changing the way the consecutive number works at the end of the unit number. The file is setup to add 1 to every new unit. I want to get it so that the last to digits for every unit default to "00". However, if an identical unit is added to the list, then it would be identified by the last to digits being "01". The next identical unit would be given "02". Then if I add another new unit and it is not identical to any of the other units on the list, its last two digits would still be "00". Just as the other example...as soon as I added another one of this particular unit (identical) it would be "01" and the next "02"...so on down the line.

[theDude]

Luke,

I REALLY APPRECIATE YOUR PRAISE & THANKS!!!

I apologize for misunderstanding your requirement for generating IDs. Just in case you get stuck trying to implement it yourself, I have revised the code to meet your requirement. Refer to the CommandButton3_Click code in UserForm2 in the attached sample file. I notated the new code section and left the old code (disabled) for comparison.

Have fun!
theDude

[sowetoddid]

theDude, that is EXACTLY what I was trying to accomplish.

Hey, first thing...you can't possibly know how helpful it is when you put all of those comments in your code. For a person trying to learn VB, it helps tremendously.

Second thing, thank you for sticking with me on this one.

Hopefully I can someday learn this stuff as well as you seem to already know it. I will definitely be studying this attachment to help me get there.


Luke

[theDude]

Hi Luke!

I'm glad my solution worked out for you and am very grateful for your appreciation of my efforts! I am by no means an expert and took on your request because it allowed me an opportunity to not only assist you, but also to expand my knowledge & expertise as well. THANKS for the challenge!

I do my best to provide detailed comments in my code for several reasons:
1) It helps to keep track of the code logic for testing/debugging.
2) It (hopefully) allows for expanding the knowledge of more inexperienced developers than I who are eager to learn.
3) It provides details for future developers during the software development lifecycle who may need to modify the code...
ACTUAL STORY - I was recently asked to upgrade a VBA solution I provided over 4 years ago that I had almost forgot it even existed, and was completely surprised it was still in use!

Best wishes in your future VBA projects!

Thanks again,
theDude