ComboBox Code Problems
 

The following code is held in ComboBox1 click event. It is intended when the
user clicks on one of the choices, either a hyperlink or macro is executed.
Im getting a compile error: Argument not optional. On the fifth line, the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index, 6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

You are using Offset incorrectly. What is the relationship between links and
macros, how does the code know which the user picks?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is intended when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is intended when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Hi Bob,

Thanks for your reply. Having the same compile error on FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is intended when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is intended
when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Hi Bob,
You were right - the compile error cleared. I also made a correction in
the ranges - F1:F86 to F81:F86, etc - which is allowing the hyperlink case to
work properly. The macro case is not working though - Runtime error 1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located in Module
2 because they operate for all worksheets, are correct (recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value

is highlighted yellow. With the "Range("G81......).Value" poriton proven
correct for the hyperlink case, Excel must object to the Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is intended
when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

It doesn't object to that bit, I tested it and it worked fine.

I am not sure what this means

.... Macro names, located in Module 2 because they operate for all
worksheets, are correct (recopied/pasted names, and retested).

Your combobox is a controls toolbox combo on the worksheet I am assuming?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a correction in
the ranges - F1:F86 to F81:F86, etc - which is allowing the hyperlink case
to
work properly. The macro case is not working though - Runtime error 1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located in
Module
2 because they operate for all worksheets, are correct (recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value

is highlighted yellow. With the "Range("G81......).Value" poriton proven
correct for the hyperlink case, Excel must object to the Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is
intended
when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a correction in
the ranges - F1:F86 to F81:F86, etc - which is allowing the hyperlink case
to
work properly. The macro case is not working though - Runtime error 1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located in
Module
2 because they operate for all worksheets, are correct (recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value

is highlighted yellow. With the "Range("G81......).Value" poriton proven
correct for the hyperlink case, Excel must object to the Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is
intended
when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are used on
multiple sheets (to differentiate from a macro located in a worksheet
module). Here is an example on one listed for the ComboBox. To make sure the
code and the macro name exactly matched, I copy/pasted the first line of the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a correction in
the ranges - F1:F86 to F81:F86, etc - which is allowing the hyperlink case
to
work properly. The macro case is not working though - Runtime error 1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located in
Module
2 because they operate for all worksheets, are correct (recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value

is highlighted yellow. With the "Range("G81......).Value" poriton proven
correct for the hyperlink case, Excel must object to the Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex, 1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is
intended
when
the
user clicks on one of the choices, either a hyperlink or macro is
executed.
I'm getting a compile error: Argument not optional. On the fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

As I say, the code works, I tested it. It does suggest some disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after a certain
amount of characters. In text this is no problem, but it can cause problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are used on
multiple sheets (to differentiate from a macro located in a worksheet
module). Here is an example on one listed for the ComboBox. To make sure
the
code and the macro name exactly matched, I copy/pasted the first line of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing the hyperlink
case
to
work properly. The macro case is not working though - Runtime error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1,
1).Value

is highlighted yellow. With the "Range("G81......).Value" poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is
intended
when
the
user clicks on one of the choices, either a hyperlink or macro
is
executed.
I'm getting a compile error: Argument not optional. On the
fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Bob,
You actually launch a macro? I have copied/pasted back and forth between
the macro and the cell so I know the macro name matches. I've looked in the
ComboBox properties for a solution, but find nothing inspiring. Do you have
any suggestions? I you have tested successfully, I must have a basic setting
somewhere that obstructs macro execution...
Phil

"Bob Phillips" wrote:

As I say, the code works, I tested it. It does suggest some disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after a certain
amount of characters. In text this is no problem, but it can cause problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are used on
multiple sheets (to differentiate from a macro located in a worksheet
module). Here is an example on one listed for the ComboBox. To make sure
the
code and the macro name exactly matched, I copy/pasted the first line of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing the hyperlink
case
to
work properly. The macro case is not working though - Runtime error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1,
1).Value

is highlighted yellow. With the "Range("G81......).Value" poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It is
intended
when
the
user clicks on one of the choices, either a hyperlink or macro
is
executed.
I'm getting a compile error: Argument not optional. On the
fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Yes. It was a very basic macro, just a simple Msgbox, but it launched fine.

The only other suggestion that I have is to send me your workbook offline.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Bob,
You actually launch a macro? I have copied/pasted back and forth between
the macro and the cell so I know the macro name matches. I've looked in
the
ComboBox properties for a solution, but find nothing inspiring. Do you
have
any suggestions? I you have tested successfully, I must have a basic
setting
somewhere that obstructs macro execution...
Phil

"Bob Phillips" wrote:

As I say, the code works, I tested it. It does suggest some disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after a
certain
amount of characters. In text this is no problem, but it can cause
problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are used on
multiple sheets (to differentiate from a macro located in a worksheet
module). Here is an example on one listed for the ComboBox. To make
sure
the
code and the macro name exactly matched, I copy/pasted the first line
of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a
correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing the
hyperlink
case
to
work properly. The macro case is not working though - Runtime
error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located
in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1,
1).Value

is highlighted yellow. With the "Range("G81......).Value"
poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you
want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It
is
intended
when
the
user clicks on one of the choices, either a hyperlink or
macro
is
executed.
I'm getting a compile error: Argument not optional. On
the
fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Your address? This is a 682K file.

"Bob Phillips" wrote:

Yes. It was a very basic macro, just a simple Msgbox, but it launched fine.

The only other suggestion that I have is to send me your workbook offline.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Bob,
You actually launch a macro? I have copied/pasted back and forth between
the macro and the cell so I know the macro name matches. I've looked in
the
ComboBox properties for a solution, but find nothing inspiring. Do you
have
any suggestions? I you have tested successfully, I must have a basic
setting
somewhere that obstructs macro execution...
Phil

"Bob Phillips" wrote:

As I say, the code works, I tested it. It does suggest some disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after a
certain
amount of characters. In text this is no problem, but it can cause
problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are used on
multiple sheets (to differentiate from a macro located in a worksheet
module). Here is an example on one listed for the ComboBox. To make
sure
the
code and the macro name exactly matched, I copy/pasted the first line
of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a
correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing the
hyperlink
case
to
work properly. The macro case is not working though - Runtime
error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names, located
in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex + 1,
1).Value

is highlighted yellow. With the "Range("G81......).Value"
poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case Else
MsgBox "Other Action not currently supported.", _
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you
want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
The following code is held in ComboBox1 click event. It
is
intended
when
the
user clicks on one of the choices, either a hyperlink or
macro
is
executed.
I'm getting a compile error: Argument not optional. On
the
fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex, 4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"
Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Your address? This is a 682K file.

"Bob Phillips" wrote:

Yes. It was a very basic macro, just a simple Msgbox, but it launched
fine.

The only other suggestion that I have is to send me your workbook
offline.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Bob,
You actually launch a macro? I have copied/pasted back and forth
between
the macro and the cell so I know the macro name matches. I've looked
in
the
ComboBox properties for a solution, but find nothing inspiring. Do
you
have
any suggestions? I you have tested successfully, I must have a basic
setting
somewhere that obstructs macro execution...
Phil

"Bob Phillips" wrote:

As I say, the code works, I tested it. It does suggest some
disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after a
certain
amount of characters. In text this is no problem, but it can cause
problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are
used on
multiple sheets (to differentiate from a macro located in a
worksheet
module). Here is an example on one listed for the ComboBox. To
make
sure
the
code and the macro name exactly matched, I copy/pasted the first
line
of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a
correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing the
hyperlink
case
to
work properly. The macro case is not working though - Runtime
error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names,
located
in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex +
1,
1).Value

is highlighted yellow. With the "Range("G81......).Value"
poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
_
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you
want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case
Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in
message

...
The following code is held in ComboBox1 click event.
It
is
intended
when
the
user clicks on one of the choices, either a hyperlink
or
macro
is
executed.
I'm getting a compile error: Argument not optional.
On
the
fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex,
4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"

Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Bob, Did you get the file?

"Bob Phillips" wrote:

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Your address? This is a 682K file.

"Bob Phillips" wrote:

Yes. It was a very basic macro, just a simple Msgbox, but it launched
fine.

The only other suggestion that I have is to send me your workbook
offline.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Bob,
You actually launch a macro? I have copied/pasted back and forth
between
the macro and the cell so I know the macro name matches. I've looked
in
the
ComboBox properties for a solution, but find nothing inspiring. Do
you
have
any suggestions? I you have tested successfully, I must have a basic
setting
somewhere that obstructs macro execution...
Phil

"Bob Phillips" wrote:

As I say, the code works, I tested it. It does suggest some
disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after a
certain
amount of characters. In text this is no problem, but it can cause
problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are
used on
multiple sheets (to differentiate from a macro located in a
worksheet
module). Here is an example on one listed for the ComboBox. To
make
sure
the
code and the macro name exactly matched, I copy/pasted the first
line
of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made a
correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing the
hyperlink
case
to
work properly. The macro case is not working though - Runtime
error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names,
located
in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex +
1,
1).Value

is highlighted yellow. With the "Range("G81......).Value"
poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case Range("F1:F86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case "Run Macro"
Application.Run _
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently supported.",
_
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,

Thanks for your reply. Having the same compile error on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what you
want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case
Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1, 1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in
message

...
The following code is held in ComboBox1 click event.
It
is
intended
when
the
user clicks on one of the choices, either a hyperlink
or
macro
is
executed.
I'm getting a compile error: Argument not optional.
On
the
fifth
line,
the
following code is highlighted: .FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex,
4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"

Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run Macro)
G81:G86 Value (Hyperlinks, or, Macro names)

ComboBox Code Problems
 

Haven't checked yet Phil, was out of action yesterday. Will check later this
morning.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Bob, Did you get the file?

"Bob Phillips" wrote:

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Your address? This is a 682K file.

"Bob Phillips" wrote:

Yes. It was a very basic macro, just a simple Msgbox, but it
launched
fine.

The only other suggestion that I have is to send me your workbook
offline.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Bob,
You actually launch a macro? I have copied/pasted back and forth
between
the macro and the cell so I know the macro name matches. I've
looked
in
the
ComboBox properties for a solution, but find nothing inspiring.
Do
you
have
any suggestions? I you have tested successfully, I must have a
basic
setting
somewhere that obstructs macro execution...
Phil

"Bob Phillips" wrote:

As I say, the code works, I tested it. It does suggest some
disconnect
between the actual name and the name in your list.

NG wrap-around is the way that newsgroups force a new line after
a
certain
amount of characters. In text this is no problem, but it can
cause
problems
when it splits a line of code over two lines.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Yes, this is a controls ComboBox.

I place "navigation" macros in a separate module when they are
used on
multiple sheets (to differentiate from a macro located in a
worksheet
module). Here is an example on one listed for the ComboBox.
To
make
sure
the
code and the macro name exactly matched, I copy/pasted the
first
line
of
the
macro into the cell where the "case" code would look.

Sub GoToTFMWorksheetPage1()
Application.ScreenUpdating = False
Sheets("TFM Worksheet Page 1").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("B100"), Scroll:=False
ActiveWindow.Zoom = 100
Application.ScreenUpdating = True
End Sub

In way of learning - what is meant by "NG wrap-around?"

"Bob Phillips" wrote:

Another thought, do the subs have arguments (parameters)?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in message
...
Hi Bob,
You were right - the compile error cleared. I also made
a
correction
in
the ranges - F1:F86 to F81:F86, etc - which is allowing
the
hyperlink
case
to
work properly. The macro case is not working though -
Runtime
error
1004:
The macro 'Sub GoTo.....()' cannot be found. Macro names,
located
in
Module
2 because they operate for all worksheets, are correct
(recopied/pasted
names, and retested). The entire line:
Application.Run _
Range("G81:G86").Cells(ComboBox1.ListIndex
+
1,
1).Value

is highlighted yellow. With the "Range("G81......).Value"
poriton
proven
correct for the hyperlink case, Excel must object to the
Application.Run
syntax. ???

Phil
"Bob Phillips" wrote:

Just NG wrap-around I think

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case
Range("F1:F86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink _

Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case "Run Macro"
Application.Run _

Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently
supported.",
_
vbInformation + vbOKOnly, "Info"
End Select
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Phil H" wrote in
message

...
Hi Bob,

Thanks for your reply. Having the same compile error
on
FollowHyperlink...

Phil
"Bob Phillips" wrote:

Looking at it again, I think that this might be what
you
want

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case
Range("F1:F86").Cells(ComboBox1.ListIndex,
1).Value
Case "Hyperlink"
ThisWorkbook.FollowHyperlink
Range("G1:G86").Cells(ComboBox1.ListIndex + 1,
1).Value
Case "Run Macro"
Application.Run
Range("G1:G86").Cells(ComboBox1.ListIndex +
1,
1).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing
direct)

"Phil H" wrote in
message

...
The following code is held in ComboBox1 click
event.
It
is
intended
when
the
user clicks on one of the choices, either a
hyperlink
or
macro
is
executed.
I'm getting a compile error: Argument not
optional.
On
the
fifth
line,
the
following code is highlighted:
..FollowHyperlink

Can someone suggest a fix?

Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("A79")
Select Case (.Offset(ComboBox1.ListIndex,
4).Value)
Case "Hyperlink"

ThisWorkbook.FollowHyperlink.Offset(ComboBox1.List Index,
6).Value
Case "Run Macro"

Application.Run.Offset(ComboBox1.ListIndex,
6).Value
Case Else
MsgBox "Other Action not currently
supported.",
vbInformation
+
vbOKOnly, "Info"
End Select
End With
End Sub
Operating addresses:

Cell Function
A79 ComboBox Linked Cell
A81:A86 ListFill Range (Text appearing in
ComboBox)
F81:F86 Action (Text: Hyperlink, or, Run
Macro)
G81:G86 Value (Hyperlinks, or, Macro names)