Recorded Macro Fails On Error'438'

[homeric]

I am trying to create a macro or vba to get the same effect that I get through manually formatting my worksheet – The manual method allows me to select a specific part row apply the formatting then clear the formatting after saving or printing the sheet, I would then go through the whole process again on the next row I wanted to format

My manual method is to: select the part row (Covering Cols B to M) > Format > Shape Effects > Shadow > Select an option > Right Click Border > Format Shape > Effects > Shadow > Choose Color > Set Transparency to 10% > Blur 30pt > Select All Cells A1 to N21 > Save or Print (A1 to M19) > Then several undos > Start on another row…

When I try to record a macro of it I get this error:-

Run-time error '438'; Object doesn't support this property or method

Debug then shows:

Sub BorderShadowEffeet()

BorderShadowEffect Macro

Macro to put shadow border effect around pre-selected row

Keyboard Shortcut: Ctrl+Shift+Q

With Selection.ShapeRange.Shadow

.Type = msoShadow25

.Visible = msoTrue

.Style = msoShadowStyleOuterShadow

.Blur = 5 .OffsetX = 0 .OffsetY = 0

---And more lines shown, but doesn't get that far---

My attempts to create a vba to do this have just confused me even more – Can anyone help me please?

The final output I want, and which I can get manually, is like this:-

 

[rlv01]

You should post your recorded macro code.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[dave3009]

Hi

Hopefully I understood this correctly. If you draw the shape in the format you desire then you could leverage the active cell to position it, you could also hide it when it's not in use and make it visible when it is. No need to redrawn or format it each time you want to highlight the part of the row.

Example

Sub MoveShp()
With ActiveSheet.Shapes("Rectangle 1")
    .Visible = msoTrue
    .Top = ActiveCell.Top
    .Left = ActiveCell.Left
End With
End Sub

Sub HideShp()
ActiveSheet.Shapes("Rectangle 1").Visible = msoFalse
End Sub

 

[homeric]

The vba returned from the failed recorded macro is as follows:-

BorderMacroTest.xlsm - Module1 (Code)
General                                                                                           BorderShadowEffect


Sub BorderShadowEffect()
'
' BorderShadowEffect Macro
' Macro to put shadow border effect around pre-selected row
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    With Selection.ShapeRange.Shadow
        .Type = msoShadow25
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 5
        .OffsetX = 0
        .OffsetY = 0
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0.599999994
        .Size = 102
    End With
    With Selection.ShapeRange.Shadow
        .Type = msoShadow25
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 5
        .OffsetX = 0
        .OffsetY = 0
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0.1000000238
        .Size = 102
    End With
    With Selection.ShapeRange.Shadow
        .Type = msoShadow25
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 30
        .OffsetX = 0
        .OffsetY = 0
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0.1000000238
        .Size = 102
    End With
    Range("P4").Select
End Sub

I'm using Windows 10 21H1 & Excel 2016

The final output I want, and which I can get manually, is like this:-

 

[homeric]

You should post your recorded macro code.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

I've tried to do this, but unsure if it has worked

 

[homeric]

Hi

Hopefully I understood this correctly. If you draw the shape in the format you desire then you could leverage the active cell to position it, you could also hide it when it's not in use and make it visible when it is. No need to redrawn or format it each time you want to highlight the part of the row.

Example

Sub MoveShp()
With ActiveSheet.Shapes("Rectangle 1")
    .Visible = msoTrue
    .Top = ActiveCell.Top
    .Left = ActiveCell.Left
End With
End Sub

Sub HideShp()
ActiveSheet.Shapes("Rectangle 1").Visible = msoFalse
End Sub

Thanks for this dave3009 I'll be trying this idea out later today as I've got to go out shortly - I've posted the code for the macrro that failed - not sure how or if it worked, I did it as a reply to my own original question, but that seemed to repeat everything - I'll get back to you when I've tried your suggestion - homeric

 

[RoryA]

Your code assumes you have a shape selected when you run the code - is that the case?

 

[rlv01]

Which line of code produced the "Run-time error '438'; Object doesn't support this property or method" error?

 

[homeric]

Which line of code produced the "Run-time error '438'; Object doesn't support this property or method" error?

Thanks for contacting me it's this one ie the first line ... With Selection.ShapeRange.Shadow

 

[homeric]

Your code assumes you have a shape selected when you run the code - is that the case?

I'm not sure - I'll have to look into this further - When I get the correct result manually I have selected the shape, but when I run the macro i don't think it is - I recorded the macro & although I did everything I would do manually I don't think it recorded properly, or perhaps it couldn't record everything for some reason