 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2006ListBox/ComboBox Acting as Macro/Hyperlink Tool
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 > 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 > Phil, > You have the _Click and _Change events for these controls where you can run [quoted text clipped - 8 lines] > > 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 > Hi Nick, > [quoted text clipped - 29 lines] > > > the selected text appears in the LB or CB, and the macro or hyperlink is > > > executed? 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 > Phil, > What are you putting in the LB/CB to indicate the destination ? [quoted text clipped - 44 lines] > 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 > 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: [quoted text clipped - 15 lines] > 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 - [quoted text clipped - 50 lines] > > is > > > > > executed? 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.” > Phil, > As far as I see it, if you have a variety of actions (hyperlink, macro, [quoted text clipped - 112 lines] > > > is > > > > > > executed? 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). [quoted text clipped - 19 lines] > 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: > [quoted text clipped - 128 lines] > > > > is > > > > > > > executed? 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 [quoted text clipped - 202 lines] > > > > > is > > > > > > > > executed? 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. [quoted text clipped - 10 lines] > > Here is how I've set up the code. Could you fill in missing code that would > make this work? > [quoted text clipped - 47 lines] > > > > NickHK ----------- CUT -------------- 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 [quoted text clipped - 90 lines] > > > > ----------- CUT -------------- 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 [quoted text clipped - 118 lines] > > > > > > ----------- CUT -------------- 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: [quoted text clipped - 163 lines] > > > > > > > > ----------- CUT -------------- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.