ComboBox Code Problems

[Guest]

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.ListIndex, 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)

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Guest]

Hi Bob,

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

Phil

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

Your address? This is a 682K file.

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)

 

[Bob Phillips]

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)

 

[Guest]

Bob, Did you get the file?

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)

 

[Bob Phillips]

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)