4 Messages
Digest #4451
Messages
Sun Dec 30, 2018 9:16 am (PST) . Posted by:
ridgebacksexcel
I get a run time error 1004 code. "Unable to get the Insert property of the Pictures class" error message.
The bold line is the highlighted Yellow line when I run debug.
Private Sub Worksheet_Change(ByVal Target As Range)
For Each oCell In Range("Tickers")
If Not Application.Intersect(Target, oCell) Is Nothing Then
Set oCell2 = oCell.Offset(0, 1)
On Error Resume Next
oCell2.Worksheet.Shapes("Image:" & oCell2.Address).Delete
On Error GoTo 0
If oCell.Value <> "" Then
sChart = "https://c.stockcharts.com/c-sc/sc?chart=" & oCell.Value & ",uu[305,180]dacayaci[pb20!b50][dg]"
Set p = oCell2.Worksheet.Pictures.Insert(sChart)
p.Name = "Image:" & oCell2.Address
p.Top = oCell2.Top + 10
p.Left = oCell2.Left + 4
Set p = Nothing
End If
End If
Next oCell
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
I dug up an old "dynamic chart" workbook I created when I was first fiddling around with inserting charts. I had to change the URL for StockCharts images, but it still seems to work here once that was done.
It does nothing until something is change in the "Tickers" range (yellow shaded cells). Then, the EXCEL change event is triggered and it insert an image to the cell immediately to the right of the entered ticker symbol.
It's a very short VBA routine. If RCHCreateComment() is no longer working, maybe something like that could work for what you need?
On Thu, Dec 27, 2018 at 6:35 PM tmallen2@... wrote:
No it does not work in my new version of Excel. I am using the exact same formula as you are. It just pulls up a new comment box and Value#! error code.
I'll keep playing with it since I know it works on an older version of Excel. It must be blocking the image somehow.
I'll get back to you in a few days when I have more time to play with it.
The bold line is the highlighted Yellow line when I run debug.
Private Sub Worksheet_Change(ByVal Target As Range)
For Each oCell In Range("Tickers")
If Not Application.Intersect(Target, oCell) Is Nothing Then
Set oCell2 = oCell.Offset(0, 1)
On Error Resume Next
oCell2.Worksheet.Shapes("Image:" & oCell2.Address).Delete
On Error GoTo 0
If oCell.Value <> "" Then
sChart = "https://c.stockcharts.com/c-sc/sc?chart=" & oCell.Value & ",uu[305,180]dacayaci[pb20!b50][dg]"
Set p = oCell2.Worksheet.Pictures.Insert(sChart)
p.Name = "Image:" & oCell2.Address
p.Top = oCell2.Top + 10
p.Left = oCell2.Left + 4
Set p = Nothing
End If
End If
Next oCell
---In smf_addin@yahoogroups.com, <rharmelink@
I dug up an old "dynamic chart" workbook I created when I was first fiddling around with inserting charts. I had to change the URL for StockCharts images, but it still seems to work here once that was done.
It does nothing until something is change in the "Tickers" range (yellow shaded cells). Then, the EXCEL change event is triggered and it insert an image to the cell immediately to the right of the entered ticker symbol.
It's a very short VBA routine. If RCHCreateComment(
On Thu, Dec 27, 2018 at 6:35 PM tmallen2@... wrote:
No it does not work in my new version of Excel. I am using the exact same formula as you are. It just pulls up a new comment box and Value#! error code.
I'll keep playing with it since I know it works on an older version of Excel. It must be blocking the image somehow.
I'll get back to you in a few days when I have more time to play with it.
Sun Dec 30, 2018 9:17 am (PST) . Posted by:
ridgebacksexcel
I've been playing around in the Trust Center settings to see if something was changed but no luck so far.
Sun Dec 30, 2018 10:31 am (PST) . Posted by:
ridgebacksexcel
I started my laptop and loaded a test file that I have been playing with. My laptop does not have the new Excel update yet. The RCHCreateComment function definitely works on my laptop but not on my desktop with the new Excel update.
Attached are two screenshots showing my laptop and desktop of the same Excel sheet.
Also I can toggle show Comments on and off on my laptop. When they are off the sheet looks just like my Desktop sheet but without the Yellow Comment boxes.. So somehow the desktop is either not showing the comments and I can't toggle them on or never created them in the first place. Weird.
When I hide comments on my laptop it shows nothing. No yellow boxes either.
When I add a comment on my desktop it looks different. Linked to Onedrive? Not the same type as before so that may be the problem. Excel completely changed the Display and functionality of Comments to make them dynamic.
I've attached the new comment type as well. Cell A8 with a purple triangle.
Attached are two screenshots showing my laptop and desktop of the same Excel sheet.
Also I can toggle show Comments on and off on my laptop. When they are off the sheet looks just like my Desktop sheet but without the Yellow Comment boxes.. So somehow the desktop is either not showing the comments and I can't toggle them on or never created them in the first place. Weird.
When I hide comments on my laptop it shows nothing. No yellow boxes either.
When I add a comment on my desktop it looks different. Linked to Onedrive? Not the same type as before so that may be the problem. Excel completely changed the Display and functionality of Comments to make them dynamic.
I've attached the new comment type as well. Cell A8 with a purple triangle.
Attachment(s) from
3 of 3 Photo(s)
Sun Dec 30, 2018 10:35 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Hmm. Working fine here...not sure what to tell you?
Does the URL for the image work in your browser? Or the IE object within
EXCEL (alt+d+d+w)?
Trust Center is probably irrelevant if the VBA code is already being used.
Possibly a firewall?
On Sun, Dec 30, 2018 at 10:16 AM tmallen2@... wrote:
> I get a run time error 1004 code. "Unable to get the Insert property of
> the Pictures class" error message.
>
> The bold line is the highlighted Yellow line when I run debug.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> For Each oCell In Range("Tickers")
> If Not Application.Intersect(Target, oCell) Is Nothing Then
> Set oCell2 = oCell.Offset(0, 1)
> On Error Resume Next
> oCell2.Worksheet.Shapes("Image:" & oCell2.Address).Delete
> On Error GoTo 0
> If oCell.Value <> "" Then
> sChart = "https://c.stockcharts.com/c-sc/sc?chart=" &
> oCell.Value & ",uu[305,180]dacayaci[pb20!b50][dg]"
> *Set p = oCell2.Worksheet.Pictures.Insert(sChart)*
> p.Name = "Image:" & oCell2.Address
> p.Top = oCell2.Top + 10
> p.Left = oCell2.Left + 4
> Set p = Nothing
> End If
> End If
> Next oCell
>
>
> ---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
>
> I dug up an old "dynamic chart" workbook I created when I was first
> fiddling around with inserting charts. I had to change the URL for
> StockCharts images, but it still seems to work here once that was done.
>
> It does nothing until something is change in the "Tickers" range (yellow
> shaded cells). Then, the EXCEL change event is triggered and it insert an
> image to the cell immediately to the right of the entered ticker symbol.
>
> It's a very short VBA routine. If RCHCreateComment() is no longer working,
> maybe something like that could work for what you need?
>
> On Thu, Dec 27, 2018 at 6:35 PM tmallen2@... wrote:
>
> No it does not work in my new version of Excel. I am using the exact same
> formula as you are. It just pulls up a new comment box and Value#! error
> code.
>
> I'll keep playing with it since I know it works on an older version of
> Excel. It must be blocking the image somehow.
>
> I'll get back to you in a few days when I have more time to play with it.
>
>
>
>
Does the URL for the image work in your browser? Or the IE object within
EXCEL (alt+d+d+w)?
Trust Center is probably irrelevant if the VBA code is already being used.
Possibly a firewall?
On Sun, Dec 30, 2018 at 10:16 AM tmallen2@... wrote:
> I get a run time error 1004 code. "Unable to get the Insert property of
> the Pictures class" error message.
>
> The bold line is the highlighted Yellow line when I run debug.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> For Each oCell In Range("Tickers")
> If Not Application.Intersect(Target, oCell) Is Nothing Then
> Set oCell2 = oCell.Offset(0, 1)
> On Error Resume Next
> oCell2.Worksheet.Shapes("Image:" & oCell2.Address).Delete
> On Error GoTo 0
> If oCell.Value <> "" Then
> sChart = "https://c.stockcharts.com/c-sc/sc?chart=" &
> oCell.Value & ",uu[305,180]dacayaci[pb20!b50][dg]"
> *Set p = oCell2.Worksheet.Pictures.Insert(sChart)*
> p.Name = "Image:" & oCell2.Address
> p.Top = oCell2.Top + 10
> p.Left = oCell2.Left + 4
> Set p = Nothing
> End If
> End If
> Next oCell
>
>
> ---In smf_addin@yahoogroups.com, <rharmelink@
>
> I dug up an old "dynamic chart" workbook I created when I was first
> fiddling around with inserting charts. I had to change the URL for
> StockCharts images, but it still seems to work here once that was done.
>
> It does nothing until something is change in the "Tickers" range (yellow
> shaded cells). Then, the EXCEL change event is triggered and it insert an
> image to the cell immediately to the right of the entered ticker symbol.
>
> It's a very short VBA routine. If RCHCreateComment(
> maybe something like that could work for what you need?
>
> On Thu, Dec 27, 2018 at 6:35 PM tmallen2@... wrote:
>
> No it does not work in my new version of Excel. I am using the exact same
> formula as you are. It just pulls up a new comment box and Value#! error
> code.
>
> I'll keep playing with it since I know it works on an older version of
> Excel. It must be blocking the image somehow.
>
> I'll get back to you in a few days when I have more time to play with it.
>
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
