Excel Macro won't run properly when called from a Function

[mike1234]

Hi,
I am trying to show a larger version of a graph and some supporting cells when the user hovers the mouse over a sparkline chart. The idea is to copy an image of the target cells (which includes the graph and supporting cells) and load the image into a user form.
When I call the code from a another macro, everything works well. However if I trigger it from a function the macro returns a blank screenshot because it can't select the cells to take the picture. I modified the function so it only updates a cell (CY57) in the spreadsheet and then use the spreadsheet_change event to kick off the macro. In this case the macro runs without an error, but it still does not appropriately select the cells to capture the image and therefore returns a blank image. If I manually change the target cell (CY57) in the spreadsheet then the spreadsheet_change event runs appropriately takes the image.
Here are some more specifics:
I am using the following formula in the cell with the sparkline to call the function:
=IFERROR(HYPERLINK(Row_Location(ROW()),""),"")

Which successfully triggers and runs this function code to update the value in cell CY57:
Public Function Row_Location(Data_Row As Integer)
If ActiveSheet.Range("CY57") <> Data_Row Then ActiveSheet.Range("CY57") = Data_Row
End Function

Then I use a worksheet_change event to kick off the ExportRange macro. This all works appropriately
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("CY57"), Range(Target.Address)) _
Is Nothing Then
ExportRange
End If
End Sub

The problem occurs in the ExportRange macro. I included the code below, but I am not sure you need the whole block. Without getting into all the tests, I have narrowed down the issue to the fact that it won't allow me to select or active any cells when triggered by a result of a function or directly from the function. Because I can't do that, it exports a blank image instead of the intended cells.
Sub ExportRange() '(rng As Range)
Dim cob, sc
Application.ScreenUpdating = True
Sheets("blank").Range("CX64:Dk85").Select

Set rng = Selection

rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture

Set cob = rng.Parent.ChartObjects.Add(10, 10, 200, 200)
'remove any series which may have been auto-added...
Set sc = cob.Chart.SeriesCollection
Do While sc.Count > 0
sc(1).Delete
Loop

With cob
.ShapeRange.Line.Visible = msoFalse '<<< remove chart border
.Height = rng.Height
.Width = rng.Width
.Chart.Paste
.Chart.Export Filename:=ThisWorkbook.Path & "\temp_image.jpg", Filtername:="jpg"
.Delete
End With

UserForm1.Show

End Sub

If I manually type a number into cell CY57 then everything runs fine. If I call ExportRange from another macro it runs fine.
Sub Tester()
ExportRange ' ActiveSheet.Range("CX64:Dk85")
End Sub
It is only when the Worksheet_change event is triggered by a result of the function that it fail.
Does anyone have any ideas how to work around this?
Thanks for the help,
Mike

 

[NdNoviceHlp]

If ActiveSheet.Range("CY57") <> Data_Row Then ActiveSheet.Range("CY57") = Data_Row

What is Data_Row? Where is it set? Where is it dimmed? I'm guessing the problem is that Data_Row has not been made a global variable. Put it at the top of the code and make it available to all subs. HTH. Dave

 

[mike1234]

Hi Dave,

Thanks for the quick response. I did not set that variable, but that part of the code runs fine in all cases. All that does is update the value in CY57 to correspond to the row of data I want to display based on the mouse location. After cell CY57 updates and it correctly updates the graph as well as kicks off the worksheet_change trigger which then runs the ExportRange macro. This issue is when the ExportRange macro runs, it does not select the cells to copy to the image (i.e. Sheets("blank").Range("CX64:Dk85").Select). For some reason that action of selecting CX64:DK85 only works if I directly run the macro or manually change the value in cell CY57 (which then triggers the worksheet_change event. It does not work when the function changes the value of CY57 though.

Thanks,
Mike

 

[NdNoviceHlp]

I see that the Data_Row is actually the function input value..whoops. Should have finished the coffee first.
Anyways, why can't U just do this...

Set rng = Sheets("blank").Range("CX64:Dk85")

or maybe..

Public Function Row_Location(Data_Row As Integer)
 If ActiveSheet.Range("CY57") <> Data_Row Then 
ActiveSheet.Range("CY57") = Data_Row
ActiveSheet.Range("CY57").Select
 End Function

I really hate the use of selection and worksheet change events and if possible I code to never use them.
Dave

 

[mike1234]

Hi,

I actually started with something like what you did in the first code block, but it did not work so I tried to explicitly select the range instead (which still does not work). I also tried the 2nd approach as well, but you can't select a cell from a function. It does not allow it (although it does not error out when you try). I think the underlying issue is that you can't select a cell or range of cells to copy the image from a function. And somehow, even though the worksheet_calculate even is completely separate, when it kicks off from a result of a function, it still has the same issue.

Any other ideas?

I was going to make a small test version of this file to share it, but just read that you can't post a file in the forum.

Thanks again for all the help,
Mike

 

[NdNoviceHlp]

Well that was a lot of brain dead circular frustration. I think I have arrived at a solution. Here's my test solution. Data in A1 and B1 and this formula in C1...

=Row_Location(B1)

This in module code for worksheet function...

Public Function Row_Location(Data_Row As Range) As Integer
If ActiveSheet.Range("A1") <> Data_Row.Value Then
Row_Location = 0
Else
Row_Location = 1
End If
End Function

This in worksheet code...

Private Sub Worksheet_Calculate()
If [sheet1!C1] = 1 Then
Sheets("sheet1").Range("A5:D15").Select
MsgBox "HOWDY"
End If
End Sub

To operate change the B1 value and TaDa the sheet is selected based upon the output of the function. I think that it can be adapted to fit your needs if I understand them. Quite remarkable how you can run a macro from a function that will give U a HOWDY but won't select the cells that U have specified or change any of the worksheet values. Anyways, give it a trial. I'm gonna trial fixing it up a bit. Dave

 

[mike1234]

Hi,

I was able to solve the issue by taking another approach. While VBA did not allow me to select cells, it does allow me to select a chart object. Therefore, I modified the chart object to show the information I wanted to display by adding some text boxes to the chart object that are formula driven. I was also able to paste the picture into a comment box instead of a user form. This created a better experience for the user.

For those wondering, here is the solution I used:

The following formula is in the cell with where the sparkline is shown. When the mouse is passed over the cell, it calls the function

=IFERROR(HYPERLINK(Row_Location(ROW(), ADDRESS(ROW(),COLUMN())),""),"")​

The function captures the row and cell address where the mouse is hovering and save them to the spreadsheet. The code is saved in a normal module.

Public Function Row_Location(Data_Row As Integer, Mouse_Location As String)
'This captures the row number and address of the cell when the mouse is passed over the cell with this function. It writes it to spreadsheet to be picked up by another macro.
If ActiveSheet.Range("CZ59") <> Data_Row Then

Application.EnableEvents = False
'this captures the cell where the mouse is currently hovering
ActiveSheet.Range("CZ60") = Mouse_Location
Application.EnableEvents = True

'this captures the row where the mouse is currently hovering
ActiveSheet.Range("CZ59") = "=" & Data_Row
End If
End Function


​

Saving the values to the spreadsheet from the function updates a regular graph in the spreadsheet (i.e. a normal chart object, not the sparkline) and triggers the Worksheet_Change which kicks off the macro below. The code only runs when target cell has been changed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("CZ59"), Range(Target.Address)) _
Is Nothing Then
'This clears out any previous comments
Range("F7:N57").ClearComments

'This captures the image from the chart object
Set MyChart = ActiveSheet.ChartObjects("Zoom_Chart").Chart
Fname = ThisWorkbook.Path & "\temp_image3.jpg"
MyChart.Export Filename:=Fname, Filtername:="JPG"

'This picks up the previously saved location where the mouse is hovering
Mouse_Location2 = ActiveSheet.Range("CZ60").Value

'This creates a comment box in the cell where the mouse is hovering and adds the image to the comment box
Set rng = ActiveSheet.Range(Mouse_Location2)
rng.AddComment (" ")
rng.Comment.Shape.Height = 325
rng.Comment.Shape.Width = 650

rng.Comment.Shape.Fill.UserPicture ThisWorkbook.Path & "\temp_image3.jpg"
End If
End Sub​

The code above takes a picture of the chart object and saves it. It then adds a comment to the cell where mouse is hovering and imports the picture into the comment box. When the user moves the mouse off the cell, the comment box automatically hides. Each time the code is triggered it wipes out the prior comment boxes.

 

[NdNoviceHlp]

Glad U figured it out. Thanks for posting your outcome. Dave