I to all.
This macro
works on 1 sheet only.Please Login or Register to view this content.
You can change it to work on all sheets of the workbook.
data_base_1
data_base_2
data_base_3
Thanks you
john
I to all.
This macro
works on 1 sheet only.Please Login or Register to view this content.
You can change it to work on all sheets of the workbook.
data_base_1
data_base_2
data_base_3
Thanks you
john
Hi there,
Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:
Nota: il sistema qui non mi ha permesso usare la lettera con accentoPlease Login or Register to view this content.
Hope this helps - please let me know how you get on.
Regards,
Greg M
Hi greg
the macro must work for all 3 sheets if the value is repeated.
Example:
if repeated in the data_base_1 sheet and insert the same value in the data_base_3 sheet, a warning must appear:
MsgBox Prompt:="Attenzione: codice " & _
"gia presente nella riga " & i + 1, data_base_1
Buttons:=vbCritical, _
Title:="Doppione!" ' <<< 2 = parte dalla riga 1
Hi again,
Thanks for your feedback.
See if the following code does what you need:
The highlighted values may be altered to suit your requirements.Please Login or Register to view this content.
Hope this helps - as before, please let me know how you get on.
Regards,
Greg M
I have tried several times to insert this value 123456 as an example in all 3 sheets but no warning appears
Hi again,
I think that the situation is possibly not helped by the fact that the "numeric" values in Column A are in fact formatted as text values. I'm not sure, but this might have caused duplicate values to be ignored. I have changed the formatting to "General" and the code seems to function correctly for me.
In the attached version of your workbook, the value 123456 is entered on Row 3 of worksheet "data_base_1", on Rows 4 & 5 of worksheet "data_base_2", and on Rows 6, 7 & 8 of worksheet "data_base_3". Row 2 of worksheet "data_base_1" is blank.
When I enter a value of 123456 in Cell A2 of worksheet "data_base_1" I receive six separate warning messages - one for worksheet "data_base_1", two for worksheet "data_base_2", and three for worksheet "data_base_3".
Hope this helps - as before, please let me know how you get on.
Regards,
Greg M
Hi greg
the cell format of the cells must remain text.
I have tried several times both text and general but not no warning appears
What can I say?
In the attached workbook I have changed the formatting of the cells in Column A on each of the three worksheets to "Text"
When I enter 123456 into the empty Cell A2 of worksheet "data_base_1" I get six separate messages telling me that:
the value 123456 exists already on Row 3 of worksheet "data_base_1"
the value 123456 exists already on Row 4 of worksheet "data_base_2"
the value 123456 exists already on Row 5 of worksheet "data_base_2"
the value 123456 exists already on Row 6 of worksheet "data_base_3"
the value 123456 exists already on Row 7 of worksheet "data_base_3"
the value 123456 exists already on Row 8 of worksheet "data_base_3"
I will assume that you have Events enabled for the workbook
It works on my system - I don't know why it doesn't work on yours
Regards,
Greg M
Hi greg, I don't know what to say
If I insert here at home with excel2007 but also in the office with excel365 in A2 sheet data_base_1 value 123456 no warning appears
Hi again,
VERY strange!
Can you set a break point at the start of the "CheckData" routine and step through it to see what happens when you insert 123456 in Cell A2?
Regards,
Greg M
Hi greg,
I don't know how to do with the procedure you ask me to do
Hi again,
Open the VBA Editor and place the cursor somewhere in the line:
Now press the F9 key - the line will be highlighted and a coloured (brown?) spot will appear in the left margin.Please Login or Register to view this content.
Return to the main Excel window and enter 123456 in Cell A2 of the "data_base_1" worksheet
The display will "jump" to the above line of code and will pause ("break") waiting for your input.
Pressing the F8 key will cause the code execution to jump to the next executable statement.
By using the F8 key to step through the code it should be possible to see what the code is/is not doing.
If you want to finish stepping through the code and allow it to continue uninterrupted until the end, just press the F5 key.
Regards,
Greg M
Hi greg.
Breakpoint inserted as per attached photo
inserted in sheet data_base_3 value 123456
returned to vba and pressed F9
stops in the brown (third) row with the yellow arrow and does not continue
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks