Reference an image in a changing worksheet

[graybeardtx]

I have limited skills but looking for assistance solving a problem without outside programming like VBA, macros etc.

I have a spreadsheet where the entries are calculated from a root seed. I want to be able to reference an image from another sheet based upon the value. I have found several sources on the web but there is one essential missing link. The starting cell for the reference varies from sheet to sheet. Example: I have a monthly calendar (which I can produce from other sources on the web). The "seed" calculation determines the first day of the week for the viewable calendar, so the actual first day of the month can be in any of the top 7 cells. I want to be able to use that cell reference to begin populating subsequent days in the month.

Since I am new, I can't put images in the post so I'll use some text magic:

28 29 30 1 2 3 4 (First week of the month)
P1 P2 P3 P4 and so on

5 6 7 8 9 10 11
P5 P6 P7 P8 P9 P10 P11

The next month will probably start on another day of the week but Day1 and Image 1 need to be paired. I'm sure it can be done by programming in some language but I'm just curious enough to want to do this within the workbook but not motivated enough (or even capable enough) to figure it out.
Thanks for any assistance.

[Keebellah]

Hi, welcome to the forum.
I suggest you correct the Office version in your profile; I don't 2109 is here yet, shouldn't it be 2019
Even if you're new, you can attach a sample file, make sure it doesn't contain private or sensitive data.

[graybeardtx]

I AM FROM THE FUTURE AND I HAVE COME TO.....
Actually, that is the version that is shown in the account settings. Don't know what to tell you. It is Office 365 Complete packsage.
I'll upload a sample as soon as I remove the propritary references.

[graybeardtx]

Here is a sample file. The initial page is for June 2021 and the 4 day images are correctly placed. However changing the month to July (Via drop-down in the Month field) and Day 1 remains in the same block but Day1 1 of the calendar shifts to the fifth column.
)

[Keebellah]

Hi FUTURE GUY
Will take a look and get back to you

[Keebellah]

I don't know if others can do it with formulas but I can with macros, is that an option for you?
For your profile: Ms-Off Ver: = 365 (2109 is the MS internal version number) and the build is extra information

[graybeardtx]

Updated the version to 365 - just shows you how much of a rookie I am.
I was sure it could be done with a macro. Macros looks like a foreign language and I don't really want to learn another language at my age (assuming I could still learn). I am not too proud to take any assistance provided. I can generally hack existing code - just unable to create.

[Keebellah]

Never too old for new tricks, I'm still learning and almost 73 . . .
Macros are just a set of sequential instructions that carry out exactly what its told to do with If and Else and otherwise, you can go on for ages.
You will have to structure the file and meet some basic rules but it can be done.
You've got coloured squares for each Day, will this always be such or other images?
Will you be updating the Day1 for the next month or are these static images?

[graybeardtx]

The images are static. Cell placement is the variable. For example the Day1 image will always be on the first day of the month.
A little clarification if it is not obvious - The day of the mmonth and the image are in separate rows in the same column. i.e. Day 1 is in C3 and the image is in C4.

[Keebellah]

That's all clear, my question was when a new month is displayed the Day 1 image in the list of days is still the same as the Day 1 image of the previous month or will you change them as the month changes? THAT is/was my question

[graybeardtx]

Let me see if I understand your question and answer accordingly. The Day1 image will always be the same image (initially but let's solve this one first). Here are some images to demonstrate 2 different months:
Attachment 753146Attachment 753147

[Keebellah]

Attachments non useable, link is invalid, attach image as you would a file

[graybeardtx]

The "Attach a File" icon brings up the screen to choose the file and the "file reference is what is attached. I haven't found any other way to attach a file. I'll keep looking but until then the text equivalent will have to do. SOrry about all the ,, and __ - it was necessary to maintaining the integrity of the image.

Month 1: S __ M___ T___ W___ Th___ F___ S
,,,,,,,,,,,,,,,,,,,,,,,,, I1 ___I2___ I3___ I4


Month 2: S __ M___ T___ W___ Th___ F___ S
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,I1___ I2___ I3
,,,,,,,,,,,,,I4

[Keebellah]

If you read the attached PDF you'll see how you can attach different file types, it's also in the forum's explanations

[graybeardtx]

Simple as all Microsoft products

[Keebellah]

Okay, got it

[graybeardtx]

I have finally made some progress. By using the Macro Record function I was able to decipher a "stanza" that performs the function of placing the image on the calendar page. I have cut and pasted several copies of the "stanzas" and manually updated the sheet position and the image to place there. I am now trying to figure out how to make the code so that it loops and places the images. The goal now is to figure out how to do that in VB. It would be simpler if the first of each month always began on a Sunday . So we'll see how long that takes making the start date variable.

[Keebellah]

I would say, great job, but have you noticed what happens if you run the same macro again?
You still haven't answered my question bout if the pictures are going to be the same for each month or if it's just Day 1 Day 2 etc.
And what happens when a new month starts?

[graybeardtx]

I must say that I haven't' I presume it would fail horribly. I always clear the data and restart the macro. I am thinking about making sure the macro gets run just once.
The pictures are keyed to the days of the month, the beginning date is the fly in the ointment. Each month rarely starts on the same day
so the first "picture", which is always paired for Day 1 through Day 30..31, etc. is placed in the cell where the Day# is calculated.

[Keebellah]

I understand the calendar and that DAY 1 is not always a Monday or a Sunday etc.
What I mean is the list of images for each day (you now have 4 with the caption Day 1, Day 2
Will this always be the same picture but only placing Day 1 in the block for Day 1, the Day 2 until Day 30 or 31 or 28 or 29 in case of February?

[graybeardtx]

You are correct. There are 31 unique pictures representing each day. The pictures are static and the image will not change , just the location of the image based upon t he day.

[Keebellah]

Clear, I can work with that

[Keebellah]

This seems to work, there is however one thing.
All the day images in the PayMnt worksheet must be named correctly, not the caption, the actual image name.
Syntax must be Day space day number so Day 1, Day 2, Day 3, Day 10 etc.
The Month selection is still the same dropdown list you had except that I added two columns to the named range and named the new named range allmonths
Column A the month name, Column B the month number and Column C the calculated date for the calendar's A2 cell value

There is also a named range allyears with starts with the actual date's year - 3 and ends with actual date's year + 3 so you have a calendar choice now from 2018 through 24

If you select another month or another year the calendar refreshes the images

[graybeardtx]

Thanks for the sample solution. It appears to accomplish the function I envisioned. I will now study the programming to learn more about the techniques.
Programming logic is hard for me to accomplish. I'll try to update the sample to use the real data.

[Keebellah]

I excuse myself for not placing comments in the VBA code.
My logic and programming techniques are my own, what I mean to say is that there are probably many ways to do this and who knows which technique is the best.
One thing, the size of the 'day' images you place in Paymnt does not really matter, the code fits it to the target cell in the calendar.

[graybeardtx]

No excuse necessary. I learn better by deciphering code.
The code that you made to fit the image in the target cell is wonderful. Thanks again.

[Keebellah]

You're welcome.
Like I said, it's not all my code but it's put together with what I've found and done earlier with similar challenges
Patience and time is what you need most to learn VBA

[graybeardtx]

I have another question regarding ths program. Some of the images are overlapping the borders of adjacent cells. The resulting image has some of the cell borders overlapped with the image so there is an inconsistency in the way the objects are modified. I'm trying to decipher the code of the macro and having difficulty understanding the code snippets. At one time I thought the images were massaged to fit in the assigned space but I can't find that in the code. The variables appear to be simply assigned with out specific modifications. WHat am I missing?

[Keebellah]

Hi, I don't really know, like I mentioned in my post #27 it's not all my code.
Will have to look into it.
Don't know if you modified anything in the file I attached last year november.
As far as I remember the image size is set to the cell size. you will have to look in that section of the vba code.
I have included that section of code maybe changing the LockAspectRatio to msoTrue ?
VBA is patience and perseverance

Please Login or Register  to view this content.