Hey Guys,
I have the below CODE EG.1 VBA on one of my WorkSheets that works well, however, when I try to add in a second range into the code for a second action on a double click on any cell in the range N12:N100 I can not make it work!! The code I am looking to add would be as shown in CODE EG.2 I have been on google for the past couple of hours but no matter what I try I can't get it to work. How would one combine these two together ?
Really appreciate any and all help
CODE EG.1
CODE EG.2
I have the below CODE EG.1 VBA on one of my WorkSheets that works well, however, when I try to add in a second range into the code for a second action on a double click on any cell in the range N12:N100 I can not make it work!! The code I am looking to add would be as shown in CODE EG.2 I have been on google for the past couple of hours but no matter what I try I can't get it to work. How would one combine these two together ?
Really appreciate any and all help
CODE EG.1
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F12:F40")) Is Nothing Then
Cancel = True
'MESSAGE NOTIFICATION
Dim Title As String
Dim Message As String
Dim PauseTime As Integer
Dim WScriptShell As Object
Dim ConfigString As String
Dim strplant As String
Message = "Sheet Is pulling in the BOM For " & Target.Value & " This can ake 1-2min"
PauseTime = 5
Title = "BOM DATA"
Set WScriptShell = CreateObject("WScript.Shell")
ConfigString = "mshta.exe vbscript:close(CreateObject(""WScript.Shell"")." & _
"Popup(""" & Message & """," & PauseTime & ",""" & Title & """))"
WScriptShell.Run ConfigString
'END OF MESSAGE NOTIFICATION
Worksheets("DATA Hold").Range("R3").Value = Target.Value
Worksheets("DATA Hold").Range("S3").Value = Target.Offset(0, -1).Value
Range("N12:AN12").Select
Selection.AutoFill Destination:=Range("N12:AN102"), Type:=xlFillDefault
Range("N12:AN102").Select
Range("N13:AN102").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D10:K10").Select
MsgBox "BOM For " & Target.Value & " Ready For Review", vbOK + vbInformation, "Complete"
End If
' NEXT ARGUMENT FOR WO LOOKUP
If Not Intersect(Target, Range("Q12:Q100")) Is Nothing Then
Cancel = True
Worksheets("DATA Hold").Range("R4").Value = Target.Value
WO = Target.Value
strplant = Sheets("DATA Hold").Range("R5").Value
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Would you Like To view the Order in SAP?", vbYesNo + vbQuestion, "Open SAP Order Details")
If Answer = vbYes Then
GetSAPWO (WO)
Dim Answerb As VbMsgBoxResult
Answerb = MsgBox("Would you Like To view the Operation Booking Analysis?", vbYesNo + vbQuestion, "Open SAP Bookings")
If Answerb = vbYes Then
GetSAPWOops (WO), (strplant)
Call Op_Hours
End If
Else
MsgBox "No problem, To view Details please Double click Covered By number"
End If
End If
End Sub
CODE EG.2
VBA Code:
If Not Intersect(Target, Range("N12:N100")) Is Nothing Then
Cancel = True
Worksheets("DATA Hold").Range("R8").Value = Target.Value
MTL = Target.Value
strplant = Sheets("DATA Hold").Range("R5").Value
Dim Answerc As VbMsgBoxResult
Answerc = MsgBox("Would you like to view the previous costs in SAP?", vbYesNo + vbQuestion, "Open SAP Cost Details")
If Answerc = vbYes Then
GetSAPCost (MTL), (strplant)
End If
Else
MsgBox "No problem, To view Details please Double click Material Number"
End If
End If
End Sub