Run 3 Subs???????

[Ray Rz]

The issue is I have 3 sub procedures to click to get 3 different files with the right click button. I use the file names to run the report but I cannot figure out how to run the 3 Sub procedures back to back as the 3 files are selected. I tried a macro to ruin them to no avail. Here are the 3 Subs and if anyone could tell me how to run all 3, I would appreciate it...

Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)


If Target.Address(0, 0) = "N10" Then

myfilepathN10 = Application.GetOpenFilename()
If myfilepathN10 = False Then
Target = ""
Else
Target = myfilepathN10
End If
Cancel = True
End If


End Sub


Sub Worksheet_BeforeRightClick1(ByVal Target As Range, Cancel As Boolean)


If Target.Address(0, 0) = "N12" Then

myfilepathN12 = Application.GetOpenFilename()
If myfilepathN12 = False Then
Target = ""
Else
Target = myfilepathN12
End If
Cancel = True


End If


End Sub


Sub Worksheet_BeforeRightClick2(ByVal Target As Range, Cancel As Boolean)


If Target.Address(0, 0) = "N14" Then

myfilepathN14 = Application.GetOpenFilename()
If myfilepathN14 = False Then
Target = ""
Else
Target = myfilepathN14
End If
Cancel = True




End Sub

Thanks,
Ray

 

[JoeMo]

You could use a Select Case like this:

Select Case Target.Address(0,0)
    Case "N10"
    myfilepathN10 = Application.GetOpenFilename()
        If myfilepathN10 = False Then
            Target = ""
        Else
            Target = myfilepathN10
       End If
   Cancel = True
   End If
   Case "N12"
'and so on.......
End Select

 

[Ray Rz]

Hi JoeMo, I tried the following:
Select Case Target.Address(0, 0)
Case "N10"
myfilepathN10 = Application.GetOpenFilename()
If myfilepathN10 = False Then
Target = ""
Else
Target = myfilepathN10
End If
Cancel = True
End If
Case "N12"
myfilepathN12 = Application.GetOpenFilename()
If myfilepathN12 = False Then
Target = ""
Else
Target = myfilepathN12
End If
Cancel = True
End If
Case "N14"
myfilepathN12 = Application.GetOpenFilename()
If myfilepathN14 = False Then
Target = ""
Else
Target = myfilepathN14
End If
Cancel = True
End If
End Select

I put it into the Module and tried it in the Worksheet as well. Both times it died on the first line and did not let me even run any Sub. Any addl suggestions would be appreciated.

Thanks,
Ray

 

[JoeMo]

Well, I didn't give you a full procedure, just an idea of how to proceed. A subrouitne needs, at a minimum, to start with "Sub" ...

Put this at the top (w/o the quote marks) "Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)"

EDIT: BTW, it's not clear to me what you are trying to achieve since your procedure doesn't appear to do anything with the information it gathers.

 

[dmt32]

hi,
see if following update to your code does what you want

Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim myfilepath As Variant
    If Not Intersect(Target, Me.Range("N10,N12,N14")) Is Nothing Then
    Cancel = True
    myfilepath = Application.GetOpenFilename()
    If Not VarType(myfilepath) = vbBoolean Then Target = myfilepath
    
    End If
End Sub

Dave

 

[Ray Rz]

I set it up as follows:

Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)


Select Case Target.Address(0, 0)
Case "N10"
myfilepathN10 = Application.GetOpenFilename()
If myfilepathN10 = False Then
Target = ""
Else
Target = myfilepathN10
End If
Cancel = True
End If
Case "N12"
myfilepathN12 = Application.GetOpenFilename()
If myfilepathN12 = False Then
Target = ""
Else
Target = myfilepathN12
End If
Cancel = True
End If
Case "N14"
myfilepathN14 = Application.GetOpenFilename()
If myfilepathN14 = False Then
Target = ""
Else
Target = myfilepathN14
End If
Cancel = True
End Select


End Sub

I think I now need to put in a statement to make it stop at the End If statement. Or possibly logic to tell it to loop? I am not positive here.

Ray

 

[Ray Rz]

Dave,
This is perfect. Thanks so much. Worked excellent.
Ray

 

[dmt32]

Dave,
This is perfect. Thanks so much. Worked excellent.
Ray

It was bit of a guess but glad solution worked ok

Dave

 

[Ray Rz]

Hi Dave,
Last thing I need a little help on is my process I am using to setup the data in my worksheet. I have 3 files I open and drop into my file. My issue is that I changed to the code you gave me and now I am receiving an error at the Sub Macro2 script for "Workbooks.Open Range("N12").Value where it tells me that it is N12 empty. Then I just run the refresh on my tables and it finishes.

Option Private Module


Sub Macro1()




MsgBox ("Update may take several minutes, Click Ok to begin")


Workbooks.Open Range("N10").Value

'Selection.AutoFilter

Range("A1:P224").Select
Selection.Copy
Windows("VBA Extractor r57with code V2.xlsm").Activate
Worksheets("Invoice Summary").Activate
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False


Call Macro2


End Sub


Sub Macro2()


' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+f
'
Workbooks.Open Range("N12").Value
'Selection.AutoFilter
ActiveCell.Offset(0, 0).Range("A1:AQ35000").Select
Selection.Copy
Windows("VBA Extractor r57with code V2.xlsm").Activate
Worksheets("MyVendor Master").Activate

ActiveCell.Offset(-1, 0).Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False




Call Macro3


End Sub
Sub Macro3()




'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Workbooks.Open Range("N14").Value
Worksheets("July 2018").Activate
Range("A3").Select
Selection.AutoFilter
Columns("A:E").Select
Selection.EntireColumn.Hidden = False
Rows("3:3").Select
Selection.AutoFilter
ActiveCell.Offset(0, 0).Range("A2:BR26000").Select
ActiveSheet.Range("$E2").AutoFilter Field:=5, Criteria1:= _
"MyVendor"
Selection.Copy
Windows("VBA Extractor r57with code V2.xlsm").Activate
Worksheets("Const. Prog. Rpt Switches").Activate
ActiveCell.Offset(0, 0).Range("A1").Select

'ActiveCell.Offset(0, 0).Range("A2").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False


Call refresh


End Sub






' Refresh all applicable pivot tables to setup month's data


Sub refresh()
'
' refresh Macro
' refresh data
'
' Keyboard Shortcut: Ctrl+r




' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)


ActiveWorkbook.RefreshAll

' Refresh all Pivot tables in all worksheets


Dim shtTemp As Worksheet
Dim pvtTable As PivotTable

For Each shtTemp In ActiveWorkbook.Worksheets
For Each pvtTable In shtTemp.PivotTables
pvtTable.RefreshTable
Next
Next
MsgBox ("Update Complete,All data is Up-to date")


End Sub

Sorry for this issue but I am not sure why this is happening as it worked beforee I assigned the Range as the file names and now it does not.

Thanks for your great help.
Ray

 

[dmt32]

Your range is unqualified so unless the sheet with required value is active, it will return an incorrect result.

ThisWorkbook.Worksheets("Sheet1").Range("N10").Value

Change name in RED as required

Dave