Jump to content
Sign in to follow this  

COM method to create an Excel macro?

Recommended Posts


I have discovered that you can not create a cell reference that links to a Graph worksheet using _ExcelHyperlinkInsert.

There is a workaround though:

Private Sub Worksheet_Selectionchange(ByVal Target As Range)
  If Not Intersect(Target, Range("A29")) Is Nothing Then
  End If
End Sub

This little macro activates the graph worksheet whenever the cursor is placed within a specified cell (A29 in this example).

Right now, I know of no way to create this macro in the background. I have to open the VB editor and paste in the macro:

Func createGraphLink()
      ;Create placeholder for 'Graph Link' cell
    $array = _ExcelSheetUsedRangeGet($oExcel, "Statistics")
    _ExcelFontSetColor($oExcel, 1, $array[3] + 1, 1, $array[3] + 1, 5)
    _ExcelWriteCell($oExcel, "Goto the graph...", $array[3] + 1, 1)
    _ExcelFontSetProperties($oExcel, $array[3] + 1, 1, $array[3] + 1, 1, True, False, True);make the graph link bold and underlines
    _ExcelFontSetColor($oExcel, $array[3] + 1, 1, $array[3] + 1, 1, 41)

       ; show workbook, open VB editor, and manually paste in code
    WinActivate("Microsoft Excel - " & $oExcel.ActiveWorkbook.name)
    Send("!{F11}") ; open up script editor
    Sleep(500) ; wait for editor to finish opening
    Send("{F7}") ; opens up a new sheet
    $array = _ExcelSheetUsedRangeGet($oExcel, "Statistics")
    ClipPut("Private Sub Worksheet_Selectionchange(ByVal Target As Range)" & @CR & 'If Not Intersect(Target, Range("A' & $array[3] & '")) Is Nothing Then' & @CR & 'Charts("Graph").Activate' & @CR & "End If" & @CR & "End Sub")
    Send("^v") ; paste the code
    Send("!q") ; quit editor
EndFunc  ;==>createGraphLink

This is such a poor hack though, and is susceptible to breakage if the user clicks or types something during that critical window. Is there a way to automate this with COM?

recordMacro() maybe?


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this