Please advice me for this freaky Hyperlink condition!

[yuvastanza]

Dear all

I am just preparing Update tracker for my personal use (with version 2007). I have created drop down list with few categories in first page or worksheet. and gave the category name to each and every worksheet. For example: if i have category called "ABC" in drop down list and there will be "ABC" worksheet in the same workbook.

My request is when i choose the category from the drop down list and clicked the name called for example "ABC", that must go to the relevant worksheet "ABC". As well as there will be other category names and it must work as the same condition also.

I prepared the work book with the above said requirements i used the (=Hyperlink) function. It is not working when the category names are in drop down list, Please help me on this if you have any IDEA!

Very Thankful to you!

Please help me on this.


Note: I need the link should be enabled in listed category names.
​

 

[Ruddles]

I think you're saying that you created a list of hyperlinks in a range of worksheet cells and then based your combo box on that range of cells, so when you click on an entry from your combo box, you're expecting it to execute the hyperlink.

Is that what you're saying?

If so, this won't work. You have to use the change event of the combo box to identify which value has been selected and then activate the appropriate sheet and cell.

 

[yuvastanza]

Hi Ruddles

Thank you for your attention on my question. I used the drop down list from Data ribbon -->Data Validation.

I could not see any options that execute these hyperlink.

Can you advice further

 

[Ruddles]

You would use the Worksheet_Change event to detect a change to the cell containing the drop-down and then activate the sheet named in that cell. There's no 'option' to do it - you have to write the VBA and add it to the Worksheet_Change event.

 

[yuvastanza]

Thank you very much Ruddles.

I will try my best with your Idea . Once done i will let you know in this page. but it will take time as i am not aware of utilizing VBA feature.

 

[Ruddles]

Sorry, okay, do this: right-click the tab of the worksheet containing your drop-down and select View Code. Look for the name of your worksheet in the VBAProject panel (top-left) and double click it. This opens up the VBA code window for the worksheet.

Paste this into the code window:-

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Target.Address(False,False) = "[COLOR=red][B]A1[/B][/COLOR]" Then
    On Error Resume Next
    Sheets(Target.Value).Activate
    On Error GoTo 0
  End If

End Sub

Change A1 to the address where your drop-down is located.

Now go back to your worksheet and select something from the drop-down. Does it switch to the correct worksheet?

 

[yuvastanza]

Hi Ruddles

My First VBA project started with you tip, it works great and really thankful to you. I like the work done by VBA project instead of depending upon the inbuilt functions in Excel.

I would like to start and develop my VBA skills, Please advice me how can i get this boon, I started here.

 

[Ruddles]

Read the posts in this forum and try to understand the simpler pieces of code and maybe try them for yourself.

Google vba tutorial and find one you like, then spend a few minutes every day learning just a few things from it.

Keep all your VBA in a separate folder so you can refer to your code when you need to.

Don't be afraid to ask!

 

[yuvastanza]

Thanks a lot for you encouraging advice.... i never hesitate to clarify my queries regarding Excel VBA from you.. i will start soon...