0

Hoping you might be able to help me with this question or to point me in the right direction. I am trying to use a Macro so that when I click on a button and sheet 1, it will look for an embedded object in sheet 2 and open it.

I have three buttons: Strategy 1 Stratgey 2 Strategy 3

Each of them open seperate workbooks e.g. Strategy 1 will open Test Plan 1, Strategy 2 will open Test Plan 2, etc.

Below is the code that I have for Strategy One. This is repeated for Strategy 2 and 3 by just replacing the sub name with two and three with the same for OLEObjects being changed to TestTwo/TestThree/etc.:

Sub StrategyOneTesting()
    Dim oEmbFile As Object
    Application.DisplayAlerts = False
    Set oEmbFile = ThisWorkbook.Sheets("TestPlans").OLEObjects("TestOne")
    oEmbFile.Verb Verb:=xlPrimary
    Set oEmbFile = Nothing
    Application.DisplayAlerts = True
End Sub

The problem I am having is that it will work for my laptop (working on Windows 10 Excel 2016), however it won't work on my other laptop working off Windows 10 Excel 2013. The error below always appears:

Run-Time Error '1004'. Unable to get the OLEObjects property of the Worksheet Class

Thanks

dwirony
  • 5,487
  • 3
  • 21
  • 43
vinnievienna
  • 1
  • 1
  • 2
  • the object has to be available on both machines in the exact same location (folder path). – Scott Holtzman Oct 13 '20 at 20:24
  • I agree with Scott. The said object (ActiveX control) has to be installed and registered on the machine. – S Meaden Oct 13 '20 at 20:27
  • What kind of "embedded object" is it about? – FaneDuru Oct 13 '20 at 21:02
  • Thanks both for your responses. I tried to replicating the exact same steps on the second laptop as I did origninally, however the same error appears. The debugging is highlighting the line 'Set oEmbFile = ThisWorkbook.Sheets("TestPlans").OLEObjects("TestOne")' as there being a problem. Would this have something to do with the version of Excel I am using on the second laptop (Excel 2013) – vinnievienna Oct 13 '20 at 21:17
  • @FaneDuru the objects that are embedded are other excel spreadsheets. I want to be able to send one spreadsheet out that only has 4 buttons on it. Once the user clicks a button, another spreadsheet will appear with all the information they need. Thanks – vinnievienna Oct 13 '20 at 21:19
  • Do you use the same option between `Link to file` and `Display as icon` in both cases? If I remember well, only 2010 version used to accept only one of them. Are you paying attention to this aspect, or follow the default behavior, which may be different? – FaneDuru Oct 13 '20 at 21:44
  • @FaneDuru I used "Display as icon" for both cases and not link to file. I think managed to figure it out. In my second tab that contained all of the embedded objects (spreadsheets) for some reason it was defaulting to Object 1, Object 2, etc. By changing the first embeded file back to "TestOne" it then matched with the ".OLEObjects("TestOne")" - thank you all for your input – vinnievienna Oct 13 '20 at 21:59

0 Answers0