none
Display as Icon Default RRS feed

  • Question

  • Hello - 

    I have a procedure of sorts that attaches/embeds a file into a worksheet. The steps call for a user to locate a file of their choice to attach/embed into the worksheet...everything works great btw! Bifg thanks to David JunFeng and James Cone for their assistance on that one!

    Anyhow, so now I am wondering if there is a way to set the "Display as Icon" option as a default so users do not have to select or check the box? Anyone know if this is possible?

    Here is my code and a screenshot of the area I am referring to:                         

    Sheets("Attachments").Range("C3").Select
        Application.Dialogs(xlDialogInsertObject).Show

        r = MsgBox("Please enter a brief description of the file you just attached into the selected cell", _
                vbQuestion + vbOKCancel, "Attention")

            If r = vbCancel Then
            Call RemoveAttach1_Click
            Exit Sub

            Else
            If r = vbOK Then

    End If

    End If

    If anyone know how or if this can be accomplished I would be ever so grateful :)

    Thank in advance for any assistance!

    Tuesday, March 15, 2016 5:25 PM

Answers

  • Hi, rstreets2

    Sorry for my mistake, you could modify like below:
    Sub Demo()
    
       Dim fileName As Variant
    
        'get file name from open file dialog box.
        fileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel files(*.xlsx),*.xlsx", Title:="Choose file to attach")
        
        'if no file chosen then close sub
        If fileName = False Then
            MsgBox ("No file chosen")
            Exit Sub
        End If
        
        'insert file into sheet.
        ActiveSheet.OLEObjects.Add fileName:=fileName, Link:=False, _
                DisplayAsIcon:=True
    
    End Sub

    Thanks for your understanding.
    • Marked as answer by rstreets2 Monday, April 4, 2016 6:25 PM
    Friday, April 1, 2016 8:29 AM

All replies

  • Try this -

    Application.Dialogs(xlDialogInsertObject).Show , , , True

    If experimenting with other optional arguments an inappropriate one might cause your Excel to freeze.

    • Proposed as answer by David_JunFeng Wednesday, March 16, 2016 3:12 AM
    • Marked as answer by David_JunFeng Thursday, March 24, 2016 8:48 AM
    • Unmarked as answer by rstreets2 Wednesday, March 30, 2016 10:18 PM
    Tuesday, March 15, 2016 11:06 PM
    Moderator
  • Hi Peter and thank you for response. My apologies for the delay in responding back. So I did attempt your suggestion, which does work and sets the Display as Icon checkbox as a default...however, the statement you made in regards to other optional arguments causing the program to freeze does come into play. This method does indeed cause the program to freeze or lockup.

    Might there be another way around this?

    Wednesday, March 30, 2016 10:21 PM
  • >>>however, the statement you made in regards to other optional arguments causing the program to freeze does come into play. This method does indeed cause the program to freeze or lockup.

    According to your description, I have reproduced this issue. unfortunately, I can't find any solution to overcome it. This workaround is that you could use OLEObjects.Add method to add a new OLE object to a sheet, refer to below code:

    Sub Demo()
    
       Dim fileName As Variant
    
        'get file name from open file dialog box.
        fileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel files(*.xlsx),*.xlsx", Title:="Choose file to attach")
        
        'if no file chosen then close sub
        If fileName = False Then
            MsgBox ("No file chosen")
            Exit Sub
        End If
        
        'insert file into sheet.
        ActiveSheet.OLEObjects.Add fileName:=ftopen, Link:=False, _
                DisplayAsIcon:=True
    
    End Sub

    For more information, click here to refer about OLEObjects.Add Method (Excel)

    In addition you could submit any feedback to User Voice:

    https://excel.uservoice.com/

    Thanks for your understanding.


    Thursday, March 31, 2016 7:12 AM
  • Hi David and thank you for your response :)

    I do like your suggestion and ran into a lil problem...see the below error. It does not like this portion of the code for some reason?



    Thursday, March 31, 2016 9:54 PM
  • Hi, rstreets2

    Sorry for my mistake, you could modify like below:
    Sub Demo()
    
       Dim fileName As Variant
    
        'get file name from open file dialog box.
        fileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel files(*.xlsx),*.xlsx", Title:="Choose file to attach")
        
        'if no file chosen then close sub
        If fileName = False Then
            MsgBox ("No file chosen")
            Exit Sub
        End If
        
        'insert file into sheet.
        ActiveSheet.OLEObjects.Add fileName:=fileName, Link:=False, _
                DisplayAsIcon:=True
    
    End Sub

    Thanks for your understanding.
    • Marked as answer by rstreets2 Monday, April 4, 2016 6:25 PM
    Friday, April 1, 2016 8:29 AM
  • Excellent! That worked much better. Exactly what I was looking for :)

    Thank you David for your assistance. You are wonderful!

    Monday, April 4, 2016 6:25 PM