+ Reply to Thread
Results 1 to 27 of 27

Display action list or comments based on cell value

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    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.

  2. #2
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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.

  3. #3
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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

  5. #5
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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?

  7. #7
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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

  8. #8
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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?

  9. #9
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    If you're clicking 'Yes' then you're saving the file as a Macro-Free workbook, so it won't save the code. You need to click 'No' and save as a Macro-Enabled workbook with the .xlsm‎ file extension from the 'Save as type' drop down list. With the old .xls file type this didn't matter but since Excel 2007 with the .xlsx format you have to save Macro encoded workbooks differently.

    Sceenshot.jpg
    Last edited by DBY; 09-06-2016 at 12:21 PM.

  10. #10
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    ok i figured out how to fix that and i got rid of it. one final question. if i want to edit the code you shared to make the image appear on one worksheet but reads the value from the same sheet and a different sheet. how do i make this happen? please ignore the be careful message below.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B2:C3")) Is Nothing Then

    If WorksheetFunction.CountIf(Range("D2:D3"), "<0") Then
    Me.Shapes("Picture 7").Visible = msoTrue
    Else
    Me.Shapes("Picture 7").Visible = msoFalse
    End If
    End If

    End Sub
    Attached Images Attached Images
    Last edited by smsmworld; 09-06-2016 at 12:38 PM.

  11. #11
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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.

  12. #12
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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.

  14. #14
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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.

  16. #16
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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.

  17. #17
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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?

  18. #18
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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

  19. #19
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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

  20. #20
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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

  21. #21
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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

  22. #22
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Display action list or comments based on cell value

    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.

  23. #23
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    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

  24. #24
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

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

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

  25. #25
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Display action list or comments based on cell value

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

    Picture 7 or
    Picture 7

  26. #26
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Display action list or comments based on cell value

    You have to assign every picture to Go_To VBA code.

  27. #27
    Registered User
    Join Date
    09-05-2016
    Location
    cairo
    MS-Off Ver
    Office 2016
    Posts
    39

    Re: Display action list or comments based on cell value

    Can you please tell me how?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Display comments when highlight the number based on 3 criterias
    By suchetherrah in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-24-2015, 07:35 AM
  2. How to display cell comments based on 2 conditions
    By vishwas1980 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2012, 02:55 AM
  3. Action List Based on Yes/No answers.
    By emmarky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-27-2010, 05:53 PM
  4. Perform action based on choice from validation list
    By kjetiltb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-05-2009, 04:57 AM
  5. Display a list of data based on value in a cell
    By friend11_6 in forum Excel General
    Replies: 1
    Last Post: 09-08-2009, 12:27 AM
  6. [SOLVED] Can I display comments in a validation list
    By Stoke in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2006, 02:20 PM
  7. [SOLVED] display a drop-down list based on the content of another cell
    By Joe S in forum Excel General
    Replies: 6
    Last Post: 12-05-2005, 08:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1