Loop that waits for a subroutine to execute

[Aristizabal95]

Hi! First of all I have almost no experience in VBA, meaning that my programming in this case may be pretty bad. I need a program that stores data into continuous cells in a worksheet. This data is entered through a textbox, which has it's own subroutine that executes after updated. My Idea is that the loop executes, but waits until the subroutine finishes before continuing, meaning that the user should be able to change the data for each cell. Since the number of data is established through a dynamic array, I must use the same textbox to change all the data values. My question is how can I make a loop wait until a subroutine linked to a textbox update finishes?? Im not sure if you understand me, since I don't really speak english. These is my code:

Private Sub TxtBxCmnMatArray_AfterUpdate()
Sheets("Listas").Range("E3").Value = TxtBxCmnMatArray.Value
End Sub
Private Sub TxtBxCmnMatRange_Enter()
Dim a As Integer
Sheets("Listas").Range("E4").Activate
For a = 1 To Sheets("Listas").Range("E3").Value
Call TxtBxCmnMatRange_AfterUpdate
ActiveCell.Offset(1, 0).Activate
Next a
End Sub

Public Sub TxtBxCmnMatRange_AfterUpdate()
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = TxtBxCmnMatRange.Value
End Sub

Sadly, the loop doesn't wait for the sub to be executed, counting the number of cells specified through "TxtBxCmnMatArray.Value" without allowing the user to enter the desired values on each cell.

Any help is appreciated.
Thanks

[AlphaFrog]

This prompts (instead of using textboxes) the user for the number of entries and then for each entry. Would that work for you?

Please Login or Register  to view this content.

You can change the Prompt's messaging to suit.

[Aristizabal95]

Thanks for the fast response! It works just fine! And it looks better than my first design! Im not sure if this can be fixed, and is no much of a big trouble, but when I enter the first data, the main window minimizes seeming like it closed. If it can get fixed easy then it would be great! If it doesn't well doesn't matter, It works just fine like that, Thanks!

[Aristizabal95]

Another thing! Is there a way to stop the loop if the user hits the cancel button? I see you placed a code there that allows the user to exit the sub, but im not sure if it can be linked to the cancel button, since if the user pressed the button by mistake, he would need to press cancel "i" number of times. Thanks alot

Ok, I see what you made: If I write "False", the loop exits, so is there to change that code so it exits when pressed cancel??

[AlphaFrog]

Thanks for the fast response! It works just fine! And it looks better than my first design! Im not sure if this can be fixed, and is no much of a big trouble, but when I enter the first data, the main window minimizes seeming like it closed. If it can get fixed easy then it would be great! If it doesn't well doesn't matter, It works just fine like that, Thanks!

There is nothing in the code I provided that would minimize your excel window. I don't know why that would happen.

Do you have some other Event-type macro (e.g. Worksheet_Change) that is doing the window minimizing?

[AlphaFrog]

Another thing! Is there a way to stop the loop if the user hits the cancel button? I see you placed a code there that allows the user to exit the sub, but im not sure if it can be linked to the cancel button, since if the user pressed the button by mistake, he would need to press cancel "i" number of times. Thanks alot

Ok, I see what you made: If I write "False", the loop exits, so is there to change that code so it exits when pressed cancel??

If you used my exact code, it should exit the loop when you press cancel. Pressing cancel returns the "False" result.

[Aristizabal95]

I copied the dimensions and the loop part, since the part of assigning an array value im doing it somewhere else, I'll check then