SRAutomater Posted October 24, 2008 Share Posted October 24, 2008 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 Charts("Graph").Activate 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 _ExcelShow($oExcel) 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 Sleep(1000) 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? Thanks Link to comment Share on other sites More sharing options...
ResNullius Posted October 25, 2008 Share Posted October 25, 2008 http://www.autoitscript.com/forum/index.ph...st&p=595675 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now