Picture problems (and chart ones) in Word/Excel 2007

[BerylM]

Hi, I'm trying to set up some macros/VBA to automate some of the standard settings we use in my new job, but I'm having a very hard time recording some of the ones to do with pictures, in particular. I know macro-recording has a hard time understanding nebulous things like right-clicking, so I worked out the keystrokes to do everything I wanted, and all was great - until I tried to record the keystrokes.

Doing it manually, when I paste my picture and go to the text-wrapping menu, set it to 'in front of text', go to the picture position tab and 'lock anchor' (all using the keyboard, no mouse at all) it works beautifully - but as soon as I set the macro to record, the whole text-wrapping menu is greyed out and nothing I can find will allow me to get into it.

Can anyone point me in the direction of how to access these functions while recording, or alternatively a VBA reference giving all the codes that relate to pictures and their setup?

As you've probably guessed from the subject/description, I'm having the same trouble with chart setup in XL, so same questions apply there - I need to work with a text box in a chart (which will already exist and be selected) to set all four margins to 0 and have the box size reset to fit the content.

Any help would be greatly appreciated - even if someone could just give me the specific lines of code to do the above specific operations, that would be a start and I might be able to start working things out from there! Unfortunately I'm extremely rusty at VBA (haven't done any for several years) and have only started working with Office2007 at all since I started this job at the beginning of September, let alone looked at the VBA in it!

Many thanks
BerylM

[HansV]

Unfortunately, the macro recorder fails completely in this respect. There were already many actions that couldn't be recorded in Word and Excel 2003, but in the 2007 version their number has increased.

Here is a macro that will set the margins and auto resize properties of a selected text box in Excel:

Code: Select all

Sub FixTextBox()
  With Selection
    With .ShapeRange.TextFrame
      .MarginTop = 0
      .MarginLeft = 0
      .MarginBottom = 0
      .MarginRight = 0
    End With
    .AutoSize = True
  End With
End Sub

[HansV]

Although it's a Word question, here is a macro you can use in Word to convert an inline shape (such as a picture) to a floating shape in front of the text and lock its anchor:

Code: Select all

Sub FixPicture()
  With Selection.InlineShapes(1).ConvertToShape
    .WrapFormat.Type = wdWrapFront
    .LockAnchor = True
  End With
End Sub

[BerylM]

Thanks, Hans, these are great! They should be exactly what I need.