Help with IF statement in VBA

[Shooter_99999]

Hi All,

Looking for some help with IF statements.

The below is an extract of a Pivot Table report (column D is normally hidden).

If the user double clicks on an entry in column A, it calls a macro (DTB) that runs another report to provide a detailed breakdown of the number clicked on.

The above uses the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True ' prevent double-click from causing Edit Mode in cell
If ActiveCell.Offset(, 3).Value = "1" Then Call DTB Else MsgBox ("You need to double click on the Actuals (Inc PO's) Column")
End Sub

I'd like to add similar functionality that if somone clicks on an entry in column C, it will call another Macro, that will provide a breakdown of the figure clicked on.


I can see how I could do this without having the message box, but cannot figure how to do it with keeping the message box.

Thanks in advance

 

[DanteAmor]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True ' prevent double-click from causing Edit Mode in cell
  If Not Intersect(Target, Range("A:A, C:C")) Is Nothing Then
    If Range("D" & Target.Row).Value = "1" Then
      If Target.Column = 1 Then
        Call DTB
      Else
        Call anotherMacro
      End If
    Else
      MsgBox ("You need to double click on the Actuals (Inc PO's) Column")
    End If
  End If
End Sub

 

[Shooter_99999]

@DanteAmor

Thanks for the reply, its appreciated.

It calls the 2 separate macros, which is great, however If I click on any other cell, it doesnt display the MsgBox.

Any ideas?

 

[DanteAmor]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True ' prevent double-click from causing Edit Mode in cell
  If Not Intersect(Target, Range("A:A, C:C")) Is Nothing Then
    If Range("D" & Target.Row).Value = "1" Then
      If Target.Column = 1 Then
        Call DBT
      Else
        Call anotherMacro
      End If
    Else
      MsgBox "There is no 1 in column D"
    End If
  Else
    MsgBox ("You need to double click on the Actuals (Inc PO's) Column")
  End If
End Sub

 

[Shooter_99999]

@DanteAmor JUst tested it and that does the trick. Thanks for your help

 

[Shooter_99999]

@DanteAmor or some other clever fellow, can you provide more assistance please?

The above works superbly, but I now need to add another condition in that when the user clicks on column B, another macro is called.

I've had a go at it, but failed miserably


Any help, greatly appreciated.

 

[DanteAmor]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True ' prevent double-click from causing Edit Mode in cell
  If Not Intersect(Target, Range("A:C")) Is Nothing Then
    If Range("D" & Target.Row).Value = "1" Then
      Select Case Target.Column
        Case 1: Call DBT          'column A
        Case 2: Call other        'column B
        Case 3: Call anotherMacro 'column C
      End Select
    Else
      MsgBox "There is no 1 in column D"
    End If
  Else
    MsgBox ("You need to double click on the Actuals (Inc PO's) Column")
  End If
End Sub

 

[Shooter_99999]

@DanteAmor Thanks a lot, that works perfectly