From a drop down list (Jan to Dec) I am trying to hyperlink to monthly sheets in the same workbook. Tried a vlookup but will not return the hyperlink location. Any assistance would be appreciated.
From a drop down list (Jan to Dec) I am trying to hyperlink to monthly sheets in the same workbook. Tried a vlookup but will not return the hyperlink location. Any assistance would be appreciated.
Why not try some Anchors instead? They're a lot like hyperlinks...see attached file. I use shortcut Ctrl + K, then use 'Place in this document' to insert reference to each tab. I also place a 'HOME' link to go back to first tab. Feel free to modify to suit your needs. I haven't tried it, but you may be able to make a Combo or list box out of it too?
Install the following code on the code sheet of the worksheet on which you want to have your dropdown. Then save the workbook as macro enabled (XLSM format) workbook. Be sure to specify the cell in which you want your dropdown in the second line of the code below.
Option Explicit
' specify the cell to contain the validation:
Const DropDownAddress As String = "C3"
' the following list must contain the names of your worksheets
' as shown on their tabs, comma-separated (not case sensitive):
Const DropList As String = "Jan,Feb,Mar,Apr,May,Jun," & _
"Jul,Aug,Sep,Oct,Nov,Dec"
Private Sub Worksheet_Activate()
' 01 Sep 2017
Dim n As Long
With ActiveSheet.Range(DropDownAddress)
On Error Resume Next
n = .SpecialCells(xlCellTypeSameValidation).Count
If Err Then
n = 1 ' there is no validation
Else
' the list has changed:
n = StrComp(.Formula1, DropList, vbTextCompare)
End If
' reset the validation if the list has changed
' or if there was no validation:
If n Then
With .Validation
On Error Resume Next
.Delete
On Error GoTo 0
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=DropList
.InCellDropdown = True
' to suppress an input message set its value to ""
.InputTitle = "Select"
.InputMessage = "Choose a sheet to open"
.IgnoreBlank = True
.ShowError = True
.ErrorTitle = "Invalid entry"
.ErrorMessage = "Please select a month from the list"
End With
Application.EnableEvents = False
.Value = Split(DropList, ",")(0)
Application.EnableEvents = True
End If
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' 01 Sep 2017
With Target
If .Address = Range(DropDownAddress).Address Then
On Error Resume Next
Worksheets(.Value).Activate
If Err Then
MsgBox "There is no sheet by the name of """ & .Value & """.", _
vbInformation, "Missing worksheet"
Else
' you can choose any cell to activate
' or delete this line of code entirely
' (along with the preceding "Else"):
ActiveSheet.Cells(3, "A").Select
End If
End If
End With
End Sub
To find the correct code sheet press Alt+F11. That opens the VB Editor. On the left top you should see the 'Project Explorer' window with the name of your workbook listed as "VBAPrtoject ([Name of workbook])". If this window isn't open press Ctl+R to open it. If you don't see all of your worksheets listed click on the + signs next to the "VBAProject" and "Microsoft Excel Objects" to open the list. Double-click on the name of the worksheet you want to have your dropdown on. That name will now be underlaid light grey (to show that it is selected), and you can paste the code in the empty pane on the top right of your screen. If the pane isn't empty, make sure that "Option Explicit" appears only once after you paste the code.
The code has two parameters at the top which you must set in accordance with the instructions written next to them. It also has two procedures, both of them so-called event procedures, meaning they will run automatically when something happens in your workbook.
The first procedure will run every time you select the sheet with the dropdown. It will install the dropdown in its designated cell. Never try to amend the dropdown in the cell. Make modifications in the code. To force the code to update your validation, delete the validation in the cell, select another sheet and then select the one with the dropdown.
The second procedure runs every time you make any change on that sheet. If the change was in the dropdown it will open the worksheet specified in the dropdown. If a worksheet by that name doesn't exist it will tell you that there is no such sheet.