Hyperlink different worksheet from Drop Down menu list, without VB?

[Sudeepkm73]

Dear Sir,

Greetings!

Kindly help me regarding hyperlink while selecting a worksheet name from Drop Down list.
Example :

I have a workbook which contain the name and detail of vendors. In first worksheet (Activity) I have created a Drop Down list containing the names of all the vendors. I have different worksheet for each vendors with their activities. If I select the vendor name from the Drop down list it should go to that respective vendor's worksheet in cell A1.

I am not so comfortable with VB scripts so kindly help me out any other way where I can do the following point. I have tried the obvious solution of making the hyperlinks, but that doesnt work. Im lost on this one, and would appreciate any help. Thanks.

Regards,
Sudeep Kr. Mazumder

 

[Fishboy]

Dear Sir,

Greetings!

Kindly help me regarding hyperlink while selecting a worksheet name from Drop Down list.
Example :

I have a workbook which contain the name and detail of vendors. In first worksheet (Activity) I have created a Drop Down list containing the names of all the vendors. I have different worksheet for each vendors with their activities. If I select the vendor name from the Drop down list it should go to that respective vendor's worksheet in cell A1.

I am not so comfortable with VB scripts so kindly help me out any other way where I can do the following point. I have tried the obvious solution of making the hyperlinks, but that doesnt work. Im lost on this one, and would appreciate any help. Thanks.

Regards,
Sudeep Kr. Mazumder

Hi Sudeep, welcome to the boards.

The easiest way to do this is going to be using simple VBA. To add this into your workbook simply right-click on the tab for the first worksheet (Activity) and select 'View Code'. On the VBA developer window that opens copy and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)    
     If Range("[COLOR=#ff0000][B]A2[/B]"[/COLOR]).Value = "[COLOR=#ff0000][B]Sheet2[/B][/COLOR]" Then
        Sheets("[COLOR=#ff0000][B]Sheet2[/B][/COLOR]").Select
        ElseIf Range("[COLOR=#ff0000][B]A2[/B][/COLOR]").Value = "[COLOR=#ff0000][B]Sheet3[/B][/COLOR]" Then
        Sheets("[COLOR=#ff0000][B]Sheet3[/B][/COLOR]").Select
        ElseIf Range("[COLOR=#ff0000][B]A2[/B][/COLOR]").Value = "[COLOR=#ff0000][B]Sheet4[/B][/COLOR]" Then
        Sheets("[COLOR=#ff0000][B]Sheet4[/B][/COLOR]").Select
     End If
End Sub

The parts in red bold can be amended to suit your needs. The A2 value is whatever cell your drop-down list is in.

The sheet names are whatever the tabs for your vendors are called.

If you have more than 3 vendors simply make another copy of the last 2 lines before the End If and amend as necessary.

 

[Sudeepkm73]

Hi Sudeep, welcome to the boards.

The easiest way to do this is going to be using simple VBA. To add this into your workbook simply right-click on the tab for the first worksheet (Activity) and select 'View Code'. On the VBA developer window that opens copy and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)    
     If Range("[COLOR=#ff0000][B]A2[/B]"[/COLOR]).Value = "[COLOR=#ff0000][B]Sheet2[/B][/COLOR]" Then
        Sheets("[COLOR=#ff0000][B]Sheet2[/B][/COLOR]").Select
        ElseIf Range("[COLOR=#ff0000][B]A2[/B][/COLOR]").Value = "[COLOR=#ff0000][B]Sheet3[/B][/COLOR]" Then
        Sheets("[COLOR=#ff0000][B]Sheet3[/B][/COLOR]").Select
        ElseIf Range("[COLOR=#ff0000][B]A2[/B][/COLOR]").Value = "[COLOR=#ff0000][B]Sheet4[/B][/COLOR]" Then
        Sheets("[COLOR=#ff0000][B]Sheet4[/B][/COLOR]").Select
     End If
End Sub

The parts in red bold can be amended to suit your needs. The A2 value is whatever cell your drop-down list is in.

The sheet names are whatever the tabs for your vendors are called.

If you have more than 3 vendors simply make another copy of the last 2 lines before the End If and amend as necessary.

Dear,

Greetings!

Thanks for the valuable reply. I have few more queries, like as I mentioned I am not so comfortable to use VB so is there any other way to do the process.

Over that I have already created a VB Script in the same worksheet for receiving few data from multiple worksheets to this worksheet (Activity), like if any activity has been done by vendor 2 then while entering the data in vendor 2 worksheet it adds two columns and the vendor's name in the Activity sheet, so in that case how to add / edit your valuable VB script in the same VB module.

Kindly suggest.

 

[Fishboy]

Dear,

Greetings!

Thanks for the valuable reply. I have few more queries, like as I mentioned I am not so comfortable to use VB so is there any other way to do the process.

Over that I have already created a VB Script in the same worksheet for receiving few data from multiple worksheets to this worksheet (Activity), like if any activity has been done by vendor 2 then while entering the data in vendor 2 worksheet it adds two columns and the vendor's name in the Activity sheet, so in that case how to add / edit your valuable VB script in the same VB module.

Kindly suggest.

Hi again Sudeep,

As far as I am aware VBA is your only real option here as you are wanting the workbook to intelligently switch to another sheet when a value on your Activity sheet changes (the value in the drop-down box). The only other method involves hyperlinks which would involve you needing to click on the link to have the active sheet changed.

With regards to merging your existing code with my suggestion, please can you post your code here so we can look at it? Please ensure when posting your code to wrap it in the correct tags to help keep the forum posts clutter free and to help make the code easier to identify.

[ CODE ] (without spaces) at the start of the code
[ /CODE ] (without spaces) at the end of the code

 

[Sudeepkm73]

Hi again Sudeep,

As far as I am aware VBA is your only real option here as you are wanting the workbook to intelligently switch to another sheet when a value on your Activity sheet changes (the value in the drop-down box). The only other method involves hyperlinks which would involve you needing to click on the link to have the active sheet changed.

With regards to merging your existing code with my suggestion, please can you post your code here so we can look at it? Please ensure when posting your code to wrap it in the correct tags to help keep the forum posts clutter free and to help make the code easier to identify.

[ CODE ] (without spaces) at the start of the code
[ /CODE ] (without spaces) at the end of the code

Dear Sir,

Greetings!

Thanks again for valuable suggestion. I really appreciate your early reply. Hereby I am posting my code for further help from your side.

Sub DataCopying()
Dim sheetName As String
Dim lastRow As Long
Dim activeRow, indexSrno As Long
Dim copyCellRange As String




sheetName = ActiveSheet.Name


If sheetName <> "Activity" Then

activeRow = ActiveCell.Row 'Active Vendor Sheet current Row

lastRow = Sheets("Activity").Range("B65536").End(xlUp).Row + 1 'Last Row on Activity Sheet

copyCellRange = "B" + Trim(Str(activeRow)) + ":C" + Trim(Str(activeRow)) + ":D" + Trim(Str(activeRow)) 'Data Copy Cell Range

'Serial No Generation on Activity Sheet
If lastRow = 2 Then
indexSrno = 1
Else
indexSrno = Sheets("Activity").Range("A" & (lastRow - 1))
indexSrno = indexSrno + 1
End If

'Data Copying
Sheets("Activity").Range("A" & lastRow) = indexSrno
Range(copyCellRange).Copy Destination:=Sheets("Activity").Range("B" & lastRow)
Sheets("Activity").Range("E" & lastRow) = sheetName

MsgBox "Data from Row No:" + Trim(Str(activeRow)) + " of Sheet(" + sheetName + ") Copied onto Row No:" + Str(lastRow) + " on Sheet(Activity)"

'Sheets("Activity").Activate
End If
End Sub

As per your other method involves hyperlinks which would involve me needing to click on the link to have the active sheet changed, it will nice of you if you can suggest the hyperlinks. I have already created a GO button next to Drop Down Menu (G1) in Activity worksheet, there as per the hyperlink after selecting the vendor name from the list if I left click the GO button it goes to the same worksheet where the name is listed for making Data validation, but how it should go to other worksheet.

Waiting for your valuable suggestion.

Regards.
Sudeep Kr. Mazumder

 

[Fishboy]

Dear Sir,

Greetings!

Thanks again for valuable suggestion. I really appreciate your early reply. Hereby I am posting my code for further help from your side.

Sub DataCopying()
Dim sheetName As String
Dim lastRow As Long
Dim activeRow, indexSrno As Long
Dim copyCellRange As String




sheetName = ActiveSheet.Name


    If sheetName <> "Activity" Then
        
        activeRow = ActiveCell.Row  'Active Vendor Sheet current Row
        
        lastRow = Sheets("Activity").Range("B65536").End(xlUp).Row + 1    'Last Row on Activity Sheet
               
        copyCellRange = "B" + Trim(Str(activeRow)) + ":C" + Trim(Str(activeRow)) + ":D" + Trim(Str(activeRow)) 'Data Copy Cell Range
        
        'Serial No Generation on Activity Sheet
        If lastRow = 2 Then
            indexSrno = 1
        Else
            indexSrno = Sheets("Activity").Range("A" & (lastRow - 1))
            indexSrno = indexSrno + 1
        End If
        
        'Data Copying
        Sheets("Activity").Range("A" & lastRow) = indexSrno
        Range(copyCellRange).Copy Destination:=Sheets("Activity").Range("B" & lastRow)
        Sheets("Activity").Range("E" & lastRow) = sheetName
        
        MsgBox "Data from Row No:" + Trim(Str(activeRow)) + " of Sheet(" + sheetName + ") Copied onto Row No:" + Str(lastRow) + " on Sheet(Activity)"
        
        'Sheets("Activity").Activate
    End If
End Sub

Whereabouts in your workbook is this code? Is it applied directly to a sheet or is it applied to ThisWorkbook? If it is the latter then you should still be able to use the code from my original post directly by right-clicking the Activity tab, selecting 'View Code' and pasting it in. It should not interfere in any way with your code from the ThisWorkbook section.

As per your other method involves hyperlinks which would involve me needing to click on the link to have the active sheet changed, it will nice of you if you can suggest the hyperlinks. I have already created a GO button next to Drop Down Menu (G1) in Activity worksheet, there as per the hyperlink after selecting the vendor name from the list if I left click the GO button it goes to the same worksheet where the name is listed for making Data validation, but how it should go to other worksheet.

Alternatively if you would prefer to go down this route where the link can be clicked, remove your GO button and replace it with the following formula in the cell instead (assuming the drop-down box populates A2):

=HYPERLINK(A2)

The above basically reads the selected value from the drop-down box in A2 and creates a hyperlink of the same sheet name in B2. Using this method would replace my original VBA suggestion but would require you to click the link to get the sheet to change.

 

[charles1994]

Thanks.

The scripts above are very useful and simple, but if my workbook is in the company server, how can the A2 value jump to my specific worksheet of workbook? like below:

Sheets("c:\UsersLocal\Desktop\AlanCole.xlsx\Enquiries").Select

Thanks
Charles