[VBA, Excel] How to Dynamically Update Cell References in Invoice Program with InputBox

Hello,
I searched the forum but found no solution.
I am a new VBA user and I have never programmed before, but I built an invoicing program for myself, before that someone helped me edit the code correctly, maybe this time someone will enlighten me for the future.

I have an invoice file in which, when you click one of the buttons, windows pop up to complete subsequent sections of the invoice.
Unfortunately, at some point I realized that when I use the code to enter data into a cell, e.g. Range ("C5"), the data will always be entered into cell C5, but in the case of invoices, - with more positions, the cells move, but my inputboxes and all the rest of the boxes still stick data "rigidly". I missed it in a clever way as you can see, but unfortunately it let me down because in addition to the patting of the textures.
at the end, it copies all these data to the invoice database, and here's the problem, because ctrl + f will not find me things in the text fields
here is my code:
How can I make the data fall into cell C5, unless it moves down, then it has to enter the data, for example, to C6 or C15 there

The assignments are probably in the code of some UserForm. Upload a sample file with all elements (personal data should be removed or changed to fictitious).

The code change will be the same everywhere.
I'll try to explain better.
I have cell C5 in which he enters the customer's order number, using the inputbox. Everything works great and until instead of 6 products the invoice has 7 products, so it adds a new line with the product, which automatically moves cell C5, in which the order number should be one line lower, so now my inputbox has to insert the order number into cell C6 and not C5. But with the above code it will always insert into C5 - how to uelsatic the code to take the shifts into account?

mysticmario wrote:

... I have a C5 cell where I enter the customer's order number, using the inputbox. Everything works great and until instead of 6 products the invoice has 7 products, so it adds a new line with the product, which automatically moves cell C5, in which the order number should be one line lower, so now my inputbox has to insert the order number into cell C6 and not C5. But with the above code it will always insert into C5 - how to uelsatic the code to take the shifts into account?

yes maybe
mysticmari...eszyt1.zip Download (12.12 kB)

The simplest fix that you can make without knowing anything more about the data entry sheet - it creates some circular reference (?) ...

Subsequent lines:
" next_row = Application.CountA (Range ("c: c")) "
and
" Range ("c" & next_row + 1) .Value = something_ there "

they enumerate the previously occupied cells in the 'C' column and add new data to the first free one below them.

I put in a file I have torn from the data, on which I have macros, etc.
includes cancellations and recurring ones when entering a date so that it doesn't change every file run.
Please do not pay attention to the aesthetics of the code, as I have already said, this is a work of my science vba in 2 weeks.
You need to change the extension to xlsm.
I hope it will help.
To use a file, unprotect the sheet, or click "create new fact" then make a new copy of the file and unlock it.
Best regards,
Mariusz Gola

mysticmario wrote:

I have cell C5 in which he typed customer's order number using inputbox. Everything works great until when instead of 6 products the invoice has 7 products

I see your project in black with such "precision" of action and description
In all invoice variants from your file in cell C5 you have either the "invoice issue date" or "goods issue date" however, the above-mentioned "customer's order number" is nowhere to be found on these invoices

When it comes to the specification of goods and services, where there may be a dilemma of the number of lines needed in a given invoice and moving with entries from the first to the next line - it suggests using formulas in specific invoice templates, e.g. as in the screenshot
2017-04...png Download (41.54 kB)

In your macro code, you only need to provide dynamic adjustment print area to the parameter of this template = MAX (Lp).
In your project you seem to have completely forgotten about it: what will the macro print or save in pdf?

I also noticed that you are trying to duplicate the codes in your project -
you create, for example, a code for UserForm3, and then in "Your last child" - module10, you again try to enter the name of the person issuing the invoice In my opinion, this UserForm code is more convenient if you do this project for yourself or your own company.

mysticmario wrote:

... includes cancellations and recurs when a date is entered, so that it doesn't change every file run.

Line with code:

Should be replaced with:

In cells' C5 'i' C6 'no function will be introduced, only the result of text formatting of the function' Today () '

Further:

mysticmario wrote:

...
1. I have a C5 cell in which I enter the customer's order number, using the inputbox.

2. Everything works great and until the invoice has 7 products instead of 6 products,
3. adds a new line with the product, which automatically moves cell C5 to me, where the order number should be one line lower, so now my inputbox must insert the order number into cell C6, not C5. But with the above code it will always insert into C5
...

Ad 1.
Into the cell ' C5 'and in' C6 'are introduced' date 'resulting from the macro code, not' customer order no '.
I understand here that that ' customer order no 'then in fact' invoice number '(?)

Ad 2. and Ad 3.
hmm ... The whole snag is that no data from the inputbox dialogs are typed into any cells, but into text fields.
By adding a new row in the table with invoice items, for example: in the " currency PLN, ENG 0% "the only thing that can be 'moved' are the text fields below the table: ' REF ',' REQ ',' AFTER ',' General description 'i' VAT rates '
mysticmari...ls_002.jpg Download (45.7 kB)

To avoid 'certain unwanted behavior' of text fields, you can select the appropriate options in their properties:
mysticmari...ls_001.jpg Download (48.58 kB)

or

play around controlling the position of these text boxes from within the code:

However, when it comes to 'code flexibility' in this particular case, the option to completely rewrite the forms should be considered, so that each entered data has its own cell or range in the worksheet. It is easier then to do anything than to move the text boxes point by point on the screen of the monitor with the macro code, because it is rather a tedious job ...

Ps: This 'software' shifting of text fields is a bit like knitting a form like this in a text file, using white space, tabs, etc.

mysticmario wrote:

... works great and until instead of 6 products, the invoice has 7 products, so it adds a new line with the product, which automatically moves the C5 cell, where the order number should be one line lower, so now my inputbox has to insert the order number into C6 cells, not C5. But with the above code it will always insert into C5 - how to uelsatic the code to take the shifts into account?

hmm ... after reading the sent 'book for invoices', I say that it can actually be very 'painful' in everyday use ... ...

Please see the following example of a 'light treatment' of its functionality ... maybe it will be useful in something.
Copy of FA...TURA02.zip Download (66.63 kB)Points: 1.5 for user

The amendments are based on:
1. 'Automate' data entry based on 'reference points' in the worksheet.
Such are the table headers or certain phrases appearing 'probably' constantly in the invoice sheet: ' REF. ',' REQ. ',' AFTER: ',' Issued by ', etc.

2. throwing the content of the drop-down lists of forms into a separate sheet - it will be much easier to add new ones and write off unnecessary items
If something does not appear on any of the lists, check the given form UserForm whether by any chance the range reserved for the list items in the sheet was exceeded and corrected accordingly:

3. Automation of 'cleaning' of the invoice sheet for new entries

4. options to fix formulas that count the values of amounts in the table

An example based on an invoice ' currency PLN, ENG 0% '- if it works, it will have to be adapted to other terminology' reference points 'in a given invoice.
The rest of the 'remarks' in the modules ...

Ps: I left the text fields to compare what and where is entered in the new version of the sheet

lanzul wrote:

mysticmario wrote:

... includes cancellations and recurs when a date is entered, so that it doesn't change every file run.

Line with code:

Code: VBScript

Log in, to see the code

Should be replaced with:

Code: VBScript

Log in, to see the code

In cells' C5 'i' C6 'no function will be introduced, only the result of text formatting of the function' Today () '

Further:

mysticmario wrote:

...
1. I have a C5 cell in which I enter the customer's order number, using the inputbox.

2. Everything works great and until the invoice has 7 products instead of 6 products,
3. adds a new line with the product, which automatically moves cell C5 to me, where the order number should be one line lower, so now my inputbox must insert the order number into cell C6, not C5. But with the above code it will always insert into C5
...

Ad 1.
Into the cell ' C5 'and in' C6 'are introduced' date 'resulting from the macro code, not' customer order no '.
I understand here that that ' customer order no 'then in fact' invoice number '(?)

Ad 2. and Ad 3.
hmm ... The whole snag is that no data from the inputbox dialogs are typed into any cells, but into text fields.
By adding a new row in the table with invoice items, for example: in the " currency PLN, ENG 0% "the only thing that can be 'moved' are the text fields below the table: ' REF ',' REQ ',' AFTER ',' General description 'i' VAT rates '


To avoid 'certain unwanted behavior' of text fields, you can select the appropriate options in their properties:


or

play around controlling the position of these text boxes from within the code:

Code: VBScript

Log in, to see the code

However, when it comes to 'code flexibility' in this particular case, the option to completely rewrite the forms should be considered, so that each entered data has its own cell or range in the worksheet. It is easier then to do anything than to move the text boxes point by point on the screen of the monitor with the macro code, because it is rather a tedious job ...

Ps: This 'software' shifting of text fields is a bit like knitting a form like this in a text file, using white space, tabs, etc.

Hello.
I was gone a while so I gave up the project for a while.
Today I will try to make a detailed description of the problem in the form of a video, otherwise I am not able to convey the essence of the problem.
Briefly:
Cell C5 in the case of my description is just an example, there is no actual reference in my project.
As for the data from the dialog boxes, They are entered into text boxes because this is the only way I could 'avoid' the lack of dynamic cells.
The created text field has a fixed ordinal number, thus giving the command e.g. to enter the invoice number in the text field 4, the input box will always enter the invoice number into the text field 4, no matter where the field is located. As I noticed, the file I attached is practically not working. Tonight I will sit down and record a video description - it will be easier. I care about this project.

mysticmario wrote:

Today I will try to make a detailed description of the problem in the form of a video, otherwise I am not able to convey the essence of the problem.
...
Cell C5 in the case of my description is just an example, there is no actual reference in my project.
...
As I noticed, the file I attached is practically not working. Tonight I will sit down and record a video description - it will be easier. I care about this project.

Please refer to the file in answer no. " # 8 ".
There is a modification that puts the data into the cells of the sheet, and not into the text boxes, which are quite "stiff" when it comes to their "dynamic" movement in the sheet - how does this example work and whether at all (?) ...

I downloaded the file
I must admit that I must sit well with this code.
I'm not that proficient in VBA, I get confused when I read this. Which is not what I understood, and I guess I took it fundamentally wrong. It seems to me that I will not need dynamic cells, although I will try this solution with answer # 8