Problems with dynamic named range - hyperlinks to trigger macro to change drop down list

hi all, good day!

this is a bit complicated, i'll try to explain as clearly as possible.

Referring to the attached sample, I have a range across rows and columns, in my sample would be F6:I10.

Each row in the range represents a hyperlink eg F6:I6 would be hyperlink 1. F7:I7 would be hyperlink 2.... and so on....

What I wish to do is, when the users click on hyperlink 1 it will trigger the macro where it changes the drop down list according to the numbering of the row, e.g. the first row in my range = F6:I6 would be row 1, thus the drop down would change to 1 when the hyperlink is clicked.

Currently, I do it in a very long-winded way where I write out all the codes for each range. (refer to attached sample)

However, in my actual data, I have currently 20 rows and possibly more in the future.
I have to modify the codes every time I added new rows or columns in my sheet.

I thought of defining the range by naming the upper left and lower right cells in the range to make it dynamic, but I don't know how to make the rest of the codes working...

My intended result is hoping to have a macros which is dynamic where I don't have to modify the whole thing should I added rows and columns in the sheet or in the range itself.

Appreciate any kind assistance.

Thank you.

hi, thank you for your reply!

I tried the codes given and it worked.

However, it doesn't work on another sheet(?). I already changed the necessary such as the name of the sheet. It gives me run time 1004: unable to set the listindex property of the drop down class.

Also, does it work if i have other hyperlinks in the same sheets when the hyperlinks are not within the 'range'?

Thank you!

hi,
is it possible to use named range and return the row of the range?

something like

Please Login or Register  to view this content.

of course, the above codes are not working....

Hi,

I figured out something like this which is more dynamic.
The only problem left is to trigger this macro only if the users click the hyperlink in the named range "Table". When users click on other hyperlinks in the same sheet, it wont trigger the macros.

Appreciate if anyone could help.

thank you!

Please Login or Register  to view this content.

hi thanks for your reply...

it's not working properly.
yes, when i click on the other two links outside the range, it does not cause error anymore.
however, instead of changing the "drop down 1", it changes another drop down list. Also, when i click on row 8 and above in the "Table" range, it gives error 1004....

hi,

i change me. to name of sheet...

Please Login or Register  to view this content.

and it works flawlessly! (keep my fingers crossed)

thank you very much for your kind assistance! you really saved me a lot of trouble.
hopefully i won't have to bother you again with the same question.

Thank you!
god bless you.