Need Excel Data Form to permit editing of entries

I made an error in opening up another thread on this subject, and I deeply apologize. I could not edit this thread, as the system indicated, so I stupidly followed through with another question.

I found that a "Do Until" loop would permit the Excel Data From to remain open but the loop restricts me to an "X" number of trys.

I am using Excel 2000 SR-1, and have constructed the following maco to to keep an Excel Data Form open so that existing line items can be selected and the Units and/or the Unit Price columns be filled with changes.

Without this macro the Excel Form does not accept more than one edit at a time, and so to take effect the form must be closed and re-opened for the next edit.

This Macro is working, but it limit me to the DO Until Counter = X.

How can I make this a variable, or better yet set the limit high, but allow the User to break out of the Macro without losing any changes made.

Sub EnterUnits()
'
' Macro to show form for entering Units
' &/or changing Unit Prices
'

Counter = 0

Do Until Counter = 3

Counter = Counter + 1
Sheets("Invoice Master").Select
Application.Goto Reference:="Database"
ActiveSheet.ShowDataForm
Loop
Close

End Sub

All I need is a way to setup the Counter after inspecting each Manually prepared Sales Order to determine the number of line items that are going to be invoiced, and then have the macro refer to that count somewhere on the Worksheet.

How can I refer to a cell on a worksheet that becomes the Counter to be used by my Do Until Loop macro???

How can I use the reference below as the Counter??

Do Until Counter = Sheets("Sales Order").Select
Range("N7").Select

My own gut feeling is that you have not designed your user form correctly.

Is the userform generated by code or designed within VBA using the insert form facility?

If the latter then the inclusion of a cammand button that translates the current value of the form 'textboxes' to a cell would seem the obvious answer. The following code is an example of what could happen when you clicked the command button.


If I am barking up the wrong tree then please accept my apologies.

Dear Jengor:

Thank you for the reply.

This is a standard form that Excel provides when you highlight a range with labels in the 1st row. This form presents a problem in that the User must close out the form after each edit of existing entries is made.

It is does not part of the VBA User forms, nor part of the Excel Control forms Box.

I chose this form because it is the most pratical one for the task.

I need help with the Macro's Counter???

The Macro permit retaining the Form open for as many edits as needed but if you set the Counter to 10 (within the code) when you may only need 5 the form will continue appearing for another 5 times.

A bit difficult to imagine the use of your form but I assume that when you want to enter the edit mode you know how many edits you wish to do?

If you know the number then reserve a cell to contain that number, for example Cell L1 could contain the number then the code would be as follows:


HTH

Dear Jengor:

Thats it. you found the way to limit this macro from repeating endlessly.

Yes the Use is for the entering of sales Oquantities and/ or to change selling prces to a given customer on a Sales Order Worksheet in a range that acts as a Master listing of Products, & services for billing.

Thanks a million. You made my day.