Hello
Careful Excel only allows two users to edit a single file at the same time!
If Utilisateur_1 opens the file by accessing it for write, Utilisateur_2 cannot access the file for writing.
3 Likes
Thank you for your help, I'll look into this and keep you posted.
If you have any examples, I'm a taker.
Thank you for anvance
Agree with @Remrem
And if not, because I had a little time:
Sub AutoName()
Name = Application.UserName ' user name
Derline = Sheet1.Cells(1, "B"). End(xlDown). Row' Will fetch the last non-empty row of the column cell "Date" sheet1 from cell "B3"
Sheet1.Cells(Derligne, "B") = Date 'Entered the date of the day's
coul = Cells.Find(Name, Cells(1, "E")). Interior.ColorIndex 'Retrieves the color of the box with the user name
range(cells(derligne, "a"), cells(derligne, "c")). Interior.ColorIndex = color
End Sub
On the other hand, be careful. The username must be RIGOROUSLY the same between the excel file and the PC, otherwise it cannot find (the same spaces, the same capitalization, etc etc).
I continue to believe that it's a source of boredom for your thing!
3 Likes
Thank you coin37coin,
No, don't tkt, it's for simplified a word file to fill in by hand. There are some who manage to make mistakes sometimes. when do we go too fast...
Thank you I test all this.
Hello, I tried the macro. it does not work.
It's surprising, it worked very well yesterday.
At what level does it block?
Edit: As much for me, she wrote instead of the last line (little mismatch)
Sub AutoName()
Name = Application.UserName ' user name
Derline = Sheet1.Cells(1, "B"). End(xlDown). Row' Will fetch the last non-empty row of the column cell "Date" sheet1 from cell "B3"
Line = Derline + 1 'Add a line to go on a blank
Sheet1.Cells(Line, "B") = Date
coul = Cells.Find(Name, Cells(1, "E")). Interior.ColorIndex 'Retrieves the color of the box with the user name
range(cells(line, "a"), cells(line, "c")). Interior.ColorIndex = color
End Sub
2 Likes
Hello
Here is the corrected macro but it will depend on the order of the user's actions:
He will first write in his reference at the end of the list ?
Sub AutoName()
Name = Application.UserName ' user name
Derligne = Sheets("Sheet1"). Cells(1, "A"). End(xlDown). Row' Will fetch the last non-empty row of the column cell "Date" sheet1 from cell "B3"
Sheets("Sheet1"). Cells(Derligne, 2). Value = Date 'Entered the date of the day
Set cell = Cells.Find(what:=Name, LookAt:=xlWhole) 'Retrieves the color of the box with the user name
coul = cell. Interior.ColorIndex
range(cells(derligne, "a"), cells(derligne, "c")). Interior.ColorIndex = color
End Sub
2 Likes
Two different versions indeed.
With @PL's, you first enter the reference and then he puts the date and color in auto.
With mine, he puts the date and color in auto and then you put the reference.
@PL: FYI, you only give your macro the choice to take "user 2" for the color;) (or something escapes me)
1 Like
I reused your first coin37coin macro and it works in fact but I'm testing your second one to see.
Yes I corrected, I had tested for user 2 and I forgot to modify !
2 Likes
Thanks for all the macro,
I can't put the macro with the button. the macro is no longer at the opening. I'm going crazy.
Here is the new table
classeur_test.xlsx
For the button, you have to go to the "developer" tab => insert => Button
Then, you insert the button into your sheet as if it were a note (a left and right click to left to set the size) then a right click on the = button> assign a macro => select your macro from the list
On the other hand, of course, the macro must be in your visual basic before
To display the developer tab (if you don't have it), you have to go to file => options => Customize the ribbon => click the "developer" box in "main tabs" if you don't do so
I don't tell you in the file, because I think it's better to struggle a little and learn than to have a turnkey solution;)
Edit: you'll be careful, you leave a lot of information in your files between this question and the previous one. There is the name of your colleagues or the complete list of parts of your company. I advise you to edit your messages and to remove/modify them.
bouton_vba.png
In fact, xlsX files do not accept macros: they are deleted when they are closed!
You either have to register under xlsM. or save in .XLS (old version 97).
2 Likes
Thank you .PL, that's why it doesn't work.
It doesn't work with the button.
Look at the file, if I'm the one who did something wrong.
classeur_test.xlsm
For me, it works well
Two remarks, however:
-> You write on the last line (ref 20244). And not on the last + 1 line Small line of modification to make as for my 2nd macro
-> You have reached the limits of the VBA: if it doesn't work, it's because you haven't written the user's name EXACTLY as it is entered in the machine. That's what I said at the very beginning of the question: it's going to be a perpetual source of error as soon as a new user bursts in.
I will therefore advise you to do a small macro to do with each user based on:
Sub DefName ()
Name = Application.UserName
ActiveCell = Name
End Sub
You just have to select the box where to put the name, launch the macro and presto, it's filled in correctly. To do for each of the people present
1 Like
That's a good idea. I'm going to try
Dsl I can't make macros work at all. We're going to give up I think.
In any case, thank you very much
I put macros on a button
Either the macro of .PL or mine.
To see the difference between the two, I advise you to click first on mine THEN on .PL's (otherwise the effect is countered by Lucas' macro)
For the set user button, don't forget to click the corresponding cell before
Have fun:)
classeur_test.xlsm
Thank you very much. It's perfect^^