Database Creation (i.e) Saving and loading user inputs

Dear All,

Can somebody help me out in generating a macro for Database Creation (i.e) Saving and loading user inputs as Iterations - I have clearly mentioned the requirements in Excel sheet enclosed (Please refer S.No.1 & S.No. 2)

Requesting you to please get me an Excel Macro for this.

Thanks in advance...

Hi Prakash;

You have designed some nice forms, but I don't think they are necessary. You already have the data entered on the sheet. Why do you want to then transfer the data to a form?
Just have a button that copies the data from Sheet1 to the sheet where you want it saved.
Create a ListBox that has all the saved Bearing Names in it, that triggers a macro that retrieves the saved data.

I would be willing to write macros that transfer data back and forth between Sheet1 & Sheets("Saved Bearings") (or whatever).
But if you want to transfer the data to the form 1st, and then from the form to another Saved Bearings sheet, and have a form transfer data from Saved Bearings to a form and then to Sheet1, that is a lot more work.

Hi Foxguy,

Thanks a lot for your response and support.

As you have mentioned, the steps that i have explained in the excel sheet involve more work. But thats how, we want this database to be done. We are trying to make the design calculations tool to be more user-friendly by incorporating GUI (using VB forms).

I can explain you the requirements still clearly.

S.No. 1.
I dont want the data already present on "sheet1" to be transferred to a form..
- I actually want those data to be taken as user-input when "Bearing" Command button is clicked. That's I have designed a form which can take in bearing name and respective Input Parameters.
- After providing inputs in the form, these values should be stored (as 6 x 1 column matrix) in separate worksheet for future reference, whenever we click "Save Iterations" Command button.
- This process of saving iterations should be repeated for all five iterations mentioned above (guess we can use 'for loop')
- Using "Load Iterations" command button, it should be able to grab data from saved iterations and populate values in the form. For this purpose, once we press "load iterations" cmd button, a drop-down list box (with all previous iteration/bearing names) should be popped up, from which required Iteration should be chosen. Once we select an already existing iteration from drop-down list box, it should be capable of populating all stored inputs in the corresponding text-boxes in the Form..

S.No. 2.
I need the same operations to be performed for S. No. 2 for Table inputs. In this case, I guess the input values should be stored and retrieved in the form of 10 x 9 (m x n) matrix.

I hope this explanation would give you better understanding. Please let me know, if you have any queries / clarifications.

I am new to Excel VBA Macros. So, requesting you to please help me out in developing an Excel VB Macro for this requirement.

Thanks in Advance...



-------------------------------------------------------------------------------------------------------------------------------

Originally Posted by foxguy

Hi Prakash;

You have designed some nice forms, but I don't think they are necessary. You already have the data entered on the sheet. Why do you want to then transfer the data to a form?
Just have a button that copies the data from Sheet1 to the sheet where you want it saved.
Create a ListBox that has all the saved Bearing Names in it, that triggers a macro that retrieves the saved data.

I would be willing to write macros that transfer data back and forth between Sheet1 & Sheets("Saved Bearings") (or whatever).
But if you want to transfer the data to the form 1st, and then from the form to another Saved Bearings sheet, and have a form transfer data from Saved Bearings to a form and then to Sheet1, that is a lot more work.

Hi Prakash;
I think I understand better, but not completely.
I do not think I need the answer to these questions. I think I can show you how to do what you are asking for. But if you answer them, they might help.

UserForm3 is where the user enters the information, and "Save Iterations" puts the information into S.No. 1. S.No. 1 is not the actual location to save iterations. The iterations will be saved on their own sheet. S.No. 1 is just a sample of what you think it will look like. Is this correct?

- This process of saving iterations should be repeated for all five iterations mentioned above (guess we can use 'for loop')

If you want the user to enter 5 iterations, why is there only room for 1 iteration on UserForm3?
Will there ALWAYS be 5 iterations to enter at the same time?

Hi foxguy,

thanks again for your response.

Please find below, my replies for your queries..

UserForm3 is where the user enters the information, and "Save Iterations" puts the information into S.No. 1. S.No. 1 is not the actual location to save iterations. The iterations will be saved on their own sheet. S.No. 1 is just a sample of what you think it will look like. Is this correct?
Yes, you are absolutely right..

If you want the user to enter 5 iterations, why is there only room for 1 iteration on UserForm3?
Will there ALWAYS be 5 iterations to enter at the same time?
As you have mentioned, In UserForm3, we have provision to enter only 1 iteration. Then, I want to add the remaining iterations (2,3,4,5) one by one using "Save Iterations" Command button. In this process, each iteration will be saved one by one as shown in S.No.1. Then, once we press "load iterations" cmd button, a drop-down list box (with all previous 5 iterations/bearing names) should be popped up, from which required Iteration should be chosen. Once we select an already existing iteration from drop-down list box, it should be capable of populating all stored input parameters in the corresponding text-boxes of the UserForm3.
There may be more iterations for other applications (>5 - May go to a max of 10). So, May be, as next step, can you please generalize your VB code to handle more inputs (i.e., saving and loading more inputs as iterations).. but, as of now, for S.No. 1, you can develop VB code handling 5 iterations

Please repeat the same procedure for S.No.2 wherein it involves saving and loading Table Input Parameters (2 Iterations)..

Hope this helps.. Please let me know if u have any queries / clarifications.

Thanks in Advance...

Originally Posted by foxguy

Hi Prakash;
I think I understand better, but not completely.
I do not think I need the answer to these questions. I think I can show you how to do what you are asking for. But if you answer them, they might help.

UserForm3 is where the user enters the information, and "Save Iterations" puts the information into S.No. 1. S.No. 1 is not the actual location to save iterations. The iterations will be saved on their own sheet. S.No. 1 is just a sample of what you think it will look like. Is this correct?


If you want the user to enter 5 iterations, why is there only room for 1 iteration on UserForm3?
Will there ALWAYS be 5 iterations to enter at the same time?

Hit post too soon. See next message

Hi Prakash;

Originally Posted by reachspk123

If you want the user to enter 5 iterations, why is there only room for 1 iteration on UserForm3?

Hi Prakash;
Will there ALWAYS be 5 iterations to enter at the same time?
As you have mentioned, In UserForm3, we have provision to enter only 1 iteration. Then, I want to add the remaining iterations (2,3,4,5) one by one using "Save Iterations" Command button. In this process, each iteration will be saved one by one as shown in S.No.1. Then, once we press "load iterations" cmd button, a drop-down list box (with all previous 5 iterations/bearing names) should be popped up, from which required Iteration should be chosen. Once we select an already existing iteration from drop-down list box, it should be capable of populating all stored input parameters in the corresponding text-boxes of the UserForm3.
There may be more iterations for other applications (>5 - May go to a max of 10). So, May be, as next step, can you please generalize your VB code to handle more inputs (i.e., saving and loading more inputs as iterations).. but, as of now, for S.No. 1, you can develop VB code handling 5 iterations

I still do not understand why you want a For 1 to 5 loop

I'm sorry that I didn't get back to this last night. I also don't have very much time today. So I only worked on "Bearings". Hopefully, it shows you how to do things, so you can set up "Speed Ratios" yourself.

I have attached a file that will save a new Bearing iteration in the next unused column. As you can see I have added a new column as a test. If you type in a new "Bearing Name" in UserForm3 and click "Save ..." it will automatically add a new column to put it in.
I have also fixed UserForm1 so it gives you a choice of all saved Bearing Names, and loads the choice into UserForm3, like you asked.

I renamed the buttons in UserForm3 so that I could tell which one was clicked.
I strongly recommend that you rename all objects, that have code attached to them, with a name that describes their function (TB1 is ok as long as that means something to you, but CommandButton_232 does not mean anything to anyone). That includes renaming the UserForms to things like "frmEditBearing", "frmChooseBearing", etc. (the "frm" on the front of the names is always a good idea. It lets other programmers know that it is a form.)
I also changed the ComboBox in UserForm1 to a ListBox. A ComboBox allows you to type in a new name, but you said that UserForm1 was to select from previously saved names.

I put the macros that do most of the work in the FoxGuy module, so you can easily find them. You need to find the macro "Definitions" and change the addresses in it to match your real workbook. I assumed that the Bearing Names were the only thing in that row.

I also added a new worksheet called "Hide This Sheet". Put it into your workbook and hide it. I don't use listboxes in forms, so I couldn't figure out how to add a horizontal RowSource to the ListBox. That's why I added the new hidden sheet, so that it could convert the Bearing Names into a vertical list, and I could add it as the RowSource in UserForm1.

There is a small bug in what I set up.
1) Load UserForm3
2) Click "Load Iterations"
3) Edit selected iteration
4) Type in a new iteration name
5) Click "Save Iterations"
6) Click "Load Iterations"
The new iteration name will not be in the list of saved iterations.
Bearing Names get put into the UserForm1.ListBox only when UserForm1 is created.
You need to put a button on UserForm1 to "Refresh List", or "Unload UserForm1" after you call it from some other form.
I didn't want to assume how you wanted it handled, so I left it for you to decide.

Hi foxguy,

Thank you so much for your support..

Can you please attach the updated Excel file with VB code? I guess you forgot to attach it in ur previous post..

Thanks!!

Originally Posted by foxguy

Hi Prakash;


I still do not understand why you want a For 1 to 5 loop

I'm sorry that I didn't get back to this last night. I also don't have very much time today. So I only worked on "Bearings". Hopefully, it shows you how to do things, so you can set up "Speed Ratios" yourself.

I have attached a file that will save a new Bearing iteration in the next unused column. As you can see I have added a new column as a test. If you type in a new "Bearing Name" in UserForm3 and click "Save ..." it will automatically add a new column to put it in.
I have also fixed UserForm1 so it gives you a choice of all saved Bearing Names, and loads the choice into UserForm3, like you asked.

I renamed the buttons in UserForm3 so that I could tell which one was clicked.
I strongly recommend that you rename all objects, that have code attached to them, with a name that describes their function (TB1 is ok as long as that means something to you, but CommandButton_232 does not mean anything to anyone). That includes renaming the UserForms to things like "frmEditBearing", "frmChooseBearing", etc. (the "frm" on the front of the names is always a good idea. It lets other programmers know that it is a form.)
I also changed the ComboBox in UserForm1 to a ListBox. A ComboBox allows you to type in a new name, but you said that UserForm1 was to select from previously saved names.

I put the macros that do most of the work in the FoxGuy module, so you can easily find them. You need to find the macro "Definitions" and change the addresses in it to match your real workbook. I assumed that the Bearing Names were the only thing in that row.

I also added a new worksheet called "Hide This Sheet". Put it into your workbook and hide it. I don't use listboxes in forms, so I couldn't figure out how to add a horizontal RowSource to the ListBox. That's why I added the new hidden sheet, so that it could convert the Bearing Names into a vertical list, and I could add it as the RowSource in UserForm1.

There is a small bug in what I set up.
1) Load UserForm3
2) Click "Load Iterations"
3) Edit selected iteration
4) Type in a new iteration name
5) Click "Save Iterations"
6) Click "Load Iterations"
The new iteration name will not be in the list of saved iterations.
Bearing Names get put into the UserForm1.ListBox only when UserForm1 is created.
You need to put a button on UserForm1 to "Refresh List", or "Unload UserForm1" after you call it from some other form.
I didn't want to assume how you wanted it handled, so I left it for you to decide.

E

Hi Prakash;

Sorry about that.

Hi foxguy,

Thanks a lot.. these are the functionalities that we were looking for..

You did mention about the minor bug while saving iterations.. As you ve mentioned, whenever we save more than 7 iterations & try to load them, it's not getting displayed in the list of saved iterations.. Also, I find the newly added iterations (more than 7) with "Iteration name" are not getting stacked/stored in 'Hide This Sheet'.. Can you please explain me how to add iterations more than 7?
& you told me to put a cmd button to "Refresh List" or Unload "Userform1".. Can you please tell me or incorporate how to perform these events (Refresh/Unload)?

Also, If we have to delete an existing iteration, can you please add this feature similar to Save/Load Iterations? i.e., Place a "Delete Iterations" command button similar to "Load Iterations". Once we press ""Delete Iterations" command button, a drop-down list box (with all previous iteration/bearing names) should be popped up, from which required Iteration should be chosen. Once we select an already existing iteration from drop-down list box, iteration should be deleted from the sheet & also from "Load iterations list box"?

Thanks in Advance,

Originally Posted by foxguy

Hi Prakash;

Sorry about that.

Hi Prakash;

I believe that if you study my macros, you will be able to do most of this yourself. I do not like to do everything for you, because then you will need me to make small changes in the future. I can not guarantee that I will be willing to do everything in the future for free. The more that I do for you now, the harder it will be for you to make your own changes in the future. I enjoy doing this for people but I do not want you to be dependent on me if you do not want to be.

If you still want me to do it (for free), I will need a few questions answered.

& you told me to put a cmd button to "Refresh List" or Unload "Userform1".. Can you please tell me or incorporate how to perform these events (Refresh/Unload)?

Right now, you can close the workbook. When you open the workbook, it will refresh the list on "Hide This Sheet" when you click "Load Iterations".
I did not study all the macros that you have already written. Is Userform1 used for "Speed Ratios" or anything other than Bearings? If it is only used for "Bearings", then I would move the macro "Bearing_RowSource" into Userform1. Right now it is in a standard module for any form to use, but if no other form uses it then it should be inside the userform.
To "Refresh List" with a button, add a button that runs "Bearing_RowSource" when you click it. You can see how I did it in "UserForm_Initialize".
But the better choice is to "Unload Userform1" when it is not being used. That way every time Userform1 is opened it will refresh the list.

Also, If we have to delete an existing iteration, can you please add this feature similar to Save/Load Iterations?

To delete a Bearing Iteration, I would need to know how "Bearings" are really stored. Are they on a separate sheet from "Speed Ratios"? Is there anything else on the same sheet? Or is it really stored like your example?

If you want me to do these things. I would need to understand why you want to Loop the bearings data entry 1 to 5. Is it possible that you might only want to add 1 Bearing Iteration to the list? Or will there ALWAYS be 5 to add to the the list?

Hi foxguy,

Thanks for your reply..

Bearing inputs are stored in separate sheet.. The sheet that I sent you was just an example.. "Speed Ratios" will be stored in another worksheet.. No. of bearing iterations is not fixed.. For this application, we use 5 bearings.. Sometimes, the no. of bearings may reach upto 25.. It would be quite useful if the loop can handle 25 iterations..

Userform1 is not used for "Speed Ratios" or anything other than Bearings.. Userform1 is just a duplication of Userform4.. So, we can make use of any one of them for loading/deleting iteration..

For your kind info that I am in the process of understanding your VB code.. this is the first time, I am doing an assignment using VB codes in Excel.. as i am a beginner, i hope I ll pick up and try to master VB programming as early as possible..

It would be of great help, if you can help me for this assignment to the maximum extent possible..

Thanks in Advance
---------------
Hi Prakash;

I believe that if you study my macros, you will be able to do most of this yourself. I do not like to do everything for you, because then you will need me to make small changes in the future. I can not guarantee that I will be willing to do everything in the future for free. The more that I do for you now, the harder it will be for you to make your own changes in the future. I enjoy doing this for people but I do not want you to be dependent on me if you do not want to be.

If you still want me to do it (for free), I will need a few questions answered.


Right now, you can close the workbook. When you open the workbook, it will refresh the list on "Hide This Sheet" when you click "Load Iterations".
I did not study all the macros that you have already written. Is Userform1 used for "Speed Ratios" or anything other than Bearings? If it is only used for "Bearings", then I would move the macro "Bearing_RowSource" into Userform1. Right now it is in a standard module for any form to use, but if no other form uses it then it should be inside the userform.
To "Refresh List" with a button, add a button that runs "Bearing_RowSource" when you click it. You can see how I did it in "UserForm_Initialize".
But the better choice is to "Unload Userform1" when it is not being used. That way every time Userform1 is opened it will refresh the list.

Originally Posted by foxguy

To delete a Bearing Iteration, I would need to know how "Bearings" are really stored. Are they on a separate sheet from "Speed Ratios"? Is there anything else on the same sheet? Or is it really stored like your example?

If you want me to do these things. I would need to understand why you want to Loop the bearings data entry 1 to 5. Is it possible that you might only want to add 1 Bearing Iteration to the list? Or will there ALWAYS be 5 to add to the the list?

Hi Prakash;

Don't quote entire messages. They just clutter up the thread and use space on the server. The moderators will get angry with you if they see you quoting entire messages. Just quote the part of my message that you need. Also, when you quote entire messages, it takes my time to read through to find your answer.

It would be quite useful if the loop can handle 25 iterations.

You are still not answering. Why do you need a loop? Userform3 can stay open for 1 iteration or 1000 iterations. It just keeps entering iterations until the user wants to stop.
Are the iterations entered 1 at a time? If they are entered 1 at a time, why do you need a loop? I think the only reason you should use a loop is to guarantee that a certain # of iterations is entered. Is there a minimum or maximum # of iterations to enter at a time?

It would be of great help, if you can help me for this assignment to the maximum extent possible.

(see how I only quote the part of your message that I'm answering).
As I said, the more I do for you, the harder it will be for you later. Do you want me to just answer questions, or do you want me to write stuff for you? I will not do everything for you. I will do things the first time, but when you need to do the same thing a second time, you will need to look at my example and do it yourself.

Bearing inputs are stored in separate sheet.

Can you send me a copy of the actual workbook. Take out all the sensitive data. I just need to see how it is formatted, and actual sheet names, and things like that. I will try to make it as easy as possible for you to just move my macros and forms into your workbook, but I need to see how it currently looks.

Hi Foxguy,

Originally Posted by foxguy

Hi Prakash;
Are the iterations entered 1 at a time? If they are entered 1 at a time, why do you need a loop? I think the only reason you should use a loop is to guarantee that a certain # of iterations is entered. Is there a minimum or maximum # of iterations to enter at a time?

There are no limitations in number of iterations. So, the existing capability should be good enough.
But I find that whenever i try to save more than 7 iterations, I am getting a peculiar error message stating "Error in loading dll". To be precise, i saved 8 iterations, then saved and closed the workbook. Then if i open it again & try to load any iteration, the above mentioned error is displayed.

Originally Posted by foxguy

Can you send me a copy of the actual workbook. I will try to make it as easy as possible for you to just move my macros and forms into your workbook, but I need to see how it currently looks.

Please find attached, the actual workbook (I have not included the computation (formulae) part and other details of Input Parameters such as Bushings, seals, torque ratios, etc.,)
1. In Design Layout worksheet, we have Bearings & Speed Ratios Command Buttons. Other command buttons such as Input3 to Input 9, etc, represent other Inputs.. I will extend the same procedure that you developed for Bearings to other Input Parameters...
2. Inputs_Database worksheet should store the Bearing Inputs as shown. And other input parameters will be stored sequentially in adjacent columns
3. PowerFlow_Ratios worksheet should store the speed ratios as shown. I will try to implement the procedure for speed Ratios on my own.

Originally Posted by foxguy

To delete a Bearing Iteration, I would need to know how "Bearings" are really stored. Are they on a separate sheet from "Speed Ratios"? Is there anything else on the same sheet? Or is it really stored like your example?

I think now, it will be clear how "Bearings" inputs are stored. They are stored on a separate sheet from Speed Ratios. So, now, can you please add "Delete Iterations" feature as I explained in yesterday's post.

Thanks!!

Hi Prakash;

Do bearings have to be stored horizontally? If they were stored vertically it would make it easier to design the data entry forms. You wouldn't have to have a Hidden Sheet. Also, the user could see more bearings on 1 sheet.

Hi Foxguy,

Originally Posted by foxguy

Hi Prakash;

Do bearings have to be stored horizontally? If they were stored vertically it would make it easier to design the data entry forms. You wouldn't have to have a Hidden Sheet. Also, the user could see more bearings on 1 sheet.

Bearing inputs iterations need to be stored in vertical format as shown in the file attached in my previous post. If I am right, I think your macros already store the Bearing iterations in vertical format (or like column matrix)..

Thanks!!

Hi Prakash;

Sorry this took so long. I was having all kinds of problems until I discovered that Userform3 had text boxes hidden behind other text boxes. And since you didn't have the tab order set, it took me a long time to discover it.

You can play around with this file as much as you want.
There are a few things that you might not like.
frmBearings is set so that an "Enter" key automatically clicks the "Done" button (unless the cursor is on another button). The user uses the "Tab" key to move from textbox to textbox. This is normal data entry procedure. If you don't like it, then select the "Done" button, click "F4" and change the "Default" property to "False".
Also, I set the tab order so that "Save Iteration" comes after "TB5". That way when the user enters something in TB5, the cursor moves right to the "Save Iteration" button.
One last thing. When the user clicks "Delete Iteration" or "Save Iteration", when it's done it clears the textboxes and moves the cursor to txt_Bearing_Name, so that the user can start another iteration.


When you are ready to move my stuff into your workbook:
Copy all the modules named "FoxGuy" into your workbook. Also copy forms "frmBearings" and "frmSelect". Assign the macro "Bearings_Display_1" to your "Bearings" button.

Hi Foxguy,

Many thanks for your support!!! These were the exact capabilities that we were looking for!!

Originally Posted by foxguy

When you are ready to move my stuff into your workbook:
Copy all the modules named "FoxGuy" into your workbook. Also copy forms "frmBearings" and "frmSelect". Assign the macro "Bearings_Display_1" to your "Bearings" button.

I have moved your macros to my actual Workbook. I have tested the Save/Load/Delete operations for "Bearings" Input Parameters. It works perfectly fine.. Thanks again..

Then, I tried to extend this procedure to incorporate second set of Inputs (Bushings Input Parameters).
For that, I have created two worksheets named "Bushings_InputsDB" and "Hide This Sheet_Bushings".
I think I have made appropriate changes in the macros. I've created new modules named "Bushings" and "BushingsDisplay_Buttons" & created a form named "frmSelect" and assigned the macro "Bushings_Display_1" to your "Bushings" button. (In the process i.e., using your macro, I learnt more in depth about VB code development such as creating functions, etc., I felt its quite a lot of learning for the past few days).. Thanks a lot..
Please find attached the actual workbook after above implementation..

Now, if I click the command button "Bushings" in Design_Layout sheet, I am getting an error message which state 'Cannot Set Name "Bushing_Names".. Aborting.. "Cancel to Step Through Code'. I am stucked now. Can you please check my macro implementation and provide your inputs in debugging this error.. Apologies for bothering you again..

Thanks!!

Hi Prakash;

You found a bug in my macros. The reason it crashed is because Bushings_InputDB!E7 was empty. It tried to create a range 0 columns wide.

I will fix it.

Also, I did not anticipate you duplicating all my macros. I am surprised that they worked. I thought you would just add onto the ones already there. I will combine your new macros into mine, so you can see how to do it. That way you will not have to do so much work to add the next one.

Hi Prakash;

Here's a new file.

I think all you have to do to add more items:

Please Login or Register  to view this content.

Hi Foxguy,

Application works perfectly fine.. Thanks you so much for your help.. Following the above procedure, I have added 20 different components (with all input parameters) to the workbook!
Now comes the most important part.. I need to automate Output Calculations using Input Parameters & Plots (XY Scatter Plot) generation. I will try myself to write VB code for these functionalities. I will keep you informed about the progress..

Thanks again for your knowledge sharing. It was quite a lot of valuable learning for me for the past few days..

Sincerely,
reachspk123

Originally Posted by foxguy

I think all you have to do to add more items:
[CODE]

Hi Prakash;

Don't forget to mark the thread solved and rate the answer if you liked it.

Hi Foxguy,

I have marked the thread solved and also 've rated the answer. Thanks a lot for your help and support..

I have some doubts in Automation of Output calculations and Plots generation.. I will open up a new thread for this problem. hoping for your support again..

Thanks,
reachspk123

Originally Posted by foxguy

Hi Prakash;

Don't forget to mark the thread solved and rate the answer if you liked it.

Originally Posted by reachspk123

I have some doubts in Automation of Output calculations and Plots generation.. I will open up a new thread for this problem.

Hi Foxguy,

For your kind info: Please follow the below thread for continuation of the previous problem:
http://www.excelforum.com/excel-prog...ml#post2347179
for Automation of Output calculations and Plots generation.. hoping for your support again..

Thanks,
reachspk123