Display action list or comments based on cell value

Hello,
allow me to start with an example from the XLS i'm working on.

Capture.JPG

Cell D has a formula to display the percentage of growth or decrease between Cell B and C. and it also has a conditional formatting icon sets.

what i'm looking for is:
1- if cell D value is negative like Product2 it should display a visual element on Cell E educating the user to click on that element. for example a yellow warning triangle.

2- when the user clicks on that visual element, it should display or lead to a list of previously written text in a cool and neat way that will educate the user on how to increase sales for example.

To be honest i don't know what is the best solution for this. so please if you could offer some options to do this, it would be very helpful.

Thank you.

let me explain more. say for example that numbers in Cell B1,2 represent Jul and Cell C1,2 represent Aug.
what i'm hoping to do is when the numbers decrease MOM. a button or a warning signal should appear automatically in Cell E2 for example. and when the user clicks on it. a pop-up should open displaying a list of text that was previously written somewhere else on the workbook. advising the user how to act in this case and increase sales.
Thanks.

Hello
Take a look at the attached file. If you change the values in B2:C3, if any of the percentage difference formula are negative an icon appears which when clicked hyperlinks to the 'Information' tab. This is protected ( no password) to prevent users overwriting information. There's another hyperlink (A1) to take you back to Sheet 1.

Perhaps you could work with something like this.
DBY

Thank you! that's exactly what i was looking for. and thank for the tips hahaha.
2 questions here:
1- how do i make the icon appear on the case of a negative number?
2- can i hide the information tab and make it only appear on the workbook when the user clicks on the icon? because there are several other metrics and each one will have it's own information tips tab.

Thank you again

Hello
I've attached an amended file to hopefully answer your questions.

1- how do i make the icon appear on the case of a negative number?

The icon does appear when there are any negative values in cells D2:D3. I may have had the calculation the wrong way round if that's what you're referring to, I've changed that in the amended file.

2- can i hide the information tab and make it only appear on the workbook when the user clicks on the icon?

In the amended file I've replaced the Hyperlink on the Info button with a macro that shows the 'Information' sheet. When that sheet is deactivated it hides again. Is this what you're looking to do>

DBY

yes sir. that's absolutely perfect. i can not thank you enough for this.

all what is left now is for me to understand how this is done so i can repeat on a larger file with different metrics and tabs.

1- how do i set the rule for the icon to appear in case of a decrease? so i can repeat it with other metrics.
2- i'm not experienced in macros. can you please show how was it done?

Hello
Glad you found this useful. You can examine the code in the VBA editor (Alt+F11). Here you will see the code used by double clicking the Sheets and module in the left panel (Project Explorer). Obviously with different metrics, sheets and cells these variables need to be adjusted for each. Much depends on your layout in the actual workbook. Without this it's hard to explain the changes needed. But perhaps you can work this out from the existing code.

The main piece of code is this:

Please Login or Register  to view this content.

The variables being the Ranges and the Shape name. This fires whenever any value in the Target range (B2:C3) is changed. It checks to see if any values in D2:D3 are less than 0. If so the Information Icon is made visible, hidden if not.

Hope this makes some sense, if not maybe you can upload a sample workbook with any sensitive information removed.

DBY

Good sir i was able to apply the code perfectly on the XLS and it works. however, when i click save on the workbook i get the attached message
macro.JPG

when i click yes i get another message
macro 1.JPG

after that when i close the file and reopen it the code is gone. do you have any idea why would this happen?

You could change this line:

Please Login or Register  to view this content.

Here if any of the values in these two ranges on different sheets are negative then the macro fires. But only if the value change is made in B2:C3 on the first sheet.

Hi
Your example file on One Drive doesn't give me much to go on but I've attached another amended file. This takes the current month's figures input in the 'Enter Data' sheet and places it into the 'Main' sheet in cells C2:C3. There's a message box pop-up when either of those cells is empty after clicking the command button.

...team members will update current month data from "Enter Data" sheet and one admin person will input last month data on the "Main" sheet. then "Main" sheet will do few formulas to compare month on month

This is basically working with only two months' data so I'm not sure how you are going to proceed if you're planning to store any of the data throughout the year.

DBY

Hi
is there anyway to remove the command button step and at the same time continue placing data in "Enter Data" sheet while it reflects automatically on "Main" sheet.

or is it necessary so that the image icon appears?

Thanks.

I've changed the code from the command button and placed it in the worksheet change event for cells C4:C5 on the Enter Data sheet. Now any changes made to these two cells will update the linked cells on the Main sheet.

DBY

ok. i think now we've reached what is aimed for and it looks amazing. all i need to know now how do i make the cells in "Main" sheet read from "Enter Data" sheet and make the icon image appear in case of a decrease?

Thanks.

all i need to know now how do i make the cells in "Main" sheet read from "Enter Data" sheet and make the icon image appear in case of a decrease?

I'm a little confused by this. This is what the code does. It takes the Enter Data values and puts them into the Main sheet and shows the icon if there's a decrease.

yeah, sorry maybe my wording was wrong. what i meant is what is the code so i can apply it and how to do it?

I can only refer you again to post #7 of this thread. That's how to examine the code and apply it to your actual workbook within the various modules. I can't be more specific without seeing the actual layout. You will need to adjust the variables as needed to the sheets, cells and clip art you are using. The code in Module 1 with the sub 'AddtoMain' can be removed if you're not using the command button option I originally used on the 'Enter Data' sheet.

DBY

Hey,
i managed to apply the different codes successfully with a single image thanks to you. i'm facing a new challenge right now when i try to have multiple icon images on the same worksheet. and each one is assigned to different cells. somehow i get an error that i'm unable to solve. here is the code and error i get.

error.JPG

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K8")) Is Nothing Then

If WorksheetFunction.CountIf(Range("M8"), "<0") Then
Me.Shapes("Picture 1").Visible = msoTrue
Else
Me.Shapes("Picture 1").Visible = msoFalse
End If
End If

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K14")) Is Nothing Then

If WorksheetFunction.CountIf(Range("M14"), "<0") Then
Me.Shapes("Picture 2").Visible = msoTrue
Else
Me.Shapes("Picture 2").Visible = msoFalse
End If
End If

End Sub

You cannot put more than one Worksheet Change Event Sub into the Worksheet Module as the error message states the name of the sub is ambiguous. You can however, put a number of arguments in the Change Event, for example try something like:

Please Login or Register  to view this content.

If you're just dealing with those two cells this is probably ok. If you have many more perhaps a different approach might be needed as repeating this code could get tiresome.

DBY

ok, so i managed to get a work around like that. and i'm repeating it with a few cells and different images on the main "Sales" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K8")) Is Nothing Then
macro1
End If
If Not Intersect(Target, Range("K14")) Is Nothing Then
macro2
End If
If Not Intersect(Target, Range("P13")) Is Nothing Then
macro3
End If
If Not Intersect(Target, Range("D8")) Is Nothing Then
macro4
End If
If Not Intersect(Target, Range("D25:D26")) Is Nothing Then
macro5
End If
If Not Intersect(Target, Range("K20")) Is Nothing Then
macro6
End If
End Sub

Sub macro1()
If WorksheetFunction.CountIf(Range("M8"), "<0") Then
ActiveSheet.Shapes("Picture 1").Visible = msoTrue
Else
ActiveSheet.Shapes("Picture 1").Visible = msoFalse
End If
End Sub

Sub macro2()
If WorksheetFunction.CountIf(Range("M14"), "<0") Then
ActiveSheet.Shapes("Picture 2").Visible = msoTrue
Else
ActiveSheet.Shapes("Picture 2").Visible = msoFalse
End If
End Sub

Sub macro3()
If WorksheetFunction.CountIf(Range("R13"), "<0") Then
ActiveSheet.Shapes("Picture 3").Visible = msoTrue
Else
ActiveSheet.Shapes("Picture 3").Visible = msoFalse
End If
End Sub
Sub macro4()
If WorksheetFunction.CountIf(Range("H8"), "<0") Then
ActiveSheet.Shapes("Picture 7").Visible = msoTrue
Else
ActiveSheet.Shapes("Picture 7").Visible = msoFalse
End If
End Sub
Sub macro5()
If WorksheetFunction.CountIf(Range("H25:H26"), "<0") Then
ActiveSheet.Shapes("Picture 8").Visible = msoTrue
Else
ActiveSheet.Shapes("Picture 8").Visible = msoFalse
End If
End Sub
Sub macro6()
If WorksheetFunction.CountIf(Range("M20"), "<0") Then
ActiveSheet.Shapes("Picture 9").Visible = msoTrue
Else
ActiveSheet.Shapes("Picture 9").Visible = msoFalse
End If
End Sub

for the other worksheet that is used to input the data i'm using a slimier code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I8")) Is Nothing Then
macro1
End If

End Sub

Sub macro1()
Application.ScreenUpdating = False
With Sheets("EG Summary")
.Unprotect
.Cells(8, 4) = Cells(8, 9)
.Protect
End With

End Sub

the problem is when i try to test that code i run into an error that i'm unable to understand.

error3.JPG
error4.JPG

That means there isn't a 'Picture 7' it can find on the Active Sheet. Check it exists and its name is exactly as you have it in the code including any spaces.

i checked the name of the image and its the same. and also i don't face that error when i update the numbers from the main sheet directly.

name.JPG

here is a sample file i created so you can see the error

https://1drv.ms/x/s!AswfOUqjj_wrghZeFPBbfEZI3GnS

In VBA code, it appears to be two blanks between Picture and 7. Check.

Picture 7 or
Picture 7

You have to assign every picture to Go_To VBA code.

Can you please tell me how?