VBA - Double click and change colour

[screeny]

Hi There,

I'm newby so please forgive my stupid questions.

I got this code from internet (maybe from this site) which works perfect in PERSONAL.XLSB.
My intention is to use GENERAL for all open excel file where PERSONAL.XLSB contain this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 3: Target.Interior.ColorIndex = 4
Case Else: Target.Interior.ColorIndex = 3
End Select
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub

Within Excel object in --> Sheet1(Sheet1)

How can I setup this to be usable as running macro for any open workbook?

Step by step instruction would be highly appreciated!

Thanks in advance!

 

[My Aswer Is This]

You said:

I got this code from internet (maybe from this site) which works perfect in PERSONAL.XLSB.​

Are you saying you have a sheet change event script in your Personal folder?
If so then it should work on any open Workbook.

But I would like to know how you put this in your Personal folder.

I was not aware of the fact this could be put in your Personal folder.

But your saying you have it in your personal folder but it does not work.

I have module scripts in my Personal folder but was not aware you could put a sheet event script in your Personal folder.

 

[screeny]

I'm saying I have this module in my personal xlsb

Sub FormatCells()
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 3: Target.Interior.ColorIndex = 4
Case Else: Target.Interior.ColorIndex = 3
End Select
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub

But it doesn't work.
I'd like to run this macro on any open workbook but unfortunately not succeed.
If any of you has the solution I'd appreciate it.

 

[My Aswer Is This]

I do not believe Before Doubleclick and Right click scripts will work from a Personal folder.

But if they will maybe someone else here at Mr. Excel will show us both how.

 

[screeny]

Is there somebody who'd be willing to solve this to me? Would be highly appreciated! Thank you!

 

[Fluff]

Put this in the ThisWorkbook module of your Personal.xlsb, save it, close Xl. When you re-open xl the before click event should work in all open workbooks.

Option Explicit
Public WithEvents app As Application

Private Sub Workbook_Open()
Set app = Application
End Sub

Private Sub app_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 3: Target.Interior.ColorIndex = 4
Case Else: Target.Interior.ColorIndex = 3
End Select
End Sub

 

[screeny]

Dear Fluff,

I really appreciate your fast delivered solution!

Let me have a fast question.

Is it possible to turn back to not coloured (or white) cell for third double click in order to eliminate previous decision (Yellow or Green)?

Thank you!

 

[Fluff]

Try

Private Sub app_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
   Case xlNone: Target.Interior.ColorIndex = 4
   Case 4: Target.Interior.ColorIndex = 3
   Case Else: Target.Interior.ColorIndex = xlNone
End Select
End Sub

 

[screeny]

Hi Fluff...

thank you and really appreciate!

 

[Fluff]

Glad to help & thanks for the feedback