For this to work easiest you need to split up your layout picture in the 50 components, or have one larger image with the 50 components as seprate images on top of it. Not too difficult to do, just some work.
Now you have a few options to highlight the status of a piece of equipment:
you could change the border-colour of the image depending on status
you could swap the image for a similar image with a different colour depending on status
The latter will undoubtedly be the best looking, but that means for each piece of equipment you need as many images as you want statuses displayed.
For instance, Status OK, equipment in green, Status light warning, equipment in orange, Status malfunction, equipment in red
I used to do something like this with images that I used for buttons. the two images of the button were exactly on top of each other. With the button up one image was set to visible, the other to not visible, and with the button down the down button image was set to visible.
So most of the work will be in making the images and setting up the total picture.
Yu can give each image on the sheet a meaningful name, so you can program it easier.
Now for the program: you want to have a macro run when you make a change to the status table. So you need to use the macro module in the sheet object.
If you open the macro editor (Alt-F11), you will see your spreadsheet in the left hand pane. If yu doubleclick on the name of the sheet holding the table, this sheets module is opened.
above this module (the right hand pane) you see two dropdown boxes. In the left (reading 'General') select 'Worksheet'. Suddenly an empty sub will appear. Delete it. Now in the right dropdownbox select 'Change'. A new empty sub will appear:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Here you can enter a macro that will run any time you make a change to this sheet!
But of course you need to check where the change was made, and act accordingly.
The cell changed is 'Target', so you can Target.column to check which column it is (to link it with the equipment piece) and the Target.Value to see what the status is.
So assuming your table has the Equipment numbers in Row A2:AZ2, and the equipment Status in Row A4:AZ4, then we want to check if row 4 in the table has been changed, else no action.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim EquipNr As String
If Not Intersect(Target, Range("A4:AZ4")) Is Nothing And Target.Cells.Count = 1 Then
' the single, changed cell is in A4:AZ4
'get equipment number in row 2
EquipNr = Target.Offset(-2, 0)
ChangeStatus EquipNr, Target.Value
End If
End Sub
This checks to see if a cell containing status has been changed, and if so calls a sub ChangeStatus. We can put this sub in this sheets module, but perhaps it is better to put it in a normal module, because then you could also address it with other macros if required.
To open a normal module (if you haven't got one created) right click on the name of your workbook in the left pane and select Insert... Module
A blank screen appears on the right (to get back to your other code just double click on the sheetname in the left panel)
In this new module paste the following code.
Code:
Option Explicit
Sub ChangeStatus(EquipNr As String, Status As String)
Dim wsImg As Worksheet
Dim shEqStat As Shape
Dim sImgName As String
' assuming that the sheet with the image is called VisualStat
Set wsImg = Sheets("VisualStat")
sImgName = EquipNr & "_" & Status
'first set all imageas for this equipment to not visible
For Each shEqStat In wsImg.Shapes
If Left(shEqStat.Name, Len(EquipNr)) = EquipNr Then
'if the image name starts with the changed equipnr
shEqStat.Visible = False
If shEqStat.Name = sImgName Then
shEqStat.Visible = False
End If
End If
Next shEqStat
End Sub
Now if you try to run this by making changes in your status sheet, it will throw all sorts of errs until you have had your images created and named after the convention I have used in this example. For example
E1223_OK
E1223_Amber
E1223_Red
But if you comment out the shape bits in the code and put msgboxes there at least you can test if the code runs.
Happy programming
