Excel Macro for Outline Coding Table

3D Design Volume Model
1

Hi all
My problem is to create a macro allowing as soon as the file is opened (on a network & for several users), to put the date of today on the desired line as well as the color of the user who has written on a line the codification of the plan.

This allows you to track how much and by whom the plans are created.

Since the computer has a specific name in the network, users who code their plans in the table will be able to have a color code of their own.
Is it possible?


Thanks in advance


classeur_test.xlsx
2

Hello.

 

If I understand correctly what you want to do: the user opens the excel file, indicates the plan number, closes the excel file?

Why not enter the date and username at the same time? I really like macros... but I'm a little afraid that you're complicating your file unnecessarily, right?

3 Likes
3

through 2 forms:

the first to determine the desired action: new codif / query of the file

the second for the new CODIF.

Color can be obtained by conditional formats.

All you have to do is get the user's ID (a priori you can do it).

1 Like
4

@Stefbeno: for the username, it's

 

name = Application.UserName

 

Where "name" is the variable. (yes, I was expecting something very complicated. But in fact not ^^)

 

2 Likes
5

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
6

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

7

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
8

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.
 

9

Hello, I tried the macro. it does not work.

10

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
11

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
12

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
13

I reused your first coin37coin macro and it works in fact but I'm testing your second one to see.

14

Yes I corrected, I had tested for user 2 and I forgot to modify  ! 

2 Likes
15

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
16

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
17

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
18

Thank you .PL, that's why it doesn't work.

19

It doesn't work with the button.

Look at the file, if I'm the one who did something wrong.


classeur_test.xlsm
20

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