Looping through userforms

[jinxtt]

Hi,

I am creating a spreadsheet whereby a user inputs information onto the spreadsheet via a series of different userforms. The last userform prompts the user if they would like to repeat the process.

Is it possible to create a counter that keeps track of the number of times that the userforms have been completed?

Many thanks
Jinxtt

[Norie]

Have you considered using one userform with a multipage control?

Each of the existing userforms could have their own page on the multipage.

[jinxtt]

No I haven't but that sounds like a good solution. I'm quite new to userforms and I'm not fully aware of all their capabilities. I'll have a go at using the multi-page option because at least I wouldn't require 5 seperate userforms which I have at present.

Thanks for you help.

[jinxtt]

I've had a look at multi-page userforms but they don't entirely solve the problem I have.

There are two options on the first userform, and the userforms that follow are dependent upon which option is selected. So if option 1 is selected on userform A, then userforms B and C follow. If option 2 is selected on userform A, then userforms D and E follow. On both userforms C and E, there is an option to go back to userform A whereby the process can be repeated. So userform A has to be seperate although userforms B and C, and seperately D and E can be on multi-page userforms.

It seems to me that I need a seperate module to control the flow of this macro but I don't have the foggiest how that would work. I assume there would need to be a counter to keep track of the number of times that the process has been repeated since each time the process is run and information captured, the information needs to be inserted by VBA into a line in a table in Excel, each time one line lower than the previous.

If anyone has any ideas I would be very grateful.

Thanks
J

[Norie]

You could hide all the pages except the one on which the initial selection is made.

Based on the selection made the appropriate pages are made visible.

Would that work for you?

[jinxtt]

Yes, that is how I've got it to run thus far. In fact I usually unload the userform before moving onto the next so that each is reset before the process is repeated. I'm probably just being silly and not seeing something blindingly obvious, but my real battle is inserting the information captured from the forms into the table in excel. How do I get VBA to insert the information from the userforms the first time it is run in Line 1 of my table, then the second time they are run insert the info into Line 2 and so on. The problem is referencing the information so that VBA can place it correctly in the table.

[Norie]

I think it might be worth seeing what you have.

Could you attach a sample workbook?

[jinxtt]

Here is the workbook. Although formatting is not great at the moment, you'll notice a blue buttion towards the bottom of the printable page on Sheet1. This activates the userforms I'm using. Just below is the table that I would like the information captured in the userforms inserted into.

If the process were carried out once, I would have no problem, but because the process in most cases needs to be run multiple times I am struggling with the insertion of the data into the table.

Any help would be great. Thanks

[Norie]

So you've sorted the main userform problem and just need help putting the data in the table.

Where should the data from each form go?

[jinxtt]

The info from the userforms needs to go below the column headings directly below the start button i.e. element, no, L, B, Size (angle) etc.
If "Plate" is selected (Equal Angle is another option but Unequal Angle can be ignored for now) in the first userform, the word "Plate" should appear beneath the element column on Sheet1. You will then be directed to the next userform whereby you would input the length and breadth of the plate, to be inserted under L and B on Sheet1. The final userform asks for position and orientation information which we can ignore for the time being. If you click on the button entitled "Another?" you are then directed back to the first userform.

I'm ok up to this point but this is when it gets very complicated. How do you get VBA to insert the next round of information gathered from the userforms into the line directly below the one which was just inserted? I was thinking of using a counter to keep track of how many times the process had been run but I'm not sure how that would work. Any ideas?

[Norie]

I kind of know which row the data is to goto and it should be straightforward to find it.

Please Login or Register  to view this content.

It's more which data from which form goes in which column/under which heading.

For example which form does the value for the Ay column come from? Is it bPlateDimensions, eAnglePositionandOrientation?

By the way, is the only difference in the eAnglePositionandOrientation and the other form with a long name the picture?

[jinxtt]

The other information is calculated directly within the cells on the spreadsheet. A stands for area and is calculated by multiplying Length by Breadth. For Angles it is different because the Area of each type is contained within a table on another sheet, which I'll import using VLookup in Excel.

y is the position of the centroid of the angle or plate which the user will need to know. This then gets multiplied by the Area A to produce Ay. Where this is leading is the section properties of steel girders - I'm a structural engineer by profession, definitely not skilled in the finer arts of programming. I am trying hard to improve though.

Yes, the last two userforms are very similar and in reality you are right, it is only the pictures which are different.

I will try using that line of code you suggest and see how that goes. I'll only get to do more work on it tonight so I'll let you know whether I've succeeded then. Thanks for your help so far.

[jinxtt]

Norie your suggestion worked perfectly. I'm now well on with my spreadsheet.

Many thanks for your help
J