Sendkeys alternative to help with XL Extras - Jim Cone

[Mike K]

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

 

[Mike K]

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

 

[Mike K]

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.

 

[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" <[email protected]>
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

 

[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" <[email protected]>
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" <[email protected]>
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

 

[Gary''s Student]

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??