Embedded Word Document Issues

[Tartan Neil]

Hello

I've embedded a Word document into an Excel file. Everything works great, however, it looks ugly.

My main issues are:

• When I click on the Word file's icon, the icon shows a bounding box around it.
• The Word file's icon can currently be resized and repositioned anywhere in the Excel document.

So, my questions are:

• How do I get rid of the bounding box?
• Right now the embedded Word file seems to be "hovering" over the Excel file rather than contained within a cell. How do I get the file icon to be the content of the cell?
• Finally, once the above has been achieved, how do I lock the icons size and position inside the cell?

Thanks in advance.

 

[Kenneth Hobson]

Welcome to the forum!

You Protect the object by setting it for the object and the worksheet. This keeps the user or you from moving it around and resizing it. Once you allow the user to edit the content directly, they can resize and move it around. Your macro will need to reset those after they exit it.

At the end is how to Open the object for editing and turn off the rulerbars. You can record a macro to see the syntax for moving and resizing. Tip: Set the object's Left and Top to be the same as the Top and Left properties of a Cell that you want it in.

If you need more help, post back.

Sub Main()
  Dim wd As Word.Document
  Sheet1.Protect "", UserinterfaceOnly:=True
  Sheet1.OLEObjects("Object 1").Verb xlPrimary
    Set wd = Sheet1.OLEObjects("Object 1").Object
  'wd.ActivePane.DisplayRulers = _
    Not wd.ActiveWindow.ActivePane.DisplayRulers
  wd.ActiveWindow.DisplayVerticalRuler = False
  wd.Activate
End Sub

 

[Tartan Neil]

Thanks for the reply.

I had a feeling marcos might be involved. Unfortunately, macros are something I know nothing about.

I tried the code above and got an error message.

I've linked to two screen shots that indicate how I copied of the code (hopefully I did that correctly) and the subsequent error message.

https://drive.google.com/file/d/0BwPTtAOQo3uSbnRJN084b2lDSGM/view?usp=sharing

https://drive.google.com/file/d/0BwPTtAOQo3uSOW9VY3N1akprNTQ/view?usp=sharing.

Once again, thanks in advance for any assistance.

 

[Kenneth Hobson]

I don't see how you could get that error. The UDF error is due to something else I guess. You can post a simple example file link if you like.

https://www.dropbox.com/s/hge8glsp7iy81y9/OpenWordContentWithoutRulerBars.xlsm?dl=0

Module Code:

Sub Main()
  'Tools >  References > Microsoft Word xx.0 Object Library
  Dim wd As Word.Document, o As Object
  ActiveSheet.Protect "", UserinterfaceOnly:=True
  Set o = Sheet1.OLEObjects("Object 1")
  o.Verb xlPrimary
  Set wd = o.Object
  wd.ActiveWindow.DisplayRulers = False
  wd.ActiveWindow.DisplayVerticalRuler = False
  wd.Activate
End Sub

Right click the sheet's tab with that object, View Code, and paste:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim o As Object, c As Range
  
  ActiveSheet.Protect "", UserinterfaceOnly:=True
  Set o = ActiveSheet.OLEObjects("Object 1")
  Set c = [D3]
  With o
    o.Locked = False
    ActiveSheet.Shapes(o.Name).LockAspectRatio = msoFalse
    .Left = c.Left
    .Top = c.Top
    .Width = c.Width
    .Height = Rows(c.Row).Height
    o.Locked = True
  End With
End Sub