xlDialogInsertObject

[G12Consult]

Is there a way to grey out the 'Create New' tab on this object dialog window so I only have create from file?

 

[combo]

You can configure built in dialogs using optional arguments, see the list:

https://msdn.microsoft.com/en-us/library/office/ff838781.aspx

As an aliernative consider Application.FileDialog(msoFileDialogFilePicker) as a selector of file and insert file in code.

combo

 

[G12Consult]

Thanks Combo.

Using you alternative

Private Sub cmdAttachGroupReport_Click()

On Error GoTo Error_Handler:

  
    Dim lngCount As Long
    Dim FPath As String
        
    With Application.FileDialog(msoFileDialogFilePicker)
        
        .AllowMultiSelect = False
        .Show
        
        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
             FPath = .SelectedItems(1)
        Next lngCount

        
   'Check if file selected
    If LCase(lngCount) = "false" Then Exit Sub
        
    'Application.Goto Sheets("Exit_Reports").Range("B2")
        
    'Insert file
    ActiveSheet.OLEObjects.Add Filename:=FPath, Link:=False, DisplayAsIcon:=False
    
    
    End With

Error_Handler: MsgBox "The system has encountered an error: " & Err.Description


End Sub

However receiving error 1004 - Application-defined or object defined error on this line

ActiveSheet.OLEObjects.Add Filename:=lngCount, Link:=False, DisplayAsIcon:=False

I am running the code from the active sheet as well

 

[combo]

[tt]FileName:=FPath[/tt]

combo

 

[Andrzejek]

How is that suppose to work....

Dim lngCount [highlight #FCE94F]As Long
[/highlight]...
If [red]LCase(lngCount) = "false"[/red] Then Exit Sub

No value of Long will ever be equal lower or upper string value "false".
Long is a Number.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[combo]

You have AllowMultiSelect set to False, so the file is either selected or not. Use value returned by Show to test if a file was selected:

Private Sub cmdAttachGroupReport_Click()

On Error GoTo Error_Handler:
    Dim fd As FileDialog
    Dim FPath As String
    
    Set fd == Application.FileDialog(msoFileDialogFilePicker)    
    With fd
        .AllowMultiSelect = False
       ' Show and check if file selected
        If .Show = -1 Then
             FPath = .SelectedItems(1)
             MsgBox Fpath ' for testing
             ' Application.Goto Sheets("Exit_Reports").Range("B2")
             'Insert file
             ActiveSheet.OLEObjects.Add Filename:=FPath, Link:=False, DisplayAsIcon:=False
        End If
    End With

Error_Handler: MsgBox "The system has encountered an error: " & Err.Description

End Sub

combo

 

[G12Consult]

I got it working thanks Combo. Using your code above still threw the error but discovered it was because my sheets were protected