none
Object (.shp) assistance RRS feed

  • Question

  • Good afternoon -

    I am seeking some assistance with a bit of code I have. I am attempting to open an Object embedded on a worksheet using a button. I think I may have gotten this for the most part. Meaning it partially works. The below highlighted area works; but only if i actually go to the object in reference and manually double-click the object to open it. Once I have manually opened the Object, the below code works, once :(

    Anybody have any ideas what may be happening or possibly another way of doing this?

    Here is what I have come up with thus far:

    Private Sub BudgetComp_Link_Click ()

         Application.ScreenUpdating = True

         Dim shp As Excel.Shape

         ‘Sheets ("Attachments").visible = True

        Sheets ("Attachments").Select   

     

        For Each shp In Me.Shapes  'Or For Each shp In Sheets ("Attachments").Shapes

        If shp.TopLeftCell.Address = Range ("B2").Address Then

          shp.Select

          Selection.Verb Verb:=xlPrimary

          Exit For

        End If

        Next 'shp

       

        'Sheets ("Attachments").visible = False

        Sheets ("Financial").Select

     

    End Sub

    Monday, November 6, 2017 11:02 PM

Answers

  • Hi rstreets2,

    Did you ever get error at "Selection.Verb Verb:=xlPrimary"? Where is the button BudgetComp_Link? 

    If it is in "Financial" sheet, you could not iterate through the shape in Me.Shapes since that's the shape collection of "Financial" sheet. I would suggest you iterate through shapes using "For Each shp In Sheets("Attachments").Shapes".

    Besides, what's the embedded file? I tried to test with a Workbook file and a picture file, both of them works well to be opened. I also tried to change them as linked file, tested result is same.

    Here is the code I'm using.

    Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Dim shp As Shape
    For Each shp In Sheets("Sheet1").Shapes
    If shp.TopLeftCell.Address = Range("B2").Address Then
          shp.Select
          Selection.Verb Verb:=xlPrimary
          Exit For
        End If
    Next
    ThisWorkbook.Activate
    Sheets("Sheet2").Select
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by rstreets2 Tuesday, November 7, 2017 8:43 PM
    Tuesday, November 7, 2017 1:52 AM

All replies

  • I have also attempted the below which works every time. However, the Object name would be different every time so does not help in that aspect... 

        

    For Each shp In Me.Shapes  'Or For Each shp In Sheets("Attachments").Shapes

    If shp.TopLeftCell.Address = Range("B2").Address Then

    ActiveSheet.Shapes.Range(Array("Object 44")).Select

    'shp.Open

    'shp.Select

    Selection.Verb Verb:=xlPrimary

    Exit For

    End If

    Next 'shp


    • Edited by rstreets2 Monday, November 6, 2017 11:17 PM update code
    Monday, November 6, 2017 11:15 PM
  • Hi rstreets2,

    Did you ever get error at "Selection.Verb Verb:=xlPrimary"? Where is the button BudgetComp_Link? 

    If it is in "Financial" sheet, you could not iterate through the shape in Me.Shapes since that's the shape collection of "Financial" sheet. I would suggest you iterate through shapes using "For Each shp In Sheets("Attachments").Shapes".

    Besides, what's the embedded file? I tried to test with a Workbook file and a picture file, both of them works well to be opened. I also tried to change them as linked file, tested result is same.

    Here is the code I'm using.

    Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Dim shp As Shape
    For Each shp In Sheets("Sheet1").Shapes
    If shp.TopLeftCell.Address = Range("B2").Address Then
          shp.Select
          Selection.Verb Verb:=xlPrimary
          Exit For
        End If
    Next
    ThisWorkbook.Activate
    Sheets("Sheet2").Select
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by rstreets2 Tuesday, November 7, 2017 8:43 PM
    Tuesday, November 7, 2017 1:52 AM
  • Hi Terry -

    Thanks again for your assistance. I feel pretty dumb right now because I actually had the solution right in my own code.

    You are correct. The highlighted portion of code below was the key. I actually had that in my code originally. Just wasn't paying attention....errr. I did use a few parts of your too :)

    Anyhow this is the final result:

    Private Sub BudgetComp_Link_Click() 'Opens the Budget Comparison Attachment

        Dim wb As Workbook

        Set wb = ActiveWorkbook

        Application.ScreenUpdating = False

        'Sheets("Attachments").visible = True

        Sheets("Attachments").Select

        Dim shp As Shape

        For Each shp In Sheets("Attachments").Shapes

        If shp.TopLeftCell.Address = Range("B2").Address Then

          shp.Select

          Selection.Verb Verb:=xlPrimary

          Exit For

        End If

        Next 'shp

        ThisWorkbook.Activate

        'Sheets("Attachments").visible = False

        Sheets("Financial").Select

        'Application.ScreenUpdating = True

     End Sub

    I did have one question that you might be able to assist with....if the Object the code is opening is an Excel workbook, how can I have the Object (workbook) open and be selected as the active workbook in view? Also while having the bottom portion of my existing code (bold font) in tact?

    Guess I would kind of need to run the bold code above, then be able to select the Object (workbook) that was just opened? Lol...may need a new post for this one :)

    Tuesday, November 7, 2017 5:54 PM
  • Hi again -

    So I kind of answered my own question at the bottom of my last reply. Here is my final code that brings the Object, if Object is Excel workbook, to active window.

    Thank you Terry for all of your assistance! Much appreciated :)

    Private Sub BudgetComp_Link_Click() 'Opens the Budget Comparison Attachment

       

        Dim wb As Workbook

        Set wb = ActiveWorkbook

        Application.ScreenUpdating = False

       

        'Sheets("Attachments").visible = True

           

        Sheets("Attachments").Select

        Dim shp As Shape

        For Each shp In Sheets("Attachments").Shapes

        If shp.TopLeftCell.Address = Range("B2").Address Then

          shp.Select

          'Selection.Verb Verb:=xlPrimary

          Selection.Verb Verb:=xlOpen = 2

          Exit For

        End If

        Next 'shp

        ActiveWindow.WindowState = xlMaximized

        ActiveWindow.Activate

        ThisWorkbook.Activate

        'Sheets("Attachments").visible = False

        Sheets("Financial").Select

       

        For Each wb In Application.Workbooks

              If Left(wb.Name, 9) = "Worksheet" Then

              wb.Activate

              Exit For

              End If

        Next wb

     

    End Sub

    Tuesday, November 7, 2017 8:43 PM