Loop to execute SAVE code as defined number of times

[aman1234]

Hi All

When a userform is loaded then initially a textbox appears asking total number of documents. And then when the user types the number and press tab then the next textboxes appear:
1. Policy no
2. Batch no
3. Document type
4. Document Prov

This Quantity textbox drives the number of iterations per input on the form after SAVE button pressed although policy number and batch number textboxes can be left populated.At each SAVE, the values of document type and Document Prov are loaded into the memory of userform FrmReturn.

The following code will keep on running as I haven't defined how many times it has to run. If there are 5 documents then the code will run 5 times and then after last iteration FrmReturn will be displayed. Can anyone please help me in this?

Please Login or Register  to view this content.

[Norie]

If the value in the textbox determines the quantity can't you use that for the no of iterations?

[aman1234]

Please can u help me how to do that? If I use for loop from 1 to textbox1.value then it doesn't work as whenever the SAVE button is clicked the loop starts from 1 always. But I want to start from 1 initially when SAVE is first pressed and then second time it should start from 2 and so on until textbox value .

Thanks

[Norie]

Sorry I don't follow.

The first time you click Save you want to loop from 1 to the value in the Quantity textbox, the next time you want to loop from 2 to the value in the Quantity textbox and so on?

[aman1234]

Suppose the user types total number of documents = 4 in the textbox1

When the SAVE button is pressed first then the code is executed and in the same form Policyno,batch number textbox values remain same but DocType and DocProv automatically gets cleared so that user can choose next document so now when the SAVE BUTTON pressed then its 2nd time and then 3rd time and last 4th time . After this the userform FrmReturn will pop up .

I hope you understood what I mean.
Thanks

[Norie]

What's the purpose of the quantity textbox? You don't seem to be using it, you also don't seem to be looping anything.

[tfurnivall]

Hi aman1234

It might, perhaps, help if you were able to describe:
i) The business purpose of what you are trying to do, and
ii) Using non-computer language, how you are trying to accomplish it

When I look at your spreadsheet, the absence of any meaningful nmes makesit very hard to figure out these two things, and they are, as I hope you will agree, somewhat fundamental to the problem you pose.

To follow up on Norie's responses.

If you want to do something a certain number of times, and that number is in a text box, then you simply use the value of the textbox as the controlling variable for a loop.

If you want to uses the value in the textbox as a guide for when you need to stop doing something, then you declare an additional variable, and do whatever you want to do using the new variable as the controlling variable.

However, I think that a clear statement of what your are trying to accomplish - in a business context, and how you are trying to accomplish it (in non-technical terms) will be the biggest help!

Tony

[aman1234]

Thanks guys.. Ok one more try from my side to explain what exactly I want...

I want to declare a variable somewhere (may be public or anything like that) and when the user types something in quantity textbox in Frmmain then that variable should hold the value and everytime SAVE button is clicked that value should be decremented by 1 each time and when its 0 then just open a form frmReturn and if its more than 0 then keep on entering document type and document Provenence for the same batch number and policy number and press SAVE.

Did i make it clear now?

Many thanks for your help so far. Now which variable can I declare in a module so that its value gets update when used in the form.

Thanks

[tfurnivall]

Hi aman1234,

Here's a skeleton for you to work from. To play with it, open the workbook, and type Alt+F11 (to start up the VBA environment. Then double click userform1, on the left hand side, and type F5 to start the form running.

HTH

Tony

[aman1234]

Thanks tfurnivall for your help. It worked perfectly fine.