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?

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

NickHK

"Phil H" wrote in message
...
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?

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" wrote:

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

NickHK

"Phil H" wrote in message
...
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?

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

"Phil H" wrote in message
...
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" wrote:

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

NickHK

"Phil H" wrote in message
...
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?

Nick,

To simplify this conversation, lets 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 P91:P94.

Nick, Im really at a loss as to how to set this up. Im used to command
buttons with macros attached, but have never set up a CB to do this. Im 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 couldnt find anything on it. Any help is greatly appreciated.

Phil

"NickHK" wrote:

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

"Phil H" wrote in message
...
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" wrote:

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

NickHK

"Phil H" wrote in message
...
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?

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

"Phil H" wrote in message
...
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 P91:P94.

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" wrote:

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

"Phil H" wrote in message
...
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" wrote:

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

NickHK

"Phil H" wrote in message
...
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?