Show/hide picture depending of the date

[nwpando]

Hi everyone,

I'm a complet noob with Excel and VBA, i have no idea how it work and i need your help.

I would like to do some kind of advent calendar. I would like to have cells with the different date of the month and when you click a cell a picture is shown on an other cell (depending on which date you choose a different picture is shown)

Like date in the column A,B,C and line 1 to 11 and when you click on the specific date an image appears in cell H10 for example.

Or another idea was to have a cell with the formula "=today()" and depending of the date show an picture would be shown in another cell. And every you open the file, the date change and another picture appears.

I don't know if this is possible or even if this something i can do without any knowledge.

Thanks in advance

 

[kevin9999]

The following demo is based on your first idea – listing all dates from the 1st to the 31st in a range from A1 to C11 (see below). I have added 3 pictures to the sheet representing the 1st, 2nd and 3rd (obviously I’m not going to create all 31 – you can do that). I have called those pictures “PR1C1” (1st), “PR1C2” (2nd) and “PR1C3” (3rd), where P = picture, R = the row that the ‘date’ is located and C = the column that the ‘date’ is located. Using this method, you would call the picture representing the 15th “PR5C3” because that’s the cell location (row and column) where the 15th is located. I hope you get the drift.
What the code does is, when you select a different cell, the code picks up what row/column you’ve selected, turns that into a string, and shows only that picture in cell H10. I’ve added a demo file to show it in action. You need to put the code in the sheet code area for the sheet where you want this to happen. Right click on the sheet Tab name, select View Code & put the code in the blank window that appears on the right of the screen.

Sheet:

Advent.xlsm
	A	B	C
1	1st	2nd	3rd
2	4th	5th	6th
3	7th	8th	9th
4	10th	11th	12th
5	13th	14th	15th
6	16th	17th	18th
7	19th	20th	21st
8	22nd	23rd	24th
9	25th	26th	27th
10	28th	29th	30th
11	31st
Sheet1

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Dim s As Shape, d As String, c As Range
    Set c = Range("H10")                        '<-- set location for display
    d = "PR" & Target.Row & "C" & Target.Column '<-- this creates a picture 'name'
    For Each s In ActiveSheet.Shapes            '<-- loop through all pictures on the sheet
        If s.Name <> d Then
            s.Visible = False
        Else
            With s
                .Visible = True
                .Top = c.Top
                .Left = c.Left
                .Width = 100        '<-- set the size of the picture here
                .Height = 100
            End With
        End If
    Next s
    Application.EnableEvents = True
End Sub

Link to demo file: Advent.xlsm

 

[kevin9999]

New link to file: Advent.xlsm