Monitoring tool

feijooos

New Member
Joined
Mar 29, 2013
Messages
2
Hi,

I am trying to build a equipment monitoring tool in Excel, but I am having some trouble. Hoping you can help....

This is the information I have:
The different types of equipment are listed in one row. The status of the equipment is listed below it in a separate column.

I also have an overview (layout) picture which shows where all the equipment is located.

My question: is there a way in excel that I can automatically overlay the status of each piece equipment on the overview picture (so somehow relate the image and table to each other - updating automatically when I change the table)? I would like to automate this since the status changes every week or so and I have 50 pieces of equipment?

Any ideas?

Thanks!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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
 

feijooos

New Member
Joined
Mar 29, 2013
Messages
2
This is great, thanks! Is there also a way to display the status in a text box next to each image (sometimes there's more than the ok/amber/red)?

much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,527
Messages
5,659,335
Members
418,497
Latest member
VAllen79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top