ListBox/ComboBox Acting as Macro/Hyperlink Tool

[Guest]

Is it possible have a List Box or Combo Box act as a macro and/or hyperlink
tool, such that when the user highlights one of the choices and clicks on it,
the selected text appears in the LB or CB, and the macro or hyperlink is
executed?

 

[NickHK]

Phil,
You have the _Click and _Change events for these controls where you can run
whatever code you require.

NickHK

 

[Guest]

Hi Nick,

Thanks for your reply - glad to know this is possible.

Using Excel 2003. Can you help me set this up? I want to set up either a
LB or CB, don't know which is preferable, here to move users by macro to
other worksheets within a workbook, or by hyperlink to documents outside the
document - I need both capabilities in one tool. Below is a typical macro
I'm using to move within the workbook. Can you help me set up this tool code?

Thanks, Phil

Sub GoToREP003()
Application.ScreenUpdating = False
Sheets("REP003").Select
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("A100"), Scroll:=False
ActiveWindow.Zoom = 84
Application.ScreenUpdating = True
End Sub

 

[NickHK]

Phil,
What are you putting in the LB/CB to indicate the destination ?
Assuming it is filled from a range called "LinkList" that contains all the
hyperlinks, you could:
Range(LinkList).Hyperlinks(List1.Index + 1).Follow....

I don't use hyperlink etc much, but something along these lines should work.

NickHK

 

[Guest]

Nick,

To simplify this conversation, let’s use a Combo Box. As I understand a CB,
I would have, for example, four lines (set up in the LinkedCell,
ListFillRange) with the text of the destination document. To illustrate:

For ComboBox1: LinkedCell is K89, ListFillRange is K91:K94

Cell ListFillRange Text Hyperlink/Macro Execute
K91 Policy ABC C:\Policies\Policy ABC
K92 Instruction XYZ Sub GoToXYZ()
K93 Note TUV K:\Notes\Note TUV
K94 Directive HIJ Sub GoToHIJ()

If the user selects “Note TUV†from the CB, “Note TUV†would appear in the
CB as usual, and Excel would execute the hyperlink. If “Instruction XYZâ€
were selected, "Instruction XYZ" would appear in the CB and the macro
GoToXYZ() would execute. The hyperlink/Sub could be set up in another cell,
say P9194.

Nick, I’m really at a loss as to how to set this up. I’m used to command
buttons with macros attached, but have never set up a CB to do this. I’m not
sure that setting up LinkedCell and ListFillRange is the answer either, but I
think I read about this a long time ago in an Excel news group somewhere -
but I couldn’t find anything on it. Any help is greatly appreciated.

Phil

 

[NickHK]

Phil,
As far as I see it, if you have a variety of actions (hyperlink, macro,
etc), depending on the selection, some sort of branching would be required.
You could have a couple of helper columns e.g.
TextToAppearInCB Action Value
K91 Policy ABC HLink C:\Policies\Policy ABC
K92 Instruction XYZ Macro GoToXYZ()
K93 Note TUV HLink K:\Notes\Note TUV
K94 Directive HIJ Macro GoToHIJ()

So in the CB_Click event, find the action that corresponds to the selected
value and branch accordingly:

With Range("K91")
Select Case LCase(.Offset(CB.ListIndex,1).Value)
Case "hlink"
'follow the hyperlink of value=Offset(CB.ListIndex,2).Value
Case "macro"
'run the macro of value=Offset(CB.ListIndex,2).Value
Case Else
'whatever...
End Select
End with

NickHK

 

[Guest]

Nick, I have set up the following (using aliases in this discussion, testing
with actual hyperlinks and macro names).

Merged Cells TextToAppearingInCB
K91:O91 Policy ABC
K92:O92 Instruction XYZ
K93:O93 Note TUV
K94:O94 Directive HIJ

Cell Action
P91 HLink
P92 Macro
P93 HLink
P94 Macro

Merged Cells Value
Q91:U91 C:\Policies\Policy ABC (inserted hyperlink)
Q92:U92 Sub GoToXYZ() (inserted macro name)
Q93:U93 K:\Notes\Note TUV (inserted hyperlink)
Q94:U94 Sub GoToHIJ() (inserted macro name)

I’m not a programmer and could not find an example in a book of how to set
up the code – can you go further with me on this? Using Option Explicit, I’m
this far:

Sub ExecuteHyperlinkOrMacro()
Dim CB As ComboBox
With Range("K91")
Select Case LCase(.Offset(CB.ListIndex, 1).Value)
Select Case LCase(.Offset(CB.ListIndex, 2).Value)
Select Case LCase(.Offset(CB.ListIndex, 3).Value)
Select Case LCase(.Offset(CB.ListIndex, 4).Value)
End Select
End With
End Sub

Running this I get a compile error: “Statements and labels invalid between
Select Case and first case.â€

 

[NickHK]

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

 

[Guest]

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

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

Nick, I have set up the following (using aliases in this discussion, testing
with actual hyperlinks and macro names).

Merged Cells TextToAppearingInCB
K91:O91 Policy ABC
K92:O92 Instruction XYZ
K93:O93 Note TUV
K94:O94 Directive HIJ

Cell Action
P91 HLink
P92 Macro
P93 HLink
P94 Macro

Merged Cells Value
Q91:U91 C:\Policies\Policy ABC (inserted hyperlink)
Q92:U92 Sub GoToXYZ() (inserted macro name)
Q93:U93 K:\Notes\Note TUV (inserted hyperlink)
Q94:U94 Sub GoToHIJ() (inserted macro name)

I'm not a programmer and could not find an example in a book of how to set
up the code - can you go further with me on this? Using Option Explicit, I'm
this far:

Sub ExecuteHyperlinkOrMacro()
Dim CB As ComboBox
With Range("K91")
Select Case LCase(.Offset(CB.ListIndex, 1).Value)
Select Case LCase(.Offset(CB.ListIndex, 2).Value)
Select Case LCase(.Offset(CB.ListIndex, 3).Value)
Select Case LCase(.Offset(CB.ListIndex, 4).Value)
End Select
End With
End Sub

Running this I get a compile error: "Statements and labels invalid between
Select Case and first case."

 

[NickHK]

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

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

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 --------------

 

[Guest]

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

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

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

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 --------------

 

[NickHK]

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

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

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

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

:

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 --------------

 

[Guest]

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?

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

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

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

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

:

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 --------------