Getting chm help file to open from menu

[andrewjs]

I have seen numerous examples on this and from what I can tell I am doing this right, but it is not working. I have a module which contains this code:

(Excel VBA)

Sub OpenOMHelp()
App.HelpFile = App.Path & "..\OMToolsHelp.chm"
' (i tried other ways of calling the path; doesn't seem to be path-related
End Sub

Another module which creates a menu for which the relevant part for this Help item is:

With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Help for OM &Tools"
.OnAction = "OpenOMHelp"
.Enabled = True
.BeginGroup = True
End With
End With

My other menu item open fine, but they are merely opening other Excel spreadhseets. But the menu displays the "Help for OM Tools" option but it is not clickable (and has a checkmark next to it).

Surely it can't be too hard to open a chm file from a menu? Can anyone point out the flaw here? I'd really appreciate it!

Thanks much,
Andrew

[Bob Phillips]

Are you trying to set a help file for the whole application, or just point that one control to a helpfile page?

[andrewjs]

I have one general Help file that I want opened when a user clicks on an item in a menu.
But the option in the menu does nothing, and in fact it displays in the menu with a checkmark next to it (as if it were "selected" or "turned on").

I've tried a few path syntaxes in the App.File call, not working; I am wondering if it is the code for the menu that isn't working.

Thanks for the help.

[Bob Phillips]

I think that you want



With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Help for OM &Tools"
.HelpFile = Application.Path & "\OMToolsHelp.chm"
.HelpContextId = 50
.Enabled = True
.BeginGroup = True
End With
End With

[andrewjs]

Thanks, I'll give that a try later today when I have a chance. All the documentation I'd read on other sites said that the only way to call a help file was to use the API, which just seems hard to believe. I'll give your code a try and report back here, thanks!

[Bob Phillips]

It is if you want to call help from within your app, but commandbar controls have a facility to link into it.

[andrewjs]

I think that you want



With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Help for OM &Tools"
.HelpFile = Application.Path & "\OMToolsHelp.chm"
.HelpContextId = 50
.Enabled = True
.BeginGroup = True
End With
End With


I really appreciate the help. I have a couple questions. It is still not working. When I select the option from the menu, instead of loading the help document, i get a compiler error which says "Variable not defined" and it dumps me into this section, highlighting the word App:

Sub OpenOMHelp()
GetOMToolsPath
App.HelpFile = OMToolsAddinPath & "..\OMToolsHelp.chm"
End Sub

I'm struggling to put together a quick VBA knowledge after years of programming in PHP. Tougher than it looks!

My second question is that the OpenOMHelp subroutine above is not actually mentioned anywhere in the menu code, so how is it linked to the menu? :

With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Help For OM &Tools"
.HelpFile = Application.Path & "\OMToolsHelp.chm"
.HelpContextID = 50
.Enabled = True
.BeginGroup = True
End With
End With

Is it just through the Helpfile mention?

[Bob Phillips]

That is because you haven't created and loaded a variable called App. If you notice, I used Application.

If you are referring to the hosting application, Excel probably, that is Application not App.

The routine you mention is not used, it just links a helpfile to an application, you want to opne it on demand.

[andrewjs]

Ah very good. I'm debugging and editing someone else's code so workign through it is a rough. I replaced the Apps with Application and those errors went away... in favor of a new one: it highlights the entire line and says

Application.HelpFile = Application.Path & "..\OMToolsHelp.chm"

Object does not support this property or method.

I am starting to pull my hair out but since I don't have any, I'm forced to look at other outlets.

[Bob Phillips]

I don't know why you are bothering with that routine, it doesn't do anything for you.

Anyway, the double dot is giving you grief



Application.HelpFile = Application.Path & "\OMToolsHelp.chm"


but as I said, it isn't any use to what you have described.

[andrewjs]

I don't know why you are bothering with that routine, it doesn't do anything for you.

Anyway, the double dot is giving you grief



Application.HelpFile = Application.Path & "\OMToolsHelp.chm"

but as I said, it isn't any use to what you have described.

I commented out the subroutine, but when I clicked the entry, i got an error that said it couldn't find the subroutine, so somehow that subroutine is relevant, i just can't see where in the code it actually gets used; strange.

i also removed the dots (and have tried other paths as well, like using + instead of &), and that didn't solve the "Object does not support this property or method" error. :(

i guess when this code was originally written it was organized in some way that i can't understand with my limited VBA background. these object-oriented models confused me.

[Bob Phillips]

Post the workbook and let's take a look.

[andrewjs]

Ok i put it here:

http://skylark.suso.org/vbaproject/

It doesn't contain the extra files for its added functionality, but I did include the in-progress help file in question.

Currently when clicking on the Help option, it now does nothing at all.

Thanks again for the assistance on this!

[andrewjs]

The code in question is in the modules "codeforall" and "main menu" -- i forgot to mention that.

[Bob Phillips]

There appears to be no downlaod option on that. Can't you zip it and upload to here?

[andrewjs]

There appears to be no downlaod option on that. Can't you zip it and upload to here?
Oh sorry - merely right-clicking on the files allows you to save them to your computer. The zip file is too large to post here (this site won't let me).

I have now uploaded a zip to that directory as well. Right-click to download.

[Bob Phillips]

Sorry, I'm not going to be able to help. My virus checker won't let me anywhere near it, saying it has a virus in the xla.

[andrewjs]

Wow Ok. I haven't seen other virus checkers have a problem. I'll post the entire code for the two modules instead.

[andrewjs]

Ok I have a module called "codeforall" which contains this in its entirety; the bit about the help menu is near the end, which I've commented out and now poses no problem (yesterday when I commented it out, clicking on the Help option in the menu caused it to fail when it tired to find the subroutine; i was running Excel 2000 and now I'm using Excel 2003 today, but both versions of VB are roughly the same, v. 6 vs v. 6.3):


Option Explicit
Public DefaultWorkbookName, ToolSheetName, ToolWorkbookName, InputCode
Public SheetLocatedinDefault, NewSheetUserName, ProblemName
Public OMToolsAddinPath As String
Public oOMToolsAddin As Excel.Workbook
Public LabelFirstTextBox, LabelSecondTextBox, LabelThirdTextBox As String
Public ValueFirstTextbox, ValueSecondTextbox, ValuethirdTextbox, ValueMaxMin, ValueBackorder As Integer
Public LastColumn, FirstRow, NewDisplayNameColumn As Integer
Public TempSheetName, NewProblemName, wSheet
Public wbDefaultWorkbookName As Workbook
Public wsProblemName As Worksheet
Public Kellie, TestName, TestName2, TestRefCount, msg

'These Subs are called from the different workbooks and open up the appropriate worksheets
'The appropriate variables for workbook and sheetname are assigned and then the First and Second Step
'procedures are run
Sub RunningFirstStep()
'Captures the path that the Addin is located in - this is done also in modMain
GetOMToolsPath
CheckDefaultWorkbook
OpenPrefsForm
End Sub
Sub ModifyDefaultSetup()
Select Case ToolSheetName
'Decision Analysis
Case "Uncertainty"
ChangeNoDecAltStatesNat
Case "Expected Value"
ChangeNoDecAltStatesNatMaxMin
'Quality Control
Case "p-chart"
'Value of Column that ends the Columns to be copied
LastColumn = 6
ChangeNoSamplesType1
Case "c-chart"
LastColumn = 5
ChangeNoSamplesType1
Case "Xbar & R chart"
ChangeNoSamplesType1
'Process Planning
Case "Point of Indifference"
ChangeNoProcesses
'Facility Layout
Case "Process Layout"
ModifyDepartments
Case "Product Layout"
ModifyWorkElements
'Facility Location
Case "Location Rating Factor"
ChangeNoSitesFactors
Case "Center-of-Gravity"
ChangeNoSites
Case "Load-Distance"
ChangeSiteDestination
'Work Measurement
Case "Stopwatch Time Study"
ModifyWorkElementsCycles
Case "Work Sampling"
ModifyWorkSampObservations
'Project Management
Case "Project Management"
ChangeNoActivities
'Supply Chain
Case "Transportation Model"
ModifySourceDestination
RunSolverReferenceCode
Case "Transshipment Model"
ModifySourceInterFinal
RunSolverReferenceCode
'Forecasting
Case "Moving Average"
ModifyMAPeriods
Case "Exponential Smoothing"
ModfiyESPeriods
Case "Adj. Exponential Smoothing"
ModifyAESPeriods
Case "Seasonal Factors"
ModifyPeriodsSeasons
Case "Linear Regression"
ModifyLRPeriods
Case "Multiple Regression"
ModifyMRPeriods
'Inventory
Case "Quantity Discounts"
ChangeNoPriceBreaks
Case "Quantity Discounts w VCC"
ChangeNoPriceBreaks
Case "ABC Classification"
ChangeNoItems
'Sales & Operations Planning
Case "SOP Strategies"
ModifySOPStratPeriods
Case "SOP Strategies w Backorder"
ModifySOPStratBOPeriods
Case "SOP with LP"
ModifySOPLPPeriods
RunSolverReferenceCode
' Workbooks(DefaultWorkbookName).Worksheets(ProblemName).SOPLPResetFinalInven tory
Case "SOP LP w Backorder"
ModifySOPLPBOPeriods
RunSolverReferenceCode
'Linear Programming
Case "Linear Programming"
ModifyConstraintsVariables
RunSolverReferenceCode
'MRP
Case "MRP Matrix"
ModifyPeriodsItems
Case "MRP Lotsizing"
ModifyMRPNoPeriods
'Scheduling
Case "Assignment Method"
ModifyTasksWorkers
RunSolverReferenceCode
Case "Sequencing Rules"
ModifyJobs
Case "Johnson's Rule"
ModifyJobsJohnsons
'Waiting Lines
Case "Single, Finite Population"
ChangePopulationSize
End Select

End Sub
Sub RunSolverReferenceCode()
On Error GoTo SolverAddinopen_error
'AddIns("Solver Add-in").Installed = True
On Error GoTo OtherInstallSolver_error
'This looks to see if SOLVER is an addin - if so AddIns(i).Name is SOLVER
i = 1
Do Until (i = AddIns.Count) Or (Upcase(AddIns(i).Name) = "SOLVER.XLA")
i = i + 1
Loop
'If Solver is installed as an addin - then make sure the Reference is also there
If (Upcase(AddIns(i).Name) = "SOLVER.XLA") Then
AddIns(i).Installed = True
j = 1
With Application.Workbooks(DefaultWorkbookName).VBProject
Do Until (j = .References.Count) Or _
(Upcase(.References(j).Name) = "SOLVER")
j = j + 1
Loop
If Application.VBE.Version >= 5 Then
Application.SendKeys "{TAB}{TAB}{ENTER}"
End If

If (Upcase(.References(j).Name) <> "SOLVER") Then
.References.AddFromFile AddIns(i).FullName
End If
End With
Else
MsgBox "The OM Tools add-in could not find Solver.", vbCritical
End If
On Error GoTo 0

Exit Sub
SolverAddinopen_error:
msg = "To use the OM Tools add-in" & vbCrLf
msg = msg & "you must have the Solver Add-in Installed" & vbCrLf
msg = msg & "You need to install the Solver Add-in from your" & vbCrLf & vbCrLf
msg = msg & "Excel Installation CD." & vbCrLf
msg = msg & "Please make this change and try again."
MsgBox msg, vbCritical, "OM Tools Instructions..."
On Error GoTo 0
Exit Sub
OtherInstallSolver_error:
msg = "To use the OM Tools add-in" & vbCrLf
msg = msg & "you must change your macro security level" & vbCrLf
msg = msg & "to allow access to Visual Basic projects." & vbCrLf & vbCrLf
msg = msg & "To do this:" & vbCrLf
msg = msg & " 1) Click Tools, Macro, Secuity" & vbCrLf
msg = msg & " 2) Select the ""Trusted Sources"" tab" & vbCrLf
msg = msg & " 3) Select ""trust access to Visual Basic projects"" " & vbCrLf & vbCrLf
msg = msg & " The Help File has additional information specific to each Excel version" & vbCrLf & vbCrLf
msg = msg & "Please make this change and try again."
MsgBox msg, vbCritical, "OM Tools XP Instructions..."
On Error GoTo 0
Exit Sub
End Sub
Sub RunningNextStep()

Application.DisplayAlerts = False

'Checks to see if new Workbook needs to be added
If SheetLocatedinDefault = False Then
Workbooks.Add
DefaultWorkbookName = ActiveWorkbook.Name
End If
'Open Correct File with OM Tool
Workbooks.Open Filename:=OMToolsAddinPath & "\System Files\" & ToolWorkbookName

'For Aggregate planning - choose with backorders or not sheet
If ToolWorkbookName = "System_SOP.xls" Then
'only have to worry if the backorder option is selected
If ValueBackorder = -1 Then
Select Case ToolSheetName
Case "SOP Strategies"
Sheets("SOP Strategies w Backorder").Select
ToolSheetName = "SOP Strategies w Backorder"
Case "SOP with LP"
Sheets("SOP LP w Backorder").Select
ToolSheetName = "SOP LP w Backorder"
Case "SOP with Transportation Method"
Sheets("SOP Transportation Method w Backorder").Select
ToolSheetName = "SOP Transportation Method w Backorder"
End Select
Else
Sheets(ToolSheetName).Select
End If
Else
If ToolSheetName = "Quantity Discounts" Then
'only have to worry if FixedCarry Cost is not checked
If ValueFixedCarry = False Then
ToolSheetName = "Quantity Discounts w VCC"
End If
Sheets(ToolSheetName).Select
Else
Sheets(ToolSheetName).Select
End If
End If
Sheets(ToolSheetName).Copy Before:=Workbooks(DefaultWorkbookName).Sheets(1)
'If Created new Workbook - delete the default Sheet1 in that Workbook
If SheetLocatedinDefault = False Then
Application.DisplayAlerts = False 'turn off Delete OK Error Message
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
End If
'Close OM Tool Workbook
Workbooks(ToolWorkbookName).Activate
Workbooks(ToolWorkbookName).Saved = True
ActiveWorkbook.Close
'Open Workbook with Student Version of OM Tool
Set wbDefaultWorkbookName = Workbooks(DefaultWorkbookName)
wbDefaultWorkbookName.Activate
'Save Sheet with Problem Name
TempSheetName = ActiveWorkbook.ActiveSheet.Name
Do While ProblemName = ""
NewProblemName = InputBox("Enter a valid Problem Name")
ProblemName = NewProblemName
Loop
'Test to see if this ProblemName Already Exists
'Kellie - add error checking if this sheet name already exists - prompt for new
'This isn't complete - if picks name of new sheet or same sheet - will let through
On Error Resume Next

Set wSheet = Sheets(ProblemName)
If wSheet Is Nothing Then 'Doesn't exist
Else 'Does exist
NewProblemName = InputBox("This Problem Name is already the name of a sheet")
ProblemName = NewProblemName
On Error GoTo 0
End If
Sheets(TempSheetName).Name = ProblemName
If NewDisplayNameColumn = 0 Then 'if didn't put name in diff coln than 5
Cells(1, 5) = NewSheetUserName & " - " & ProblemName
Else
Cells(1, NewDisplayNameColumn) = NewSheetUserName & " - " & ProblemName
End If
Set wsProblemName = wbDefaultWorkbookName.Sheets(ProblemName)

ModifyDefaultSetup
CloseProgram
End Sub
Sub CheckDefaultWorkbook()
Dim DefaultWorkbookTest
'this Sub determines if there is a workbook open - and if so captures its name
Set DefaultWorkbookTest = Application.ActiveWorkbook
If DefaultWorkbookTest Is Nothing Then
DefaultWorkbookName = ""
Else
DefaultWorkbookName = ActiveWorkbook.Name
End If
End Sub
Sub GetOMToolsPath()
'Puts the Addin into the Worksheets Object
Set oOMToolsAddin = Workbooks("OMTools.xla")
OMToolsAddinPath = oOMToolsAddin.Path
End Sub

Sub CloseOMMenu()
RemoveMainMenu
GetOMToolsPath
' Application.Workbooks(OMToolsAddinPath & "\OMTools.xla").Close
Application.Workbooks("OMTools.xla").Close
End Sub
'Sub OpenOMHelp()
'GetOMToolsPath
' Application.HelpFile = Application.Path & "..\OMToolsHelp.chm"
'End Sub
Sub OpenPrefsForm()
'Opens OM Prefs Form
frmOMToolsPrefs.StartUpPosition = 2
frmOMToolsPrefs.Show
End Sub



Then I have a module for the main menu that contains this:


Option Explicit
Public i, j, k

Sub MacroInformation()
'Author: Kellie Keeling
'Created: March 6, 2005
'Last Updated: May 24, 2005

End Sub
Sub Auto_Open()
'AddIns("Analysis ToolPak").Installed = True
' AddIns("Analysis ToolPak - VBA").Installed = True
Workbooks.Add
Application.ScreenUpdating = False
AddMainMenu
'Shows opening screen
frmOpening.Show
End Sub
Function Upcase(x As Variant) As String
Upcase = ""
Dim i As Integer, y As String, anum As Integer
For i = 1 To Len(x)
y = Mid(x, i, 1)
anum = Asc(y)
If anum >= 92 And anum <= 122 Then y = Chr$(anum - 32)
Upcase = Upcase & y
Next
End Function
Sub RunSolverCode()
MsgBox ("This Ran")
Dim loaderror
Dim msg As String
On Error GoTo open_error
Application.DisplayAlerts = False

'Make sure Solver is installed
'On Error Resume Next
' Select Case Val(Application.Version)
' Case 9
' ThisWorkbook.VBProject.References.AddFromFile ("C:\Program Files\Microsoft Office\Office\Library\Solver\SOLVER.XLA")
' Case 11
' ThisWorkbook.VBProject.References.AddFromFile ("C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.XLA")
' End Select

AddIns("Solver Add-in").Installed = True

i = 1
Do Until (i = AddIns.Count) Or (Upcase(AddIns(i).Name) = "SOLVER.XLA")
i = i + 1
Loop
If (Upcase(AddIns(i).Name) = "SOLVER.XLA") Then
j = 1
With ThisWorkbook.VBProject
'With Application.VBE.VBProjects("OMTools")
Do Until (j = .References.Count) Or _
(Upcase(.References(j).Name) = "SOLVER.XLA")
j = j + 1
Loop
If Application.VBE.Version >= 5 Then Application.SendKeys "{TAB}{TAB}{ENTER}"
If (Upcase(.References(j).Name) <> "SOLVER.XLA") Then
.References.AddFromFile AddIns(i).FullName
End If

End With
Else
MsgBox "The OM Tools add-in could not find Solver.", vbCritical
End If
Exit Sub
open_error:
Select Case Err.Number
Case 1004
msg = "To use the OM Tools add-in" & vbCrLf
msg = msg & "you must change your macro security level" & vbCrLf
msg = msg & "to allow access to Visual Basic projects." & vbCrLf & vbCrLf
msg = msg & "To do this:" & vbCrLf
msg = msg & " 1) Click Tools, Macro, Secuity" & vbCrLf
msg = msg & " 2) Select the ""Trusted Sources"" tab" & vbCrLf
msg = msg & " 3) Select ""trust access to Visual Basic projects"" " & vbCrLf & vbCrLf
msg = msg & "Please make this change and try again."
MsgBox msg, vbCritical, "OM Tools XP Instructions..."
loaderror = True
ThisWorkbook.Close
Case 32813 ' Name conflicts with existing module, project, or object library
' MsgBox Err.Number & ": " & Err.Description
Resume Next
Case Else
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error Opening OM Tools..."
End Select
End
End Sub
Sub Auto_Close()
RemoveMainMenu
End Sub
Sub CloseProgram()
Application.ScreenUpdating = True
End
End Sub

Sub AddMainMenu()
RemoveMainMenu
Dim oOMToolsBar As CommandBar
Dim oOMToolsPopup As CommandBarPopup
Dim oDecisionAnalysisOpt As CommandBarPopup
Dim oProductDesignOpt As CommandBarPopup
Dim oProcessPlanning As CommandBarPopup
Dim oFacilityLayoutOpt As CommandBarPopup
Dim oQualityControlOpt As CommandBarPopup
Dim oProcessPlanningOpt As CommandBarPopup
Dim oFacilityLocationOpt As CommandBarPopup
Dim oWorkMeasurementOpt As CommandBarPopup
Dim oSupplyChainOpt As CommandBarPopup
Dim oForecastingOpt As CommandBarPopup
Dim oInventoryOpt As CommandBarPopup
Dim oSalesOperationsPlanningOpt As CommandBarPopup
Dim oLinearProgrammingOpt As CommandBarPopup
Dim oMRPOpt As CommandBarPopup
Dim oSchedulingOpt As CommandBarPopup
Dim oWaitingLinesOpt As CommandBarPopup
Set oOMToolsBar = CommandBars("Worksheet Menu Bar")
With oOMToolsBar.Controls
Set oOMToolsPopup = .Add(msoControlPopup, , , 9) 'puts new menu in ninth position 'adds new option
With oOMToolsPopup
.TooltipText = "Russell and Taylor OM Tools"
.Caption = "OM Tools"
End With
With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "&Productivity"
.OnAction = "OpenProductivity"
.Enabled = True
End With
End With

Set oDecisionAnalysisOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oDecisionAnalysisOpt
.Caption = "&Decision Analysis"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&Decision Making under Uncertainty"
.OnAction = "OpenDecMaking"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Expected Value"
.OnAction = "OpenExpectedValue"
.Enabled = True
End With
End With

Set oQualityControlOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oQualityControlOpt
.Caption = "&Quality Control"
With .Controls.Add(msoControlButton)
.Caption = "&p-chart"
.OnAction = "Openpchart"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&c-chart"
.OnAction = "Opencchart"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Xbar and R chart"
.OnAction = "Openxbarrchart"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "P&rocess Capability"
.OnAction = "Openprocesscapability"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Acceptance Sampling"
.OnAction = "Openacceptancesampling"
.Enabled = True
End With
End With
Set oProductDesignOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oProductDesignOpt
.Caption = "P&roduct Design"
With .Controls.Add(msoControlButton)
.Caption = "&Reliability"
.OnAction = "Openreliability"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&System Availability"
.OnAction = "OpenSystemAvailability"
.Enabled = True
End With
End With
Set oProcessPlanningOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oProcessPlanningOpt
.Caption = "Pr&ocess Planning"
With .Controls.Add(msoControlButton)
.Caption = "&Break Even Analysis"
.OnAction = "OpenBreakEvenAnalysis"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Point of Indifference"
.OnAction = "OpenPointofIndifference"
.Enabled = True
End With
End With
Set oFacilityLayoutOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oFacilityLayoutOpt
.Caption = "&Facility Layout"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&Process Layout"
.OnAction = "OpenProcessLayout"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "P&roduct Layout"
.OnAction = "OpenProductLayout"
.Enabled = True
End With
End With
Set oFacilityLocationOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oFacilityLocationOpt
.Caption = "F&acility Location"
With .Controls.Add(msoControlButton)
.Caption = "&Location Rating Factor"
.OnAction = "OpenLocationRatingFactor"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Center-of-Gravity"
.OnAction = "OpenCenterofGravity"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "Load-&Distance"
.OnAction = "OpenLoadDistance"
.Enabled = True
End With
End With
Set oWorkMeasurementOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oWorkMeasurementOpt
.Caption = "&Work Measurement"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&Learning Curve"
.OnAction = "OpenLearningCurve"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Stopwatch Time Study"
.OnAction = "OpenTimeStudy"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Work Sampling"
.OnAction = "OpenWorkSampling"
.Enabled = True
End With
End With

With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Pro&ject Management"
.OnAction = "OpenProjectManagement"
.Enabled = True
End With
End With
Set oSupplyChainOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oSupplyChainOpt
.Caption = "&Supply Chain"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&Transportation Model"
.OnAction = "OpenTransportationModel"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "Transshipment &Model"
.OnAction = "OpenTransshipmentModel"
.Enabled = True
End With
End With
Set oForecastingOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oForecastingOpt
.Caption = "Fore&casting"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&Moving Average"
.OnAction = "OpenMovingAverage"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Exponential Smoothing"
.OnAction = "OpenExponentialSmoothing"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Adjusted Exponential Smoothing"
.OnAction = "OpenAdjExponentialSmoothing"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Seasonal Factors"
.OnAction = "OpenSeasonalFactors"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Regression"
.OnAction = "OpenRegression"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "M&ultiple Regression"
.OnAction = "OpenMultipleRegression"
.Enabled = True
End With

End With
Set oInventoryOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oInventoryOpt
.Caption = "&Inventory"
With .Controls.Add(msoControlButton)
.Caption = "&ABC Classification"
.OnAction = "OpenABCClassification"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&EOQ"
.OnAction = "OpenEOQ"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Production Quantity"
.OnAction = "OpenPQ"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Quantity Discounts"
.OnAction = "OpenQuantityDiscounts"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Reorder Point"
.OnAction = "OpenReorderPoint"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Fixed Period Model"
.OnAction = "OpenFixedPeriodModel"
.Enabled = True
End With

End With
Set oSalesOperationsPlanningOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oSalesOperationsPlanningOpt
.Caption = "Sales &Operations Planning"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&SOP Strategies"
.OnAction = "OpenSOPStrategies"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "SOP &Linear Programming"
.OnAction = "OpenSOPLinearProgramming"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "SOP &Transportation Method"
.OnAction = "OpenSOPTransportationMethod"
.Enabled = True
End With
End With
With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "&Linear Programming"
.OnAction = "OpenLinearProgramming"
.Enabled = True
End With
End With
Set oMRPOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oMRPOpt
.Caption = "&MRP"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "MRP &Matrix"
.OnAction = "OpenMRPMatrix"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&MRP &Lotsizing"
.OnAction = "OpenMRPLotsizing"
.Enabled = True
End With
End With
With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "&Kanbans"
.OnAction = "OpenKanbans"
.Enabled = True
.Visible = True
End With
End With
Set oSchedulingOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oSchedulingOpt
.Caption = "Sc&heduling"
.Enabled = True
With .Controls.Add(msoControlButton)
.Caption = "&Assignment Method"
.OnAction = "OpenAssignmentMethod"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Sequencing Rules"
.OnAction = "OpenSequencingRules"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Johnson's Rule"
.OnAction = "OpenJohnsonsRule"
.Enabled = True
End With
End With
Set oWaitingLinesOpt = oOMToolsPopup.Controls.Add(msoControlPopup) 'adds new option
With oWaitingLinesOpt
.Caption = "Waiti&ng Lines"
With .Controls.Add(msoControlButton)
.Caption = "&Single Server"
.OnAction = "OpenSingleServer"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "Single, &Constant Service"
.OnAction = "OpenSingleConstantService"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Single, Finite &Queue"
.OnAction = "OpenSingleFiniteQueue"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Single, Finite &Population"
.OnAction = "OpenSingleFinitePopulation"
.Enabled = True
End With
With .Controls.Add(msoControlButton)
.Caption = "&Multiple Server"
.OnAction = "OpenMultipleServer"
.Enabled = True
End With
End With
With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Help For OM &Tools"
.HelpFile = Application.Path & "\OMToolsHelp.chm"
.HelpContextID = 50
.Enabled = True
.BeginGroup = True
End With
End With
With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = "Remo&ve OM Tools Menu"
.OnAction = "CloseOMMenu"
.Enabled = True
End With
End With


End With
End Sub

Sub RemoveMainMenu()
Dim oCmdCtl As CommandBarControl
For Each oCmdCtl In CommandBars("Worksheet Menu Bar").Controls
If oCmdCtl.Caption = "OM Tools" Then
oCmdCtl.Delete
Exit For
End If
Next oCmdCtl


End Sub


When I click on the Help option in the menu, nothing at all happens (which is better than getting an error!).

[andrewjs]

I think that you want



With oOMToolsPopup
With .Controls.Add(msoControlButton)
.Caption = &quot;Help for OM &Tools&quot;
.HelpFile = Application.Path & &quot;\OMToolsHelp.chm&quot;
.HelpContextId = 50
.Enabled = True
.BeginGroup = True
End With
End With


Is an .OnAction necessary so the menu item actually does something?

[Bob Phillips]

I have been reading up on this, and although we seem to be following the instructions, the help says that it is invoked with Shift-F1, not clicking the control, and seems to suggest that it only appliesto controls that are inline, such as comboboxes.

Seems best to revert to my tried and trusted method, using the API call




Declare Function HtmlHelp Lib "hhctrl.ocx" _
Alias "HtmlHelpA" _
(ByVal hWnd As Long, _
ByVal lpHelpFile As String, _
ByVal wCommand As Long, _
ByVal dwData As Long) As Long

Const HH_DISPLAY_TOPIC = &H0
Const HH_SET_WIN_TYPE = &H4
Const HH_GET_WIN_TYPE = &H5
Const HH_GET_WIN_HANDLE = &H6
Const HH_DISPLAY_TEXT_POPUP = &HE ' Display string resource ID or text in _
a pop-up window.
Const HH_HELP_CONTEXT = &HF ' Display mapped numeric value in _
dwData.
Const HH_TP_HELP_CONTEXTMENU = &H10 ' Text pop-up help, similar to WinHelp's _
HELP_CONTEXTMENU.
Const HH_TP_HELP_WM_HELP = &H11 ' text pop-up help, similar to WinHelp's _
HELP_WM_HELP.

Public Sub OpenOMToolsHelp()
Dim hwndHelp As Long
'The return value is the window handle of the created help window.
Dim hwndHH
hwndHH = HtmlHelp(0, ThisWorkbook.Path & "\OMToolsHelp.chm", _
HH_HELP_CONTEXT, 50)
End Sub

[andrewjs]

Wow, thanks! I'll try this later... I am not sure which module you suggest this be placed into. I have zero experience with API stuff. Does all that go into the main menu module, that draws the menu? There is also a subroutine that I supposed would actually go in the separate module that has all the other subs, or does it matter?

I'll be very very happy indeed if this works on the first try, but I'll let you know. thanks again!

[Bob Phillips]

Just add it to any module, but make sure all the API and constant declarations are at the head of the module.

And then set that control's OnAction property to point at the ShowOMToolsHelp procedure.

[andrewjs]

OK, i feel like i'm getting close! I should say, we're getting close! I am getting this error with the code I have added:

HH_HELP_CONTEXT called without a [Map] section.

... upon clicking on the help menu item.

[Bob Phillips]

That sounds like your help file is incomplete, so the contextid is not finding its designated section.

[andrewjs]

That sounds like your help file is incomplete, so the contextid is not finding its designated section.

Ahh, that's probably true, I haven't finished the Help file, but how does the module know that? What does a contextid of 50 refer to? I used the Microsoft HTML Help Workshop, and there's no reason why my Help file couldn't be considered finished (although I am planning to add more to it).

[Bob Phillips]

When you are creating a help file, you can give a chapter or a section an entry point. This is known as the ContextId. When you call the help file with the context id, it jumps directly into that location.

I haven't used HTML Help Workshop in a while (I assume that is what you are using), I use West Wind Help compiler, but you have to add them somewhere in there. The 50 was the number you gave originally, and it knowns that it isn't finished because you passed a contextid of 50, and it couldn't find it.

[andrewjs]

When you are creating a help file, you can give a chapter or a section an entry point. This is known as the ContextId. When you call the help file with the context id, it jumps directly into that location.

I haven't used HTML Help Workshop in a while (I assume that is what you are using), I use West Wind Help compiler, but you have to add them somewhere in there. The 50 was the number you gave originally, and it knowns that it isn't finished because you passed a contextid of 50, and it couldn't find it.

I am reading the documentation withing Help Workshop on this right now.. it would appear as though it is overkill for me to use context ids at all since I just want the Help file to open to its default page (which is already set in the .chm). It would seem overkill to create the header files, and then the topic text files that Workshop needs for context-sensitive help when I don't actually need it to be context-sensitive. Can I remove some portion of the API code so that it doesn't look for a particular context ID, and just opens the file?

[Bob Phillips]

In that case you are going to have to call in to the first topic.



Declare Function HtmlHelp Lib "hhctrl.ocx" _
Alias "HtmlHelpA" _
(ByVal hWnd As Long, _
ByVal lpHelpFile As String, _
ByVal wCommand As Long, _
ByVal dwData As Any) As Long

Const HH_DISPLAY_TOPIC = &H0
Const HH_SET_WIN_TYPE = &H4
Const HH_GET_WIN_TYPE = &H5
Const HH_GET_WIN_HANDLE = &H6
Const HH_DISPLAY_TEXT_POPUP = &HE ' Display string resource ID or text in _
a pop-up window.
Const HH_HELP_CONTEXT = &HF ' Display mapped numeric value in _
dwData.
Const HH_TP_HELP_CONTEXTMENU = &H10 ' Text pop-up help, similar to WinHelp's _
HELP_CONTEXTMENU.
Const HH_TP_HELP_WM_HELP = &H11 ' text pop-up help, similar to WinHelp's _
HELP_WM_HELP.

Public Sub OpenOMToolsHelp()
Dim hwndHelp As Long
'The return value is the window handle of the created help window.
Dim hwndHH

hwndHH = HtmlHelp(0, ThisWorkbook.Path & "\OMToolsHelp.chm", _
HH_DISPLAY_TOPIC, ByVal "topic1.htm")
End Sub


Note that you have pass the name of the first topic file to the API, and also note that I have change the type declaration, to allow for this string.

[andrewjs]

I'm getting a type mismatch error with that change. Also I wonder what the "topic1.htm" is referring to. It's one .chm file, the topics are all contained within that one file, and once that .chm is compiled, it is no longer .htm, right? I tried substituting the name of the original .htm that was used to create the .chm, but that didn't work nor would that make sense. I also tried the name of the of topic page in the .chm from the table of contents. I looked at this page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/htmlhelp/html/vsconCmdhhdisplaytopic.asp

Which seems to suggest that you don't have to specify a topic, but using NULL as provided in the example still didn't work. Type mismatch error all the way..

[Bob Phillips]

NULL is C, VB is vbNull, although that also does nothing in my test.

When you use HTML Help workshop, even though it compiles to a chm file, the source is htm files, so pick one of them.

[andrewjs]

When you use HTML Help workshop, even though it compiles to a chm file, the source is htm files, so pick one of them.

I have tried that but no matter what I put in there, i get a "type mismatch" error for the entire line:

hwndHH = HtmlHelp(0, ThisWorkbook.Path & "\OMToolsHelp.chm", _
HH_DISPLAY_TOPIC, ByVal "general_help.htm")

[Bob Phillips]

Did you also change the line that was

ByVal dwData As Long) As Long

to

ByVal dwData As Any) As Long

as I pointed out earlier.

You have to do this otherwise it is trying to put a string in a Long, type mismatch.

[andrewjs]

Holy cow it is working!! I am very appreciative of your help. I am going to have to study the code now to see exactly how it is working. API seems quite full of odd commands that mean nothing to me now. I really thank you!

[Bob Phillips]

I have just realised why the null didn't work as well, I should have been passing vbNullString, not just vbNull.



Public Sub OpenOMToolsHelp()
Dim hwndHelp As Long
'The return value is the window handle of the created help window.
Dim hwndHH

hwndHH = HtmlHelp(0, &quot;ThisWorkbook.Path & "\OMToolsHelp.chm&quot;, _
HH_DISPLAY_TOPIC, ByVal vbNullString)
End Sub

[andrewjs]

Well using the .htm file mention instead seems to do the trick, so I'm guessing that the results are the same either way (since null would presumably open the .chm to its default page, which in this case is the same as the .htm i used instead of null).

[Bob Phillips]

Indeed it does, but it is nice to know null DOES work.

[andrewjs]

Ok, on some computers, when the Help file opens, the table of contents displays but all the help files themselves on the right pane show: Action canceled

Internet Explorer was unable to link to the Web page you requested. The page might be temporarily unavailable.

Even changing it to your null string instead of the name of the .htm that created the pages does not work. Why would this be? Affects some computers but not all.

[Bob Phillips]

No idea, never seen that I am afraid.

[andrewjs]

I think i found the problem. It only happens if you post the .chm to a web server, then download it. Windows security updates disable .chm files upon download due to potential security concerns:

http://support.microsoft.com/default.aspx?scid=kb;en-us;902225

I'll be if it gets zipped up though it wouldn't be a problem. (which i'll have to test)