|
ComboBox Code Error
The below ComboBox code is intended to launch a hyperlink, or start a macro,
based on user selection. The ComboBox operates okay, and Case Hyperlink works, but Case Run Macro generates a Run time error 1004: The macro Sub GoToTFMWorksheetPage1() cannot be found, with the two lines beginning Application.run yellow highlighted. Cell range G81:G86 holds the action names Hyperlink or Run Macro, with the hyperlink or macro name immediately adjacent in cells F81:F86. The macro is located in the worksheet module where the ComboBox is located, and the text of the macro name was copy/pasted into the code to assure exact macro name recognition. Could someone suggest how to correct this problem? Thanks, Phil Option Explicit Private Sub ComboBox1_Click() With ThisWorkbook.ActiveSheet.Range("A79") Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Hyperlink" ThisWorkbook.FollowHyperlink _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Run Macro" Application.Run _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case Else MsgBox "This selection has no hyperlinked document or linked worksheet.", vbInformation + vbOKOnly, "Information:" End Select End With End Sub |
ComboBox Code Error
Phil,
Try putting the macro(s) in a general module rather than the worksheet. Doing this worked OK for me. "Phil H" wrote: The below ComboBox code is intended to launch a hyperlink, or start a macro, based on user selection. The ComboBox operates okay, and Case Hyperlink works, but Case Run Macro generates a Run time error 1004: The macro Sub GoToTFMWorksheetPage1() cannot be found, with the two lines beginning Application.run yellow highlighted. Cell range G81:G86 holds the action names Hyperlink or Run Macro, with the hyperlink or macro name immediately adjacent in cells F81:F86. The macro is located in the worksheet module where the ComboBox is located, and the text of the macro name was copy/pasted into the code to assure exact macro name recognition. Could someone suggest how to correct this problem? Thanks, Phil Option Explicit Private Sub ComboBox1_Click() With ThisWorkbook.ActiveSheet.Range("A79") Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Hyperlink" ThisWorkbook.FollowHyperlink _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Run Macro" Application.Run _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case Else MsgBox "This selection has no hyperlinked document or linked worksheet.", vbInformation + vbOKOnly, "Information:" End Select End With End Sub |
ComboBox Code Error
Hi Toppers,
Moved the macro into Module 2 (where the GoTo...macros are located) and none of the cases work (Hyperlink and Default worked previously in the worksheet module). No error messages. "Toppers" wrote: Phil, Try putting the macro(s) in a general module rather than the worksheet. Doing this worked OK for me. "Phil H" wrote: The below ComboBox code is intended to launch a hyperlink, or start a macro, based on user selection. The ComboBox operates okay, and Case Hyperlink works, but Case Run Macro generates a Run time error 1004: The macro Sub GoToTFMWorksheetPage1() cannot be found, with the two lines beginning Application.run yellow highlighted. Cell range G81:G86 holds the action names Hyperlink or Run Macro, with the hyperlink or macro name immediately adjacent in cells F81:F86. The macro is located in the worksheet module where the ComboBox is located, and the text of the macro name was copy/pasted into the code to assure exact macro name recognition. Could someone suggest how to correct this problem? Thanks, Phil Option Explicit Private Sub ComboBox1_Click() With ThisWorkbook.ActiveSheet.Range("A79") Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Hyperlink" ThisWorkbook.FollowHyperlink _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Run Macro" Application.Run _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case Else MsgBox "This selection has no hyperlinked document or linked worksheet.", vbInformation + vbOKOnly, "Information:" End Select End With End Sub |
ComboBox Code Error
Phil,
Sorry if I have confused things: the combox macro should stay with the sheet in which it exists. The other macros i.e. those called, should be in a general module. I have re-run a test and it calls my macro OK and activates a hyperlink. What have you placed in your cell ... it should be "GoToTFMWorksheetPage1()" ... no SUB. "Phil H" wrote: The below ComboBox code is intended to launch a hyperlink, or start a macro, based on user selection. The ComboBox operates okay, and Case Hyperlink works, but Case Run Macro generates a Run time error 1004: The macro Sub GoToTFMWorksheetPage1() cannot be found, with the two lines beginning Application.run yellow highlighted. Cell range G81:G86 holds the action names Hyperlink or Run Macro, with the hyperlink or macro name immediately adjacent in cells F81:F86. The macro is located in the worksheet module where the ComboBox is located, and the text of the macro name was copy/pasted into the code to assure exact macro name recognition. Could someone suggest how to correct this problem? Thanks, Phil Option Explicit Private Sub ComboBox1_Click() With ThisWorkbook.ActiveSheet.Range("A79") Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Hyperlink" ThisWorkbook.FollowHyperlink _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Run Macro" Application.Run _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case Else MsgBox "This selection has no hyperlinked document or linked worksheet.", vbInformation + vbOKOnly, "Information:" End Select End With End Sub |
ComboBox Code Error
Toppers,
Gaining ground. I put the code back in the worksheet module, and removed the text "Sub " from the Value cell. Cases for hyperlink and default work again. The macro case still does not work, but, the run-time error has been eliminated. The text in the Action cells (in the range G81:G86) for running the macro remain "Run Macro," without the parentheses. "Toppers" wrote: Phil, Sorry if I have confused things: the combox macro should stay with the sheet in which it exists. The other macros i.e. those called, should be in a general module. I have re-run a test and it calls my macro OK and activates a hyperlink. What have you placed in your cell ... it should be "GoToTFMWorksheetPage1()" .. no SUB. "Phil H" wrote: The below ComboBox code is intended to launch a hyperlink, or start a macro, based on user selection. The ComboBox operates okay, and Case Hyperlink works, but Case Run Macro generates a Run time error 1004: The macro Sub GoToTFMWorksheetPage1() cannot be found, with the two lines beginning Application.run yellow highlighted. Cell range G81:G86 holds the action names Hyperlink or Run Macro, with the hyperlink or macro name immediately adjacent in cells F81:F86. The macro is located in the worksheet module where the ComboBox is located, and the text of the macro name was copy/pasted into the code to assure exact macro name recognition. Could someone suggest how to correct this problem? Thanks, Phil Option Explicit Private Sub ComboBox1_Click() With ThisWorkbook.ActiveSheet.Range("A79") Select Case Range("F81:F86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Hyperlink" ThisWorkbook.FollowHyperlink _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case "Run Macro" Application.Run _ Range("G81:G86").Cells(ComboBox1.ListIndex + 1, 1).Value Case Else MsgBox "This selection has no hyperlinked document or linked worksheet.", vbInformation + vbOKOnly, "Information:" End Select End With End Sub |
| All times are GMT +1. The time now is 02:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com