ComboBox Code Error

The below ComboBox code is intended to launch a hyperlink, or start a macro,
based on user selection. The ComboBox operates okay, and Case �Hyperlink�
works, but Case �Run Macro� generates a Run time error �1004�: The macro Sub
GoToTFMWorksheetPage1()� cannot be found, with the two lines beginning
Application.run� yellow highlighted. Cell range G81:G86 holds the action
names Hyperlink or Run Macro, with the hyperlink or macro name immediately
adjacent in cells F81:F86. The macro is located in the worksheet module
where the ComboBox is located, and the text of the macro name was copy/pasted
into the code to assure exact macro name recognition. Could someone suggest
how to correct this problem?
Thanks, Phil

Option Explicit

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case Else
MsgBox "This selection has no hyperlinked document or linked
worksheet.", vbInformation + vbOKOnly, "Information:"
End Select
End With
End Sub

Phil,
Try putting the macro(s) in a general module rather than the
worksheet. Doing this worked OK for me.

"Phil H" wrote:

> The below ComboBox code is intended to launch a hyperlink, or start a macro,
> based on user selection. The ComboBox operates okay, and Case “Hyperlink”
> works, but Case “Run Macro” generates a Run time error ‘1004’: The macro Sub
> GoToTFMWorksheetPage1()’ cannot be found, with the two lines beginning
> Application.run… yellow highlighted. Cell range G81:G86 holds the action
> names Hyperlink or Run Macro, with the hyperlink or macro name immediately
> adjacent in cells F81:F86. The macro is located in the worksheet module
> where the ComboBox is located, and the text of the macro name was copy/pasted
> into the code to assure exact macro name recognition. Could someone suggest
> how to correct this problem?
> Thanks, Phil
>
> Option Explicit
>
> Private Sub ComboBox1_Click()
> With ThisWorkbook.ActiveSheet.Range("A79")
> Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
> Case "Hyperlink"
> ThisWorkbook.FollowHyperlink _
> Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> Case "Run Macro"
> Application.Run _
> Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> Case Else
> MsgBox "This selection has no hyperlinked document or linked
> worksheet.", vbInformation + vbOKOnly, "Information:"
> End Select
> End With
> End Sub
>

Hi Toppers,

Moved the macro into Module 2 (where the GoTo...macros are located) and none
of the cases work (Hyperlink and Default worked previously in the worksheet
module). No error messages.

"Toppers" wrote:

> Phil,
> Try putting the macro(s) in a general module rather than the
> worksheet. Doing this worked OK for me.
>
> "Phil H" wrote:
>
> > The below ComboBox code is intended to launch a hyperlink, or start a macro,
> > based on user selection. The ComboBox operates okay, and Case “Hyperlink”
> > works, but Case “Run Macro” generates a Run time error ‘1004’: The macro Sub
> > GoToTFMWorksheetPage1()’ cannot be found, with the two lines beginning
> > Application.run… yellow highlighted. Cell range G81:G86 holds the action
> > names Hyperlink or Run Macro, with the hyperlink or macro name immediately
> > adjacent in cells F81:F86. The macro is located in the worksheet module
> > where the ComboBox is located, and the text of the macro name was copy/pasted
> > into the code to assure exact macro name recognition. Could someone suggest
> > how to correct this problem?
> > Thanks, Phil
> >
> > Option Explicit
> >
> > Private Sub ComboBox1_Click()
> > With ThisWorkbook.ActiveSheet.Range("A79")
> > Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
> > Case "Hyperlink"
> > ThisWorkbook.FollowHyperlink _
> > Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> > Case "Run Macro"
> > Application.Run _
> > Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> > Case Else
> > MsgBox "This selection has no hyperlinked document or linked
> > worksheet.", vbInformation + vbOKOnly, "Information:"
> > End Select
> > End With
> > End Sub
> >

Phil,
Sorry if I have confused things: the combox macro should stay
with the sheet in which it exists. The other macros i.e. those called, should
be in a general module. I have re-run a test and it calls my macro OK and
activates a hyperlink.

What have you placed in your cell ... it should be "GoToTFMWorksheetPage1()"
... no SUB.


"Phil H" wrote:

> The below ComboBox code is intended to launch a hyperlink, or start a macro,
> based on user selection. The ComboBox operates okay, and Case “Hyperlink”
> works, but Case “Run Macro” generates a Run time error ‘1004’: The macro Sub
> GoToTFMWorksheetPage1()’ cannot be found, with the two lines beginning
> Application.run… yellow highlighted. Cell range G81:G86 holds the action
> names Hyperlink or Run Macro, with the hyperlink or macro name immediately
> adjacent in cells F81:F86. The macro is located in the worksheet module
> where the ComboBox is located, and the text of the macro name was copy/pasted
> into the code to assure exact macro name recognition. Could someone suggest
> how to correct this problem?
> Thanks, Phil
>
> Option Explicit
>
> Private Sub ComboBox1_Click()
> With ThisWorkbook.ActiveSheet.Range("A79")
> Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
> Case "Hyperlink"
> ThisWorkbook.FollowHyperlink _
> Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> Case "Run Macro"
> Application.Run _
> Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> Case Else
> MsgBox "This selection has no hyperlinked document or linked
> worksheet.", vbInformation + vbOKOnly, "Information:"
> End Select
> End With
> End Sub
>

Toppers,

Gaining ground. I put the code back in the worksheet module, and removed
the text "Sub " from the Value cell. Cases for hyperlink and default work
again. The macro case still does not work, but, the run-time error has been
eliminated. The text in the Action cells (in the range G81:G86) for running
the macro remain "Run Macro," without the parentheses.

"Toppers" wrote:

> Phil,
> Sorry if I have confused things: the combox macro should stay
> with the sheet in which it exists. The other macros i.e. those called, should
> be in a general module. I have re-run a test and it calls my macro OK and
> activates a hyperlink.
>
> What have you placed in your cell ... it should be "GoToTFMWorksheetPage1()"
> .. no SUB.
>
>
> "Phil H" wrote:
>
> > The below ComboBox code is intended to launch a hyperlink, or start a macro,
> > based on user selection. The ComboBox operates okay, and Case “Hyperlink”
> > works, but Case “Run Macro” generates a Run time error ‘1004’: The macro Sub
> > GoToTFMWorksheetPage1()’ cannot be found, with the two lines beginning
> > Application.run… yellow highlighted. Cell range G81:G86 holds the action
> > names Hyperlink or Run Macro, with the hyperlink or macro name immediately
> > adjacent in cells F81:F86. The macro is located in the worksheet module
> > where the ComboBox is located, and the text of the macro name was copy/pasted
> > into the code to assure exact macro name recognition. Could someone suggest
> > how to correct this problem?
> > Thanks, Phil
> >
> > Option Explicit
> >
> > Private Sub ComboBox1_Click()
> > With ThisWorkbook.ActiveSheet.Range("A79")
> > Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
> > Case "Hyperlink"
> > ThisWorkbook.FollowHyperlink _
> > Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> > Case "Run Macro"
> > Application.Run _
> > Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
> > Case Else
> > MsgBox "This selection has no hyperlinked document or linked
> > worksheet.", vbInformation + vbOKOnly, "Information:"
> > End Select
> > End With
> > End Sub
> >