• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

double clicking cell changes font color

M

mdavid

Member
Hi I need help,
I have a cell that has a font color red, when I dbl click on the cell the font color changes to black - I don't understand why the font color changes when I dbl click. I've tried running debug starting at Worksheet_BeforeDoubleClick but the color changes before the debug starts as soon as I double click the cell, can anyone give me an idea why this is happening.
Thanks for any help
David
 
Thanks for your reply, here's the code:
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lRow As Long
Dim cols As Range
Dim UForm As Object
Dim PrepAdminOPen As Boolean
lRow = Cells(Rows.Count, 2).End(xlUp).Row

  If Not Application.Intersect(Target, Range("J2:J" & lRow)) Is Nothing Then
     Cancel = True
     UserForm5.Show
     Exit Sub
  End If
The font color changes before UserForm5 opens, but here's the UserForm5 UserForm_Initialize:
Code: 
Private Sub UserForm_Initialize()
'ActiveSheet.Unprotect
KeepRed 32
ListBox1.ListIndex = -1
Me.StartUpPosition = 0
Me.Top = 40
'Me.Left = 615
  Me.Left = 0.95 * (Application.Left + Application.Width - Me.Width)
End Sub
and here's the keepRed routine:
Code: 
Sub KeepRed(defaultcolor)
'  Keeps the cell font color if it's red else changes font color to defaultcolor
Dim rng As Range, cell As Range
ActiveSheet.Unprotect
Set rng = Intersect(Range("B" & ActiveCell.Row & ":P" & ActiveCell.Row), ActiveSheet.UsedRange)
For Each cell In rng
       If Not (cell.Font.ColorIndex = 3) Then
           cell.Font.ColorIndex = defaultcolor
       End If
   Next cell
'ActiveSheet.Protect
End Sub

As I say the color changes before the debug starts in Worksheet_BeforeDoubleClick, so I don't think the problem's in the above code.

Thanks for any help
David
 
I would like to know the sequence of events between double clicking in a cell and Worksheet_BeforeDoubleClick routine.
I have a cell who's Font.Colorindex = 3 (red) when I double click in the cell the font color changes to black - this change takes place before Worksheet_BeforeDoubleClick is entered 'cause I've placed the debug break-point there and the font color changes before debug starts, so my question is where should I look for the code causing the font color change - 'cause I don't know where it's happening I cant show any problem code.
Thanks
David
 
To step through code as vletm suggests, Click on Alt F8 and select "Step Into" and then your code will appear. To step through the visible code in the VBE, click on F8 for each step and you will then see what each step is doing. If you wish to see the print out of each step, activate the Immediate Window and Add "Debug Print" to your code. Look at this link for more info on this procedure.

https://stackoverflow.com/questions/2916287/where-does-vba-debug-print-log-to

https://www.excelcampus.com/vba/vba-immediate-window-excel/
 
Hi Vletm, Thanks for your reply.
Problem is this is happening between me dbl clicking in the cell and before Worksheet_BeforeDoubleClick routine fires, and I have no idea what happens between those 2 events.
Could you maybe give me an example of what macro to check.
Thanks
David
 
mdavid
Run ALL Your Macros step-by-step or
of course
You can run Macro-by-Macro step-by-step
until You'll find Your challenge.
Without any sample file, only You can do it!
 
If I want to know what's happening when I dbl click a cell on a sheet then the only macro I know to go to is:
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

and I find that the event is happening before entering this sub, I don't know where else to go to check.
 
mdavid
If You don't know what makes that challenge then
You should check more!
Did You run/ test ALL Your Marcos with step-by-step?
You have shown three Macros, is there more?
... or ...
Do same happen if You make a new file?
That's possible too!
 
Have you looked at any Conditional Formatting that may be involved/triggered?

Can you post the file here?
 
Brad Yundt replied to my question:
"When I try doubleclicking a cell in Kusleika’s workbook, I find that the Worksheet_SelectionChange event sub runs before the one for Worksheet_BeforeDoubleClick."

Hope this helps someone
 
Hi vletm, All I asked was what is the sequence of events between double click and Worksheet_BeforeDoubleClick. I was googling this and came across Brad Yundt, I wrote to him asking for help on this issue, and he kindly replied with the answer - the problem code was in the Worksheet_SelectionChange event which is the event between double clicking the mouse and the Worksheet_BeforeDoubleClick event. I thought that seeing people here had tried to help me, I'd post the solution rather than leaving it unsolved.
Thanks
David
 
You must log in or register to reply here.
Back
Top