Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Linking .hlp help file to Function Wizard
Hi Coders
I have written a series of vba functions and I want to provide context-sensitive help for each of these functions from the help file hyperlink on the function wizard. Using HelpScribble (www.helpscribble.com) I was able to put together a .hlp help file and load the right help file page for each individual function. (I used a .hlp file rather than the newer .chm files because the .chm would never load properly). My problem is that with some earlier versions of Excel (well Excel 2000 9.0) I can only find the .hlp file if I manually unlock the VBA password protection, go to the VBA Project's properties and click on the ... button to direct Excel to the .hlp file. I have tried saving the .hlp file in many different folders (windows, office, library etc) but have not found a folder that Excel defaults to when looking for help files. I have tried entering an absolute path in the Help File field but that does not work either. Is there a way to do this programmatically through VBA (ie tell the VBA project where the help file is)? I am able to find the path to the help files using SHGetFolderPath (Shell32) and can call a particular help topic with WinHelp (user32), but neither of those API calls are useful if I can't detect from the VBA project the click event that occurs when the function wizard hyperlink is clicked. Any thoughts or suggestions would be much appreciated. Regards Jeremy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Linking .hlp help file to Function Wizard
Try something like this
Application.MacroOptions Macro:="CalcSeqCountRef", Description:="Calculation sequence counter for the referenced cell", Category:=9, HelpFile:=ThisWorkbook.Path & "\FastExcelUser2.hlp", HelpContextID:=309 If that does not work there is also a much more complex and troublesome method using XLM macros & hidden namespaces etc (I have just spent 4 days getting it to work....) regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Jsadleir" wrote in message ... Hi Coders I have written a series of vba functions and I want to provide context-sensitive help for each of these functions from the help file hyperlink on the function wizard. Using HelpScribble (www.helpscribble.com) I was able to put together a .hlp help file and load the right help file page for each individual function. (I used a .hlp file rather than the newer .chm files because the .chm would never load properly). My problem is that with some earlier versions of Excel (well Excel 2000 9.0) I can only find the .hlp file if I manually unlock the VBA password protection, go to the VBA Project's properties and click on the ... button to direct Excel to the .hlp file. I have tried saving the .hlp file in many different folders (windows, office, library etc) but have not found a folder that Excel defaults to when looking for help files. I have tried entering an absolute path in the Help File field but that does not work either. Is there a way to do this programmatically through VBA (ie tell the VBA project where the help file is)? I am able to find the path to the help files using SHGetFolderPath (Shell32) and can call a particular help topic with WinHelp (user32), but neither of those API calls are useful if I can't detect from the VBA project the click event that occurs when the function wizard hyperlink is clicked. Any thoughts or suggestions would be much appreciated. Regards Jeremy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Linking .hlp help file to Function Wizard
Thanks Charles - Unfortunately I was already using Macrooptions in that way
and it works perfectly to get each function's description into the function wizard, but for some reason the Help File is never found even with the path approach you are suggesting unless I do it manually. I'm a great fan of MacroOptions although I wish it also had the option to include the argument level descriptions that xlfRegister has. Thanks for the suggestion Jeremy "Charles Williams" wrote: Try something like this Application.MacroOptions Macro:="CalcSeqCountRef", Description:="Calculation sequence counter for the referenced cell", Category:=9, HelpFile:=ThisWorkbook.Path & "\FastExcelUser2.hlp", HelpContextID:=309 If that does not work there is also a much more complex and troublesome method using XLM macros & hidden namespaces etc (I have just spent 4 days getting it to work....) regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Jsadleir" wrote in message ... Hi Coders I have written a series of vba functions and I want to provide context-sensitive help for each of these functions from the help file hyperlink on the function wizard. Using HelpScribble (www.helpscribble.com) I was able to put together a .hlp help file and load the right help file page for each individual function. (I used a .hlp file rather than the newer .chm files because the .chm would never load properly). My problem is that with some earlier versions of Excel (well Excel 2000 9.0) I can only find the .hlp file if I manually unlock the VBA password protection, go to the VBA Project's properties and click on the ... button to direct Excel to the .hlp file. I have tried saving the .hlp file in many different folders (windows, office, library etc) but have not found a folder that Excel defaults to when looking for help files. I have tried entering an absolute path in the Help File field but that does not work either. Is there a way to do this programmatically through VBA (ie tell the VBA project where the help file is)? I am able to find the path to the help files using SHGetFolderPath (Shell32) and can call a particular help topic with WinHelp (user32), but neither of those API calls are useful if I can't detect from the VBA project the click event that occurs when the function wizard hyperlink is clicked. Any thoughts or suggestions would be much appreciated. Regards Jeremy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Linking .hlp help file to Function Wizard
Well you could add this sort of thing to the appropriate open event
ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path & "\FastExcelUser2.hlp" but it wont work if the project is protected, and you need Trust Access to VBProjects ... otherwise its go for the mysteries of the REGISTER and UNREGISTER command etc Start with "Professional Excel development" by Bullen and Bovey (If you go that route when you get to the help argument you will notice that there isnt a contextid argument. Do not despair just put \FastExcelUser2.hlp!309 ) If you want to beat the 256 character limit there is an interesting post from KeepitCool on Dicks Kusleikas blog about using the hidden namespace which can be made to work in 97 through 2003 (for some reason I get duplicate entries in the 97 Function wizard). But be warned its not for the faint-hearted. regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Jsadleir" wrote in message ... Thanks Charles - Unfortunately I was already using Macrooptions in that way and it works perfectly to get each function's description into the function wizard, but for some reason the Help File is never found even with the path approach you are suggesting unless I do it manually. I'm a great fan of MacroOptions although I wish it also had the option to include the argument level descriptions that xlfRegister has. Thanks for the suggestion Jeremy "Charles Williams" wrote: Try something like this Application.MacroOptions Macro:="CalcSeqCountRef", Description:="Calculation sequence counter for the referenced cell", Category:=9, HelpFile:=ThisWorkbook.Path & "\FastExcelUser2.hlp", HelpContextID:=309 If that does not work there is also a much more complex and troublesome method using XLM macros & hidden namespaces etc (I have just spent 4 days getting it to work....) regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Jsadleir" wrote in message ... Hi Coders I have written a series of vba functions and I want to provide context-sensitive help for each of these functions from the help file hyperlink on the function wizard. Using HelpScribble (www.helpscribble.com) I was able to put together a .hlp help file and load the right help file page for each individual function. (I used a .hlp file rather than the newer .chm files because the .chm would never load properly). My problem is that with some earlier versions of Excel (well Excel 2000 9.0) I can only find the .hlp file if I manually unlock the VBA password protection, go to the VBA Project's properties and click on the ... button to direct Excel to the .hlp file. I have tried saving the .hlp file in many different folders (windows, office, library etc) but have not found a folder that Excel defaults to when looking for help files. I have tried entering an absolute path in the Help File field but that does not work either. Is there a way to do this programmatically through VBA (ie tell the VBA project where the help file is)? I am able to find the path to the help files using SHGetFolderPath (Shell32) and can call a particular help topic with WinHelp (user32), but neither of those API calls are useful if I can't detect from the VBA project the click event that occurs when the function wizard hyperlink is clicked. Any thoughts or suggestions would be much appreciated. Regards Jeremy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Linking .hlp help file to Function Wizard
Many Thanks Charles - I'll look into all those suggestions - I know only too
well how simple problems like these can quickly rise with the ballooning complexity of a mushroom cloud, until days later with more and more code sprouting in all directions, and no nearer to resolution, you find yourself on a rainy beach at midnight and suddenly realise that if the users want help they can just open the help file themselves. Regards Jeremy "Charles Williams" wrote: Well you could add this sort of thing to the appropriate open event ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path & "\FastExcelUser2.hlp" but it wont work if the project is protected, and you need Trust Access to VBProjects ... otherwise its go for the mysteries of the REGISTER and UNREGISTER command etc Start with "Professional Excel development" by Bullen and Bovey (If you go that route when you get to the help argument you will notice that there isnt a contextid argument. Do not despair just put \FastExcelUser2.hlp!309 ) If you want to beat the 256 character limit there is an interesting post from KeepitCool on Dicks Kusleikas blog about using the hidden namespace which can be made to work in 97 through 2003 (for some reason I get duplicate entries in the 97 Function wizard). But be warned its not for the faint-hearted. regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Jsadleir" wrote in message ... Thanks Charles - Unfortunately I was already using Macrooptions in that way and it works perfectly to get each function's description into the function wizard, but for some reason the Help File is never found even with the path approach you are suggesting unless I do it manually. I'm a great fan of MacroOptions although I wish it also had the option to include the argument level descriptions that xlfRegister has. Thanks for the suggestion Jeremy "Charles Williams" wrote: Try something like this Application.MacroOptions Macro:="CalcSeqCountRef", Description:="Calculation sequence counter for the referenced cell", Category:=9, HelpFile:=ThisWorkbook.Path & "\FastExcelUser2.hlp", HelpContextID:=309 If that does not work there is also a much more complex and troublesome method using XLM macros & hidden namespaces etc (I have just spent 4 days getting it to work....) regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Jsadleir" wrote in message ... Hi Coders I have written a series of vba functions and I want to provide context-sensitive help for each of these functions from the help file hyperlink on the function wizard. Using HelpScribble (www.helpscribble.com) I was able to put together a .hlp help file and load the right help file page for each individual function. (I used a .hlp file rather than the newer .chm files because the .chm would never load properly). My problem is that with some earlier versions of Excel (well Excel 2000 9.0) I can only find the .hlp file if I manually unlock the VBA password protection, go to the VBA Project's properties and click on the ... button to direct Excel to the .hlp file. I have tried saving the .hlp file in many different folders (windows, office, library etc) but have not found a folder that Excel defaults to when looking for help files. I have tried entering an absolute path in the Help File field but that does not work either. Is there a way to do this programmatically through VBA (ie tell the VBA project where the help file is)? I am able to find the path to the help files using SHGetFolderPath (Shell32) and can call a particular help topic with WinHelp (user32), but neither of those API calls are useful if I can't detect from the VBA project the click event that occurs when the function wizard hyperlink is clicked. Any thoughts or suggestions would be much appreciated. Regards Jeremy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Linking .hlp help file to Function Wizard
Charles,
Recent post from JKP of Daily Dose set me thinking too. I've just mailed you my code. (Crashproof,no xlmsheet, unloading/reloading) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Charles Williams wrote in Try something like this Application.MacroOptions Macro:="CalcSeqCountRef", Description:="Calculation sequence counter for the referenced cell", Category:=9, HelpFile:=ThisWorkbook.Path & "\FastExcelUser2.hlp", HelpContextID:=309 If that does not work there is also a much more complex and troublesome method using XLM macros & hidden namespaces etc (I have just spent 4 days getting it to work....) regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Jsadleir" wrote in message ... Hi Coders I have written a series of vba functions and I want to provide context-sensitive help for each of these functions from the help file hyperlink on the function wizard. Using HelpScribble (www.helpscribble.com) I was able to put together a .hlp help file and load the right help file page for each individual function. (I used a .hlp file rather than the newer .chm files because the .chm would never load properly). My problem is that with some earlier versions of Excel (well Excel 2000 9.0) I can only find the .hlp file if I manually unlock the VBA password protection, go to the VBA Project's properties and click on the ... button to direct Excel to the .hlp file. I have tried saving the .hlp file in many different folders (windows, office, library etc) but have not found a folder that Excel defaults to when looking for help files. I have tried entering an absolute path in the Help File field but that does not work either. Is there a way to do this programmatically through VBA (ie tell the VBA project where the help file is)? I am able to find the path to the help files using SHGetFolderPath (Shell32) and can call a particular help topic with WinHelp (user32), but neither of those API calls are useful if I can't detect from the VBA project the click event that occurs when the function wizard hyperlink is clicked. Any thoughts or suggestions would be much appreciated. Regards Jeremy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using excel wizard for file download | Excel Discussion (Misc queries) | |||
function wizard excel 2000 not calculating in worksheet | Excel Worksheet Functions | |||
No Excel Function Wizard on Mac-Office 2004? | Excel Worksheet Functions | |||
Argument limit in Excel Function Wizard | Excel Programming | |||
Excel function wizard categories | Excel Programming |