Cannot Call Macro

L

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174
I use the following code in Excel 2010

Code: 
Private Sub CommandButton1_Click()
Set n = ActiveSheet.Pictures.Paste
With Range("f46")
t = .Top
l = .Left
On Error Resume Next
End With
With n
.Top = t
.Left = l
.ShapeRange.LockAspectRatio = msoFalse
.Width = 424
.Height = 600
Application.ScreenUpdating = False
Application.CutCopyMode = False
On Error Resume Next
End With
ActiveSheet.Pictures.Select
Call shadow
Application.ScreenUpdating = False
Range("b44:q44") = "Correlation To Offset"
On Error Resume Next
End Sub

however, the Call shadow will not run the shadow macro in excel 2007. It works fine in 2010. I'm stumped.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
lol.xls said:
I use the following code in Excel 2010

Code: 
Private Sub CommandButton1_Click()
Set n = ActiveSheet.Pictures.Paste
With Range("f46")
t = .Top
l = .Left
On Error Resume Next
End With
With n
.Top = t
.Left = l
.ShapeRange.LockAspectRatio = msoFalse
.Width = 424
.Height = 600
Application.ScreenUpdating = False
Application.CutCopyMode = False
On Error Resume Next
End With
ActiveSheet.Pictures.Select
Call shadow
Application.ScreenUpdating = False
Range("b44:q44") = "Correlation To Offset"
On Error Resume Next
End Sub

however, the Call shadow will not run the shadow macro in excel 2007. It works fine in 2010. I'm stumped.
Click to expand...

Try commenting out the On Error Resume Next statements and see what error is being generated.
 
Upvote 0
The code still pastes the image in the clipboard, it just doesn't call the shadow macro, so I never receive an error [If i'm understanding you correctly]
 
Upvote 0
I took out the On Error Resume Next and it operated the same as before. It didn't call the shadow macro, and i still did not receive an error.
 
Upvote 0
lol, sorry, i've been staring at the computer too long today. so i took out all of the On Error Resume Next lines, and i did receive an error on my second line of code [.Type = msShadow21.

Code: 
Sub shadow()'
' shadow Macro
'
    With Selection.ShapeRange.shadow
        .Type = msoShadow21
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 23
        .OffsetX = 7.7781745931
        .OffsetY = 7.7781745931
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(51, 51, 51)
        .Transparency = 0.3500000238
        .Size = 100
    End With
    With Selection.ShapeRange.shadow
        .Type = msoShadow21
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 23
        .OffsetX = 7.7781745931
        .OffsetY = 7.7781745931
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(51, 51, 51)
        .Transparency = 0.3500000238
        .Size = 100
    End With
    With Selection.ShapeRange.shadow
        .Type = msoShadow21
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 23
        .OffsetX = 7.7781745931
        .OffsetY = 7.7781745931
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(51, 51, 51)
        .Transparency = 0.3500000238
        .Size = 100
    End With
    With Selection.ShapeRange.shadow
        .Type = msoShadow21
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 23
        .OffsetX = 7.7781745931
        .OffsetY = 7.7781745931
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(51, 51, 51)
        .Transparency = 0.3500000238
        .Size = 100
    End With
    With Selection.ShapeRange.shadow
        .Type = msoShadow21
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 23
        .OffsetX = 7.7781745931
        .OffsetY = 7.7781745931
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(51, 51, 51)
        .Transparency = 0.3500000238
        .Size = 100
    End With
End Sub
 
Upvote 0
lol.xls said:
lol, sorry, i've been staring at the computer too long today. so i took out all of the On Error Resume Next lines, and i did receive an error on my second line of code [.Type = msShadow21.

Code: 
Sub shadow()'
' shadow Macro
'
    With Selection.ShapeRange.shadow
        .Type = [B][COLOR=#B22222]msoShadow21[/COLOR][/B]
        .Visible = msoTrue
        ......
Click to expand...
I don't have XL2010, but apparently they extended the number of shadow types in it because "MsgBox msoShadow20" display a value in XL2007 whereas msoShadow21 displays the empty string in XL2007. So I am guessing you will need to assign a shadow type with a lower number.
 
Upvote 0
Ok...so here's another point, if i select the image after it's been pasted, and run shadow() by itself, it formats the image the way it does when it's called in 2010...
 
Upvote 0
I normally work in XL2003 and, being retired for a number of years now, have no occasion to work with shapes or their shadows... so I am not sure what to tell you as I have no experience to reach back to. All I can do is report what I see... hopefully someone with experience with shadows will come along and respond to you. Right now it is late in the evening on Sunday, so a lot of the US volunteers are probably not online. Would say in 8 or 9 hours (at the beginning of Monday) they should start arriving back on. If you don't get a response within an hour or two of then, I would suggest responding to your message with a "Bump" message to bring it to the top of the list where it will have a better chance of being seen. Good luck.
 
Upvote 0
This works on my Excel 2007 SP3 with Err.Clear, but seems it does not work in Excel 2007 with previous SPs.
Rich (BB code): 
Private Sub CommandButton1_Click()
  
  Application.ScreenUpdating = False
  Application.CutCopyMode = False
  On Error GoTo exit_
  
  With ActiveSheet.Pictures.Paste
    .Top = Range("F46").Top
    .Left = Range("F46").Left
    .ShapeRange.LockAspectRatio = msoFalse
    .Width = 424
    .Height = 600
  End With
  Call shadow
  Range("B44:Q44").Value = "Correlation To Offset"
  Err.Clear ' <-- Fix the Err bug in Excel 2007 after calling shadow() and some actions
  
exit_:
  Application.ScreenUpdating = True
  If Err Then MsgBox Err.Description, vbCritical, "Error"

End Sub

Sub shadow()  '
  With ActiveSheet.Pictures.ShapeRange.shadow
    .Type = msoShadow21
    .Visible = msoTrue
    .Style = msoShadowStyleOuterShadow
    .Blur = 23
    .OffsetX = 7.7781745931
    .OffsetY = 7.7781745931
    .RotateWithShape = msoFalse
    .ForeColor.RGB = RGB(51, 51, 51)
    .Transparency = 0.3500000238
    .Size = 100
  End With
End Sub
 
Upvote 0
In Windows 7, using Excel 2007, everything up to date...it still doesn't call the macro. As I stated before, I can run shadow() independently and it runs fine. I can call shadow() in excel 2010 perfectly. Not sure what's happening....any more ideas? I also wanted to shout out to Rick & ZVI....This is the best forum on the internet because of the people who participate here. Thanks a million.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
I want to merge two VBA Codes !
Replies
2
Views
144
MAlhash
M
D
  • Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
219
davidepstein22
D
H
  • Solved
Cannot supress Display alert Message
Replies
4
Views
208
howard
H
W
  • Question
Macro not working on protected sheet
Replies
2
Views
82
SunnyAlv
S
A
  • Solved
VBA - link to [any] the current user's desktop
Replies
4
Views
134
AV_Geek
A

Forum statistics

Threads
1,203,727
Messages
6,056,961
Members
444,899
Latest member
Excel_Temp

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top