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

 

[rlv01]

So what if there is a formula? I don't see anything wrong with what you have except that I'd move the Cancel statement to the end.

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
            Sheets("Erlang").Range("c5").Value = Target.Value
        End If
    End If
    Cancel = True
End Sub

 

[Peter_SSs]

.. except that I'd move the Cancel statement to the end.

I definitely would not do that. If the user has 'Allow editing directly in cells' set then moving the Cancel statement would stop them editing any cell in the worksheet with a double-click.

@Darren_workforce
What is going wrong with your code. I also don't see anything wrong with your code ....
... provided the range you want the code applied to is actually C16:L16 (tricky to read in your post when lower case is used).

BTW, unless you have merged cells in the target range there is no need for the marked lines below since without merged cells you cannot double-click more than one cell at a time.

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

 

[Darren_workforce]

I appreciate the input and apologies for the use of the lower case. So the code seems to work if the source cell I'm double clicking on is just data. EX: I add a 2 and double click the cell. The 2 will get added to the referenced cell on the other worksheet. The problem is the source cell I want to click on is actually the results of a formula. When I double click the source cell with the formula. I get the following:

The cell I'm clicking on is F16 which doesn't matter. But that is the concern I'm having. The popup appears and the data within the cell is not transferred over to the other worksheet.

If I cannot solve this, I was thinking maybe an alternate solution would be to just setup another cell somewhere on the worksheet. When I double click on it, it takes the results of the formula (F16) and pastes the results into C5 of the other worksheet.

I'm very open to any suggestions the pros want to offer.

 

[Peter_SSs]

Are you sure that you have Cancel = True in the code that is being triggered? Your picture looks like the cell that you have double-clicked is in edit mode which would indicate to me that Cancel = True has not bee processed.

Add these two lines to the code and see what messages you get

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
            MsgBox Cancel
            Cancel = True
            MsgBox Cancel
            Sheets("Erlang").Range("c5") = Target.Value
        End If
    End If
End Sub

 

[Darren_workforce]

Are you sure that you have Cancel = True in the code that is being triggered? Your picture looks like the cell that you have double-clicked is in edit mode which would indicate to me that Cancel = True has not bee processed.

Add these two lines to the code and see what messages you get

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
            MsgBox Cancel
            Cancel = True
            MsgBox Cancel
            Sheets("Erlang").Range("c5") = Target.Value
        End If
    End If
End Sub

I updated the code. Same popup displaying the formula within the cell but no message box popup. Just to test, I overrode the formula and just manually added a 4 and tried it. First message box was false' and the second message box was 'true'

 

[Peter_SSs]

As a further test, can go to File -> Options -> Advanced and uncheck "Allow editing directly in cells" -> OK
and test with the formula cell again.

 

[Darren_workforce]

As a further test, can go to File -> Options -> Advanced and uncheck "Allow editing directly in cells" -> OK
and test with the formula cell again.

OK yes that seemed to fix it. Having that disabled won't affect anything else on the worksheets negatively, will it?

Also, is it possible to effect multiple ranges using a single double click formula? I tried adding a second formula identical to my original and just changing the effected cells and sub name but it didn't seem to work at all.

 

[Peter_SSs]

OK yes that seemed to fix it. Having that disabled won't affect anything else on the worksheets negatively, will it?

It will simply stop you being able to put a cell into edit mode with a double-click. You can still edit a cell by selecting it and either clicking in the formula bar or pressing F2 on the keyboard.
However, something else must be going on as you you should not have to change that setting.
Could you do another little test for me?

1. In Options put the setting back to "Allow editing directly in cells"
2. With a copy of your workbook, in the 'Staffing' sheet's vba module, remove (or comment out) the whole double-click code and replace it with this.
   

   Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Cancel = True
   End Sub

3. Now double-click the formula cell.

Does the formula cell go in to edit mode or not?

is it possible to effect multiple ranges using a single double click formula?

Try like this

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") = Target.Value
  ElseIf Not Intersect(Target, Range("B20:B25")) Is Nothing Then
    Cancel = True
    MsgBox "You double-clicked in B20:B25"  '< Replace with your desired code
  ElseIf Target.Address = "$J$1" Then
    Cancel = True
    MsgBox "You double-clicked J1"          '< Replace with your desired code
  End If
End Sub

 

[Darren_workforce]

It will simply stop you being able to put a cell into edit mode with a double-click. You can still edit a cell by selecting it and either clicking in the formula bar or pressing F2 on the keyboard.
However, something else must be going on as you you should not have to change that setting.
Could you do another little test for me?

1. In Options put the setting back to "Allow editing directly in cells"
2. With a copy of your workbook, in the 'Staffing' sheet's vba module, remove (or comment out) the whole double-click code and replace it with this.
   

   Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Cancel = True
   End Sub

3. Now double-click the formula cell.

Does the formula cell go in to edit mode or not?



Try like this

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") = Target.Value
  ElseIf Not Intersect(Target, Range("B20:B25")) Is Nothing Then
    Cancel = True
    MsgBox "You double-clicked in B20:B25"  '< Replace with your desired code
  ElseIf Target.Address = "$J$1" Then
    Cancel = True
    MsgBox "You double-clicked J1"          '< Replace with your desired code
  End If
End Sub

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.
My original row of C16:L16 does seem to work but I wanted to add C15:L15 to the double-clickable range. The ElseIf Target.Address would also need to be on the "Erlang" tab.

I have both the short code to eliminate the edit mode but also the last code you provided. Now when attempting to run, I on an 'ambiguous" popup since the sub names are identical.