Adding a message Yes No option in a working code

[ipbr21054]

The code in use is shown below.

Currently the code works when a user double clicks a cell in column B
If the user double clicks in any other column they are shown a message advising to double click in column B
Currently the next step is that the value in column C is HONDA & if it is the code continues to run.

It is now that if that value is anything other than HONDA then a message needs to pop up with a question, currently its YOU CAN ONLY SELECT HONDA MOTORSYSLES, so this needs to be changed.

The question would basically be CONTINUE TO LOAD USER FORM or JUST STOP

Just stop WOULD EXIT SUB lets say user incorrectly selected wrong column.
Continue WOULD BYPASS THE CODE THAT copies value to MCVIN page & just go straight to opening the user form MotorcycleDatabase.LoadData Me, Target.Row

Basically if the value is YAMAHA or TRIUMPH etc there is no need to copy value to MCVIN sheet BUT still allow the user to see THE OTHER VALUES on user form to be loaded.

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

    If Target.Column = 2 Then
        If Target.Offset(, 1) = "HONDA" Then
            Sheets("MCVIN").Range("F7").Value = ActiveCell.Value
        Else
            MsgBox "YOU CAN ONLY SELECT HONDA MOTORCYCLES", vbCritical, "HONDA ONLY MESSAGE"
            Exit Sub
        End If
    Else
        MsgBox "YOU MUST SELECT THE VIN IN COLUMN B", vbCritical, "SELECT VIN MESSAGE"
        Exit Sub
    End If
    
   Worksheets("MCVIN").Activate
   Worksheets("MCLIST").Activate
   MotorcycleDatabase.LoadData Me, Target.Row
End Sub

 

[Georgiboy]

Maybe the below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ans As Long
    
    If Target.Column = 2 Then
        If Target.Offset(, 1) = "HONDA" Then
            Sheets("MCVIN").Range("F7").Value = ActiveCell.Value
        Else
            ans = MsgBox("Continue to load user form?", vbYesNo, "Not Honda")
            If ans <> 6 Then Exit Sub
        End If
    Else
        MsgBox "YOU MUST SELECT THE VIN IN COLUMN B", vbCritical, "SELECT VIN MESSAGE"
        Exit Sub
    End If
    
   Worksheets("MCVIN").Activate
   Worksheets("MCLIST").Activate
   MotorcycleDatabase.LoadData Me, Target.Row
End Sub

 

[ipbr21054]

I was working on the same but keep getting errors.
Can you advise please what the <>6 means ?

I ask as i need to clear the cells on the MCVIN sheet as it has values from previous search.
So i need to do this when the user selects YES like MCVIN cells B9 clear E9 clear etc etc then load form
Hence my question about <>6

I use this on the sheet MCVIN to clear the cells,so can we call it maybe ???

Private Sub ClearVinNumber_Click()
Range("F7:H7").ClearContents
Range("B9").ClearContents
Range("E9").ClearContents
Range("J9").ClearContents
Range("L9").ClearContents
Range("O9").ClearContents
Range("R9").ClearContents
Range("B11").Select
Range("A1").Select
End Sub

 

[Georgiboy]

6 means 'Yes' has been pressed on the message box. The variable 'ans' collects the answer from the message box. If 'ans' = 6 then Yes has been pressed otherwise No has been pressed.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ans As Long
    
    If Target.Column = 2 Then
        If Target.Offset(, 1) = "HONDA" Then
            Sheets("MCVIN").Range("F7").Value = ActiveCell.Value
        Else
            ans = MsgBox("Continue to load user form?", vbYesNo, "Not Honda")
            If ans = 6 Then
                ' Yes selected on message box so do something here
                Sheets("MCVIN").Range("F7:H7,B9,E9,J9,L9,O9,R9").ClearContents
            Else
                ' Assume No has been selected on message box
                Exit Sub
            End If
        End If
    Else
        MsgBox "YOU MUST SELECT THE VIN IN COLUMN B", vbCritical, "SELECT VIN MESSAGE"
        Exit Sub
    End If
    
   Worksheets("MCVIN").Activate
   Worksheets("MCLIST").Activate
   MotorcycleDatabase.LoadData Me, Target.Row
End Sub

 

[ipbr21054]

Worked a treat Thanks