Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sendkeys alternative to help with XL Extras - Jim Cone
Oh Wise Ones,
I am using Jim Cones XL Extras add-in which does a wonderful job of indexing all the worksheets of a workbook and creates a table of contents with hyperlinks. What I would like is to be able execute the index routine when a new sheet is added either automatically (1st choice) or with a button (2nd Choice). I need the HR rep to be able to do this the easiest possible way. She will be periodically adding sheets, so the index will occasionally need updating. The table index is not available to be added to a custom menu nor is it in a macro that I can access. The only option I see as an vba rookie is to have a sendkeys macro that calls alt,right arrow 4X(to the insert menu) then down arrow 5X. What can I do? Thanks, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sendkeys alternative to help with XL Extras - Jim Cone
I tried that. The created macro was empty. Also, if I wanted to attach a
button, wouldn't that require me to put in in a module? Then I would need to focus on the worksheet "table of contents" before the sendkey events. Sorry for my vba prowess, but I am a quality technician improving some excel sheets in my not-so-spare time. Thanks, Mike "Gary''s Student" wrote: I see.... First trying to turn on the Recorder and then call Cone's stuff from the menu bar. It may capture the correct syntax for you You can add more Application.SendKeys "{DOWN}" to get you to the proper place, to do the mouse click will require a differenct API: http://scriptorium.serve-it.nl/view.php?sid=44 -- Gary''s Student gsnu200712 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sendkeys alternative to help with XL Extras - Jim Cone
Thanks Gary_S,
Jim has an add-in called "XL Extras" that adds an extra item to the Insert menu called "Table of Contents". This executes the hidden code to get the list of all worksheets in a workbook. Then creates, lists them on a sheet called "Table of Contents. The 5th option down on the insert menu is "Table of Contents" but it doesn't show up on the customize menu. So I could only think of sendkeys to get me there. I didn't know the syntax for the arrow keys. "Gary''s Student" wrote: I am a bit confused. The following: Sub mike_k() Application.SendKeys "%{f}" Application.SendKeys "{RIGHT}" Application.SendKeys "{RIGHT}" Application.SendKeys "{RIGHT}" Application.SendKeys "{DOWN}" Application.SendKeys "{DOWN}" Application.SendKeys "{DOWN}" DoEvents End Sub will get the user ready to insert a new worksheet, but you need to click the mouse. You can't click with SendKeys. Why not just code a routine that will 1. perform the Sheets.Add 2. call the indexer? How are you activating Cone's routine now?? -- Gary''s Student gsnu200712 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sendkeys alternative to help with XL Extras - Jim Cone
Mike,
Thanks for the kind comments. However, I don't understand the new problem. The add-in creates a menu item on the Insert menu called "Table of Contents". Why not just install the add-in on the HR reps computer? When the menu item is clicked it adds the TOC or, if one already exists, asks the user if they want to replace the existing one. The program can also be activated by the keyboard with Alt + I, T and Y (if necessary). You could use send keys to do the same - but why? Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Mike K" wrote in message Oh Wise Ones, I am using Jim Cones XL Extras add-in which does a wonderful job of indexing all the worksheets of a workbook and creates a table of contents with hyperlinks. What I would like is to be able execute the index routine when a new sheet is added either automatically (1st choice) or with a button (2nd Choice). I need the HR rep to be able to do this the easiest possible way. She will be periodically adding sheets, so the index will occasionally need updating. The table index is not available to be added to a custom menu nor is it in a macro that I can access. The only option I see as an vba rookie is to have a sendkeys macro that calls alt,right arrow 4X(to the insert menu) then down arrow 5X. What can I do? Thanks, Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sendkeys alternative to help with XL Extras - Jim Cone
Mike,
Further... With the "Customize" window open and with the Ctrl key pressed, you can drag the "Table of Contents" menu item to any menu or toolbar. A copy of the menu item is created in the new location. Note that any copied menu item is not recognized by the add-in during uninstall and has to be manually deleted. Jim Cone "Jim Cone" wrote in message Mike, Thanks for the kind comments. However, I don't understand the new problem. The add-in creates a menu item on the Insert menu called "Table of Contents". Why not just install the add-in on the HR reps computer? When the menu item is clicked it adds the TOC or, if one already exists, asks the user if they want to replace the existing one. The program can also be activated by the keyboard with Alt + I, T and Y (if necessary). You could use send keys to do the same - but why? Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Mike K" wrote in message Oh Wise Ones, I am using Jim Cones XL Extras add-in which does a wonderful job of indexing all the worksheets of a workbook and creates a table of contents with hyperlinks. What I would like is to be able execute the index routine when a new sheet is added either automatically (1st choice) or with a button (2nd Choice). I need the HR rep to be able to do this the easiest possible way. She will be periodically adding sheets, so the index will occasionally need updating. The table index is not available to be added to a custom menu nor is it in a macro that I can access. The only option I see as an vba rookie is to have a sendkeys macro that calls alt,right arrow 4X(to the insert menu) then down arrow 5X. What can I do? Thanks, Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sendkeys alternative to help with XL Extras - Jim Cone
I am a bit confused. The following:
Sub mike_k() Application.SendKeys "%{f}" Application.SendKeys "{RIGHT}" Application.SendKeys "{RIGHT}" Application.SendKeys "{RIGHT}" Application.SendKeys "{DOWN}" Application.SendKeys "{DOWN}" Application.SendKeys "{DOWN}" DoEvents End Sub will get the user ready to insert a new worksheet, but you need to click the mouse. You can't click with SendKeys. Why not just code a routine that will 1. perform the Sheets.Add 2. call the indexer? How are you activating Cone's routine now?? -- Gary''s Student gsnu200712 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if function with extras. | Excel Worksheet Functions | |||
Sendkeys alternative to help with XL Extras - Jim Cone | Excel Programming | |||
Spacebar extras removal | Excel Worksheet Functions | |||
Jim Cone | Excel Programming | |||
Alternative to SendKeys | Excel Programming |