Using VBA Doubleclick on a cell containing formula

[Darren_workforce]

I have a sheet (Staffing) with a graph that borrows its information from another worksheet (Erlang). The Erlang worksheet has a lot of information I don't want to present during my meeting so that is why I moved the graph. I would like to double click on any cells within the range below on the Staffing worksheet and have the number populate on the Erlang sheet which would then update the graph. The problem is the cell I want to double click contains a formula. Is there a workaround to the code that would allow what I'm requesting to happen?

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("c16:l16")) Is Nothing Then
        If Target.Cells.Count = 1 then
            Cancel = True
            Sheets("Erlang").Range("c5") = Target.Value
        End If
    End If
End Sub

 

[Peter_SSs]

using the shortened code and reactivating "allow editing directly in cells", it did not go into edit mode and simply allowed me to double-click the cell.

Thanks. That is exactly what should have happened.

My original row of C16:L16 does seem to work

Also good news.

I wanted to add C15:L15 to the double-clickable range.

What should happen if you double-click a cell in C15:L15?

The ElseIf Target.Address would also need to be on the "Erlang" tab.

I'm not sure what you mean by that? Can you explain in more detail?

I have both the short code to eliminate the edit mode but also the last code you provided.

Remove that short code. It was just a test.

 

[Darren_workforce]

Thanks. That is exactly what should have happened.


Also good news.


What should happen if you double-click a cell in C15:L15?


I'm not sure what you mean by that? Can you explain in more detail?


Remove that short code. It was just a test.

I wanted to add C15:L15 to the double-clickable range.

What should happen if you double-click a cell in C15:L15?
The data in that cell should populate into cell C4 on the Erlang tab.

The ElseIf Target.Address would also need to be on the "Erlang" tab.

I'm not sure what you mean by that? Can you explain in more detail?
I meant to say in referencing the above item, when clicking on C15:L15, it would need to add that data into the Erlang cell C4. So I was trying to explain (poorly) that the ElseIf Target.Address line of the code needed to reference a different worksheet.

Remove that short code. It was just a test.
Done.

One thing I noticed, and maybe it's because of the adjustment in the Options, but when I click on (Staffing, C16:L16, the formatting of that cell carries over to the (Erlang, C5) cell. I tried adjusting the alignment on (Erlang, C5) as well as (Staffing, C15:L15) and it won't allow it.

 

[Peter_SSs]

What should happen if you double-click a cell in C15:L15?
The data in that cell should populate into cell C4 on the Erlang tab.

Thanks.

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("C16:L16")) Is Nothing Then
    Cancel = True
    Sheets("Erlang").Range("C5").Value = Target.Value
  ElseIf Not Intersect(Target, Range("C15:L15")) Is Nothing Then
    Cancel = True
    Sheets("Erlang").Range("C4").Value = Target.Value
  End If
End Sub

One thing I noticed, and maybe it's because of the adjustment in the Options, but when I click on (Staffing, C16:L16, the formatting of that cell carries over to the (Erlang, C5) cell. I tried adjusting the alignment on (Erlang, C5) as well as (Staffing, C15:L15) and it won't allow it.

I'm not exactly sure what you are describing but there is nothing in the code that copies or transfers formatting from 'Staffing' to 'Erlang". The code moves values only.

 

[Darren_workforce]

Thanks.

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("C16:L16")) Is Nothing Then
    Cancel = True
    Sheets("Erlang").Range("C5").Value = Target.Value
  ElseIf Not Intersect(Target, Range("C15:L15")) Is Nothing Then
    Cancel = True
    Sheets("Erlang").Range("C4").Value = Target.Value
  End If
End Sub

I'm not exactly sure what you are describing but there is nothing in the code that copies or transfers formatting from 'Staffing' to 'Erlang". The code moves values only.

OK I'll have to look into it. The source cell and target cell both are right aligned and I'm unable to adjust that to anything else. Maybe it was something done earlier in the creation of the worksheet and I thought they were related to the vba code. However, that last code did exactly what I was looking to do. I appreciate the help a great deal! Thank you again!!

Requesting advice:
Should I create a new thread for this or would you have any input?

My supervisor team wanted me to setup a workbook that broke down our team by queue. Each sheet is a queue and down Col A of each sheet are the agents staffed. I went 1 step extra and created a Lookup sheet. All you need to do is add the agent name into B4 and my formula below generates the agent's respective queues into B7:Bxx. However, the code only generates the first sheet that it finds the agent's name in. Would you know what would need to be adjusted so that ALL queues are listed for any given agent? SheetList is just a grouped column with every tab listed in the Lookup sheet.

=IFERROR(INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$A$50"),B4)>0),0)),"")

 

[Peter_SSs]

However, that last code did exactly what I was looking to do. I appreciate the help a great deal! Thank you again!!

You're welcome. Thanks for the confirmation.

Requesting advice:
Should I create a new thread for this or would you have any input?

Very different question so start a new thread.