• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Hyperlink with Auto Shape ? [SOLVED]

C

Cruiser

Member
I have a dynamic hyperlink in A1 created via a formula based on 2 DV lists as follows:

=HYPERLINK("[&$B$1&".xls]"&$C$1&"!Link","Goto")

1. A1 contains a DV list of workbook names.
2. B1 contains a DV list of worksheet names.
3. Every worksheet in workbook contains a single-cell named-range named "Link" which is the default home position for that worksheet.

This method works perfectly for my application by offering a user definable hyperlink. I do not want to change this (ie DV list based on another DV list, etc). What I have is exactly what I want... except...
By doing this via a formula in a cell, I cannot protect the formula without checking "Select Locked Cells" when protecting the worksheets. Can't have that, but, if I leave it unlocked it is vulnerable. You also have the problem of the user clicking on a locked cell. That selects my unlocked hyperlink, sending the user, unintentionally, to a different workbook. Of course, if I lock the cell, the hyperlink does not function. What I would like to do is attach this same formula to an autoshape that I can format as I like, but eliminate the problems of having the dynamic hyperlink formula in a cell. How would I write VBA code for the above formula so that I can create a macro to assign to an autoshape?

I am open to other ideas, as long as:
1. The resulting hyperlink is dynamically based on the 2 DV lists
2. I have full control over the design format (as with autoshape)

Also note that I have tried recording a macro by typing in the formula, but it won't take.
 
NARAYANK991 said:
Hi ,

See the file for one way of doing it ; there are alternative ways too.

Narayan
Click to expand...
Thanks, but I am not sure how to use that example to include my formula. I have a named range "Link" in each worksheet and the 2 DV lists are used to determine which workbook and worksheet to link to.
 
Initially, this seemed to work, but upon further testing, there is a fatal flaw:

1. Put the hyperlink formula in a different cell (to be hidden later).
2. Create a new autoshape.
3. Assign new macro to the autoshape
4. Hit record, click on the hyperlink formula cell, and then stop recording.
5. Hide the hyperlink formula.

Problem: The macro disregards the dynamic aspect of the hyperlink formula. When the macro is recorded, it is coded to hyperlink to the workbook and worksheet selected in the 2 DV lists at the time of macro recording. Subsequently changing the 2 DV list selections does not change the original hyperlink destination. Can I write the code so that the macro activates the current hyperlink formula, in the hidden cell, based on the 2 DV list selections at the time (anytime) I click on the autoshape?

Second, correction to original post:

1. B1 contains a DV list of workbook names.
2. C1 contains a DV list of worksheet names.
 
Last edited:
Hi ,

See if this is acceptable ; if not , I'll see whether the macro can have the formula to goto the selected sheet.

Note that there is no error checking , and it is assumed that the workbooks are open when the link is clicked.

Narayan
 

Attachments

Hi ,

This uses another method , where by changing the text in cells B1 , C1 , the Worksheet_Change event procedure does what is required. You do not need to click on any hyperlink.

Narayan
 

Attachments

NARAYANK991 said:
Hi ,

This uses another method , where by changing the text in cells B1 , C1 , the Worksheet_Change event procedure does what is required. You do not need to click on any hyperlink.

Narayan
Click to expand...
Thanks again. Great method for a different application, but unfortunately it won't work for mine. I do need a hyperlink, which must be clicked, just as in the example I uploaded. What I have works exactly as I need except that I can't find a way to protect it.
 
NARAYANK991 said:
Hi ,

You have not commented on the first file I uploaded.

Narayan
Click to expand...
That one gave me an error, but your note "assumes other workbooks are open" shows me that it is not the same result as mine. In the example I sent you, if the hyperlink is to an unopened workbook, clicking opens it. I am looking for a way to do exactly what I am doing now, but in a protected way. Thanks.
 
NARAYANK991 said:
Hi ,

See if this is OK.

Narayan
Click to expand...
Cool, thanks. I copied the autoshape to the other two tabs and changing the worksheet selection with the DV list does work. However, I also copied the workbook twice so I have WB's 1,2 &3, but clicking the autoshape does not change workbooks based on my workbook DV list selection.
 
Hi ,

Please check that when the macro is assigned to the autoshape in the second and third workbooks , it is assigned correctly.

Are you saying that you made two copies of the workbook Flintstone_1.xls , and then renamed them to Flintstone_2.xls and Flintstone_3.xls ?

Narayan
 
NARAYANK991 said:
Hi ,

Please check that when the macro is assigned to the autoshape in the second and third workbooks , it is assigned correctly.

Are you saying that you made two copies of the workbook Flintstone_1.xls , and then renamed them to Flintstone_2.xls and Flintstone_3.xls ?

Narayan
Click to expand...
To your question: Correct, I now have three workbooks just as in the example I posted.

The macro in the autoshape functions the same in all 3 workbooks. It links to the worksheet DV list selection in C1, but not the workbook DV list in selection in B1. Thanks.
 
NARAYANK991 said:
Hi ,

Sorry , but it was a mistake in the code.

See this file , and when copying , make copies of this file.

Narayan
Click to expand...
Works like a charm. It seems that the only compromise is that we are married to the text in P1, but I can format that as desired, and by limiting the size of the autoshape, only "Go To" shows. I can live with that! So I will hide P1 and I believe we are all set. I Can't thank you enough Narayan. Is there a way on the forum to mark the question as solved or to laud your Excel Ninja prowess?
 
NARAYANK991 said:
Hi ,

Thanks. You have already done so by clicking on Like !

I'll add the word SOLVED to the thread title.

Narayan
Click to expand...
Sorry, one last hurdle... I can change the DV lists to those from my actual project and the link works perfectly. But when I paste the code into a new macro in my real project, it doesn't work. I must be going about it wrong. What is the proper way to get your new code into a different workbook?
 
Hi ,

I assume that you will have already defined the named range Link in your new workbook(s).

The code itself , which I am reproducing below , needs to copied in its entirety , to a new code module in your new workbook. Press ALT F11 to bring up the VB editor , click on Insert , and then on Module. Copy the code into this newly inserted code module.
Code: 
Const CONSTANTTEXT = "Go To Worksheet tab "

Sub RoundedRectangle1_Click()
    Dim wbkisopen As Workbook
    insidetext = Replace(ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text, CONSTANTTEXT, "")
    wbkname = Mid(insidetext, 2, InStr(1, insidetext, "]") - 2)
    shtname = Right(insidetext, Len(insidetext) - InStr(1, insidetext, "]"))
    On Error Resume Next
    Set wbkisopen = Workbooks(wbkname)
    On Error GoTo 0
  
    If wbkisopen Is Nothing Then
       wbkname = Left(wbkname, InStr(1, wbkname, ".") - 1)
       Set wbkisopen = Workbooks.Open(ThisWorkbook.Path & "\" & wbkname)
    End If
  
    wbkisopen.Worksheets(shtname).Activate
    Range("Link").Select
End Sub
Narayan
 
Thanks. Somehow I wasn't picking up the line:
Const CONSTANTTEXT = "Go To Worksheet tab "
All good now. Thanks again!
 
You must log in or register to reply here.
Back
Top