Creating a Tracker in Excel for Employess on/off jobsite

[BMilleRock]

I currently have an excel workbook and sheet that allows me to use simple functions like (If(ISNA(VLOOKUP)) where I only have to enter an employees' badge # and the rest of their information populates to the right of their badge # (to include the time of the inputted badge #, meaning the time they entered the jobsite that day). The only thing I cannot figure out is

1) how do I attach their photo IDs to their rows in the master sheet of all the employees with all their information and

2) how do I use my simple function, or a function such as LOOKUP() to take an input, such as badge# (123456) and return their photo in a column like the rest of their information??


Thank you!

 

[Perpa]

BMilleRock,
Glad to see you have had some success and help from others while I was away.
Change these 2 lines of code:

Set rng = Range("N2:N" & Cells(Rows.Count, [COLOR=#ff0000]712[/COLOR]).End(xlUp).Row)
....
...
linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=Cells(r.Row, [COLOR=#ff0000]2[/COLOR]).Left, Top:=Cells(r.Row, [COLOR=#ff0000]2[/COLOR]).Top, Width:=-1, Height:=-1)

To these:

Set rng = Range("N2:N" & Cells(Rows.Count, [COLOR=#ff0000]"N"[/COLOR]).End(xlUp).Row) 
....
...
linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=Cells(r.Row, [COLOR=#ff0000]16[/COLOR]).Left, Top:=Cells(r.Row, [COLOR=#ff0000]16[/COLOR]).Top, Width:=-1, Height:=-1)

In the first line of code, the '712' is referring to the 'column' number NOT the 'row' number; Changing the '712' to "N" will set the correct range and find the bottom cell of that column.

In the second line of code, the picture will be placed in column 16 in the same row, NOT column '2'.

You should go out to column 712 (column AAJ) and you will probably find your pictures. You will want to delete all those pictures and run the macro again after making the above changes to the code.
Perpa

 

[BMilleRock]

I changed hte code in those lines as you stated above and the macro is running, but nothing is happening still...

 

[Perpa]

BMilleRock,
What worksheet are you viewing when you run the code? Have the cells in columns 1 to 15 already been populated?

Did you find any photos in column 712 (column AAJ)? If not, then the macro is probably exiting due to the code line 'On Error GoTo errHandler'.

If you step through the code using F8, does the yellow code line step through the next lines sequentially, or does it jump to the line:
'errHandler:' ?

After stepping down to the line just below 'If r.Value <> "" Then ', if you hover the mouse cursor over 'r.value' in the line above, what value does it show? It should be the top filename in column N. If not, then what is your first row of data, row 2 or row 1?
The macro as it exists is looking to begin in row 2:

Set rng = Range("N[COLOR=#ff0000]2[/COLOR]:N" & Cells(Rows.Count, "N").End(xlUp).Row)

The answers to these questions should help us sort this out.
Perpa

 

[Michael M]

Did you use the tips in Post #6 to make sure the path and filename were correct ??
Also, try putting the file AND picture in the same directory.
Have you considered putting the pictures in a Comment....so the picture will become visible when the mouse goes to that cell ??

 

[BMilleRock]

I am using the master database sheet which has columns 1-15 with information inputted without any functions. It's just a normal excel sheet.

The IN/OUT sheet has the populated information based off excel functions.

No photos in column 712.

Using F8 it goes line by line--no jumping to 'erHandler'. The value for 'r.Value' says empty. My first line of data in the excel file is in row 2. row 1 has the column headings.

 

[BMilleRock]

I have been able to attach a photo as a comment, but I wanted the photo to attach and lock to my cell because I am using functions, such as IF(ISNA(VLOOKUP)) on another sheet to pull data from the master sheet to other sheets. So I want to be able to populate the photos on multiple sheets, not just the master sheet.

 

[Perpa]

BMilleRock,
I discovered the problem running the macro on my computer this morning...we have shown the filename in column N with the file extension '.jpg', AND the code is also adding '.jpg', so the code is seeing the filename as 'DAMCIV170244.jpg.jpg'.
Change this line of code (remove the red font portion indicated):

Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=fPath & r.Value[COLOR=#ff0000] & ".jpg"[/COLOR], linktofile:=msoFalse, _
savewithdocument:=msoTrue, Left:=Cells(r.Row, 16).Left, Top:=Cells(r.Row, 16).Top, Width:=-1, Height:=-1)

It should read like this:

Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=fPath & r.Value, linktofile:=msoFalse, _
savewithdocument:=msoTrue, Left:=Cells(r.Row, 16).Left, Top:=Cells(r.Row, 16).Top, Width:=-1, Height:=-1)

And code should work.
Perpa

 

[BMilleRock]

Great, thank you!

 

[Perpa]

Great, thank you!

You are quite welcome.
Perpa