Double click => returns different message boxes depending on cell color?

Ayra88

New Member
Joined
Nov 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Dear friends,

I'm quite new to VBA and have been confronted with a difficult task at my job - I have been struggling with this for some hours, I hope you could help me. I'll try to make the problem simple:

Say, I have in column A various projects with different colors representing the risk of the projects.

I want to write a VBA Code that will perform the following:

If I double click on a green project => A message box should appear containing the text "Low Risk"
If I double click on a yellow project => A message box should appear containing the text "Medium Risk"
If I double click on an orange project => A message box should appear containing the text "High Risk"

I have formatted the cells with pastel colors, in the column C we can see the color codes of the cells.

1606167751229.png


Please see my code below. The Problem is, it doesn't matter on which cell I click, only the message box "Low Risk" will appear. I don't quite get where is the mistake.

I would very much appreciate your help!

(In reality the problem would be: double click on green cell => jump to another sheet ( I already have this part written); double click on yellow cell => message box "...."; double click on orange cell =>message box "...")

Best Regards, A.

---

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As rangE, Cancel As Boolean)
Dim Cell As range
For Each Cell In range("A1:A15")
With Cell
If Not Intersect(Target, range("A1:A15")) Is Nothing Then
If Cell.Interior.Color = 11854022 Then
Call showGreen
Exit For
ElseIf Cell.Interior.Color = 10092543 Then
Call showyellow
Exit For
ElseIf Cell.Interior.Color = 11389944 Then
Call showRed
Exit For
End If
End If
End With

Next Cell
End Sub
---
Sub showGreen()
Dim showCheck
showCheck = MsgBox("Low Risk")
End Sub
---
Sub showyellow()
Dim showCheck
showCheck = MsgBox("Medium Risk")
End Sub
---
Sub showRed()
Dim showCheck
showCheck = MsgBox("High Risk")
End Sub
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("A1:A15")) Is Nothing Then
      If Target.Interior.Color = 11854022 Then
         Call showGreen
      ElseIf Target.Interior.Color = 10092543 Then
         Call showyellow
      ElseIf Target.Interior.Color = 11389944 Then
         Call showRed
      End If
      Cancel = True
   End If
End Sub
 
Solution

Ayra88

New Member
Joined
Nov 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Dear Fluff,

wow it works perfect! Thank you very very much! May I ask what does the line "Cancel = True" mean?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
It prevents the cell from going into edit mode.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,265
Messages
5,571,219
Members
412,371
Latest member
KUstudent
Top