We help IT Professionals succeed at work.

Suddenly can't record macros creating or affecting shapes

John Carney
John Carney asked
on
I've recorded many macros in the past that create and move and re-size shapes, and set or change their colors (font, fill, liners, etc.). All of a sudden, I can't do that.

Has anyone ever encountered this? And if so, what caused it  and how do I fix it? I'm using Excel 2007.

Thanks,
John
Comment
Watch Question

John CarneyReliability Business Tools Analyst II

Author

Commented:
In the meantime, how would I write the code that would format a button so that the first word ("Hide") would be white and the second word ("Buttons") would be RGB(158,231,39)?

Thanks,
John
Top Expert 2011

Commented:
Check
(a) if your security settings have been changes - Excel options
(b) if you are in EDIT mode
(c) if you are on a network and/or have an admin, check if your permissions have changed
Now also check the following:
- Two settings (Digital Certificate for VBA Projects and Visiual Basic for Applications) from the Installation Options tab may have the Not Available option selected when the application is installed.
- Change these to Run from My Computer

Also,
Top Expert 2011

Commented:
Also check y
1. Use MSO2007 Installation **.
2. Make sure the "Visual Basic for Applications" option is installed:
+ Office Shared Features
+ Visual Basic for Applications
3. Enable macros:
Click on: The Office Button | Excel Options | Trust Center | Trust Center Settings (Button on Right) | Macro Settings
Ensure you select one of the options for enabled macros - avoid the "Enable all Macros" and use one with warnings to avoid getting infected by any malicious macros.
Top Expert 2011

Commented:
Also check to see if there is another macro active/running in the VBA window.  That will gray out the Record macro button
Top Expert 2011

Commented:
Here is what the macro recorder yielded

Sub formatbutton()
    Selection.Characters.Text = "Hide Buttons"
    With Selection.Characters(Start:=1, Length:=4).Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    With Selection.Characters(Start:=5, Length:=1).Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    With Selection.Characters(Start:=6, Length:=7).Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = RGB(158, 231, 39) 'changed this manually to get what you want
    Range("E5").Select
End Sub
Most Valuable Expert 2011
Top Expert 2011

Commented:
Excel 2007 does not have that capability in the macro recorder, sadly (MS broke it) - it is restored (mostly) in 2010.
Top Expert 2011

Commented:
Rorya...I am sure that the macro recorder wrks on Excel 2007
Check above
Most Valuable Expert 2011
Top Expert 2011
Commented:
The macro recorder records almost nothing to do with shapes. I'm guessing you were working on a forms button? (I'm also surprised you got that code in 2007 as it ought to refer to themecolors and not colorindex).
I recorded inserting a rectangle, adding some text, changing the background colours and borders and then formatted part of the text, and the recorded code was:
Sub Macro4()
'
' Macro4 Macro
'

'
    Range("C10").Select
End Sub

Open in new window


Since this an issue openly acknowledged by Microsoft, I will respectfully disagree. ;)
Most Valuable Expert 2011
Top Expert 2011

Commented:
See for example this MSKB article: http://support.microsoft.com/kb/937620
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks, Rory. Sorry I've been away from this question for so long. My apologies, extermie.  Thank you for posting, but I have to go with Rory on this one.

- John