Excel userform - vba code to modify datasheet

[mariec_06]

In an userform I have code to add new datas on the next available row which works perfectly.

I have also a code "Getdata" to populate the userform with the datas already in. I am working on a code in order to modify/add new info in data already existing ("save_data"). However it appears that the code "save_data" can not overwrite the "GetData".....

for example, under REF 003, there are some infos already in which appears in the userform but some infos are "empty" and when I try to fill in them the userform does not keep them and revert to the original info = empty.....

Here below both code

Please Login or Register  to view this content.

how could I modify or the Getdata or the save_data code in order to be able to see the info already from the datasheet in the userform but also be able to modify them (delete/change/add)....

thank you in advance

[Greg M]

Hi there,

Can you post a sample of your workbook and, in particular, the UserForm so that we can get a better idea of the layouts?

Regards,

Greg M

[mariec_06]

Here attached......

for example if worksheet("recap").range("aj") is empty I can not update it via the userform....

[Greg M]

Hi again,

This is a fairly complicated UserForm!

Can you post a copy of (or an extract from) the workbook "CALC(P) - - - + CALC(S) - - -1" which your code uses, so that I can get a better idea of what's supposed to be happening?

Regards,

Greg M

[mariec_06]

forget this part is not anymore applicable

just the getdata and save codes are a problem

apologise for the confusion

[mariec_06]

here an amended file

[Greg M]

Hi there,

I cannot even display the UserForm

I receive a "Could not set the Value property. Type mismatch" error when I attempt to display it. This appears to come from the "#VALUE!" value contained in Cell Y2 of the "PRICING FINAL" worksheet - this in turn comes from the "#REF!" value contained in Cell V2 of that worksheet.

Cell V2 contains the following formula:

Please Login or Register  to view this content.

which suggests that it is referencing cells which have been deleted at some stage.

Sorry, but I can't attempt to solve your problem until I can at least get the UserForm to display correctly.

Another point: your workbook appears to be linked to a "GENERAL LIST (VSL + CIE) 2016.xlsx" workbook - I don't know if access to this workbook is required in order to diagnose the problem fully.

Regards,

Greg M

[mariec_06]

again I apologise for all those bugs

the original table works perfectly (links, formulas etc)

the userform (that hopefully you will be able to view now) works perfectly to add a new ref and to view all the datas ('geetdatas') related to a ref already in the worksheet.

My only problem is that the userform can not overwrite the infos (with the 'save_datas' code/save button). it appears that the userform can not change the infos displayed by 'getdata' i.e if a cell is empty I can not fill it in nor change an already existing datas.

example: column AJ, Au for ref 2016CT003 are empty as per getdata, and I can not update them afterwards with the save button

Hope now you will be able to diagnostic the issue and maybe to correct the codes

[Greg M]

Hi again,

I've taken a quick look at your latest workbook, and I'm afraid that there are some items you're going to have to deal with


In the "SAVECMD_Click" routine:

Please Login or Register  to view this content.

The first thing to do is to delete the highlighted statement, which is retrieving original data and overwriting the modified UserForm values BEFORE the modified data have been written to the worksheet.


When Reference No 2016CT003 is selected in the REF: ComboBox, this statement is somehow triggering a call to the following routine:

Please Login or Register  to view this content.

which again retrieves original worksheet data and overwrites the modified values in the UserForm.

I don't have time to investigate why the above routine is being called, but I suspect that the reason is complex rather than simple

There may well be other reasons why worksheet data is not being correctly updated, but I haven't been able to check the application completely.

Sorry I don't have better news, but the above may be of some use to you in resolving the problem.

Regards,

Greg M