Progress! The error message is working, but not the Case code.

Excel is throwing a Compile error: Argument not optional. On the following
line,
ThisWorkbook.FollowHyperlink.Offset(ComboBox1.Lis €¦. €œFollowHyperlink€� is
highlighted

When I reset the VBA editor, and then select (click) in the ComboBox,
nothing happens €“ makes me wonder if I have the cell in the first line of
code set properly for the code to find the proper Action and Value cells.
The down rows and over columns thing. Could you explain that again?


"NickHK" wrote:

Phil,
OK, see if you can get to work:
Private Sub ComboBox1_Click()
With ThisWorkbook.ActiveSheet.Range("K91")
Select Case (.Offset(ComboBox1.ListIndex, 5).Value)
Case "Hyperlink" '"Hyperlink" is the exact Action cell text
ThisWorkbook.FollowHyperlink .Offset(ComboBox1.ListIndex,
6).Value
Case "Run Macro" '"Run Macro" is the exact Action cell text
Application.Run .Offset(ComboBox1.ListIndex, 6).Value
Case Else
MsgBox "Other Action not currently supported.", vbInformation +
vbOKOnly, "Info"
End Select
End With
End Sub

It could do with some error handling for cases when the macro or hyperlink
is not valid/cannot function, but we need to get this going first.

NickHK

"Phil H" wrote in message
...
Nick

My solution continues to elude. Please understand, I do not understand
the
vernacular used by professional programmers and have to compensate by
reading
helps, as you have suggested, or dig into the books. But for what I'm
trying
to do here, I need a sample to follow, or code that gets things done.
This
said, can we make a final try for a solution? If so, here is the code I
have
in the ComboBox click event:

Private Sub ComboBox1_Click()

With Range("K91")

Select Case (.Offset(ComboBox1.ListIndex, 1).Value) "I eliminated
the LCase"

Case "Hyperlink" "Hyperlink" is the exact Action cell text
(.Offset(ComboBox1.ListIndex, 5).Value = 6)

Case "Run Macro" "Run Macro" is the exact Action cell text
(.Offset(ComboBox1.ListIndex, 5).Value = 6)

Case Else

End Select
End With
End Sub

Value cells how have working hyperlinks inserted and macro names inserted

Thanks for you time on this Nick .
Phil


"NickHK" wrote:

Phil,
It looks like your combobox is called "Combobox1". So change CB to match
that.
"hlink" does not be dimmed; it's a string literal.
Ok, so for .Offset you need 5 to get the Action and 6 to get Value.

Did you read the Help on Select Case ? Or follow the example I sent ?
Case "hlink"
'follow the hyperlink of value=Offset(CB.ListIndex,2).Value
Case "macro"

NickHK

"Phil H" wrote in message
...
Nick,

Since I have set Option Explicit, Excel demands CB and Hlink be
dimmed. I
'm
sure "Macro" will have to be dimmed when we get to it.
Merged cells have been unmerged.
Code now resides in the ComboBox1 event
Click event is used
In this example, the action names (Hlink and Macro) are 5 columns to
the
right of K91, and the values (hyperlink addresses or macro names) are
6
columns to the right.

I'm trying to set this up as a universal tool, such that whatever text
is
used for the various CB choices, the tool works - only the hyperlinks
and
macro names would change. Thus the use of Case "1", Case "2", etc.
Is
this
okay?

Here is how I've set up the code. Could you fill in missing code that
would
make this work?

Private Sub ComboBox1_Click()
Dim CB As ComboBox
Dim Hlink As Hyperlink
With Range("K91")
Select Case LCase(.Offset(CB.ListIndex, 1).Value)
Case 1

Case 2

Case 3

Case 4

End Select
End With
End Sub

Thanks, Phil

"NickHK" wrote:

Phil,
"CB" is the name of the combobox, so change that to whatever you
have
called
your combobox. No need to Dim CB as ComboBox. And this code goes in
the
CB_Click event, so it is fired when a selection is made.
You need to check the help for the structure of Select Case. See my
example
and follow that.
Select Case [test expression]
Case [Value1]
'Do this if true
Case [Value2]
'Do this if true
....etc

Also, Help will explain how Offset work. It takes 2 arguments (rows,
columns)

The logic of this approach is that from whatever value is selected
in
the
combobox, you go down that many rows from the "K91" (given by
CB.ListIndex),
then across to the "Action" column of that row.
You then see what should do; HLink or Macro are the 2 choices have
at
the
moment. So once you know that will e.g hyperlink, then code
following
"Case
"hlink"" will be run.

It would be easier if you did not used merged cells, as that makes
it
more
difficult to clearly see how many columns you have Offset to the
right
to
get to the Action and Value columns.

Unless anyone has any other ideas...

NickHK

----------- CUT --------------