hyperlink

[pskumar]

Iam trying to learn excel. My query is about hyperlink. I created a hyperlink for a portion of a workbook. But when i click on the hyperlink the entire workbook is visible though the linked text is seen in a box. It is still distracting since the entire work book is seen along with the linked portion. Is it possible to see only the linked text instead of entire workbook .

 

[bobhc]

Good day pskumar

Bit difficult to say what the problem is without seeing the hyperlink address. Could you upload the file or a sample?

 

[SirJB7]

Hi, pskumar!

Give a look at the attached file. 1st worksheet contains the hyperlinked address (D4:F6), 2nd worksheet has the HYPERLINK formula in cell A1, which I assume that it's similar to your actual one.
The workbook has code (macro) for the activate event of 1st worksheet, you can see it pressing Alt-F11 to go to the VBA editor and then double clicking on the 1st worksheet (Hoja1) entry in the left panel which will display the code in the big right pane.

This is the code:

Option Explicit
 
Private Sub Worksheet_Activate()
    ' constants
    ' declarations
    Dim rng As Range
    ' start
    Application.EnableEvents = False
    Set rng = Selection
    ' process
    '  hide all
    With Cells
        .EntireRow.Hidden = True
        .EntireColumn.Hidden = True
    End With
    '  show selection
    Application.ScreenUpdating = True
    With rng
        .EntireRow.Hidden = False
        .EntireColumn.Hidden = False
        .Cells(.Rows.Count, .Columns.Count).Select
        .Cells(1, 1).Select
    End With
    ' end
    Set rng = Nothing
    Application.EnableEvents = True
    Beep
End Sub

Just advise if any issue.

Regards!

 

[SirJB7]

@b(ut)ob(ut)hc
Hi, my friend!
Good afternoon.
You weren't here when I began writing my post, so I assume you'd still keep the habit of entering surreptitiously by the window.
Regards!

 

[Sam Mathai Chacko]

So basically, you do not want to workbook to get opened when you click the hyperlink? If you mean by giving hyperlink to a portion of a working, you mean that you want to see the value of a cell in another workbook in your active workbook, you should be using a formula instead of a hyperlink. And the formula can be a direct link to the cell. On the other hand, if you still want it to be a hyperlink, but you want the value to be displayed, why don't you use the HYPERLINK function in Excel? That way, you can not only use a link to your destination, but also in the second argument, pass the value that you want to be displayed.

Having said that, if it is a hyperlink, and you still don't want the workbook to open up when you click on the cell, then you'll need to use VBA. Post back if you need clarity, or if I've not understood your requirement correctly.

 

[Sam Mathai Chacko]

Gosh! Same case here too as bobhc. My bad :D

 

[bobhc]

Good day SirJB7 ....surreptitiously...a fine word, rolls of the tongue,.....But when you are my size you cannot do anything surreptitiously

 

[pskumar]

Good day pskumar

Bit difficult to say what the problem is without seeing the hyperlink address. Could you upload the file or a sample?

thank you for your help. two files (Hyperlink and destination file) are uploaded. when you click hyperlink the destination portion along with the entire workbook are visible. is it possible to see only the required part or any other way to properly identify the portion.

 

[bobhc]

Good day pskumay

Your hyperlink does as it should, the only difference is I would have put Sheet1! in it. If you are just wanting to display the cell with the data in that the link points to then you are going too have to use SirJB7's code and tweak the range code.

 

[SirJB7]

Hi, pskumar!
The method you're using (defining an hyperlink in workbook Book2 pointing to workbook Book1) won't work if files are moved or renamed, so you'll have to update it manually, since it isn't a normal cell/range/workbook reference that Excel will update when source changes. Using HYPERLINK function guarantees you that flexibility.
However I think you didn't read carefully my previous post as it's related to a .xlsm file and not to a .xlsx file because if has VBA code, the macro for the change event in linked worksheet of (in your case) workbook Book1. That's to say that despite of the hyperlink attributes in workbook Book2 (either fixed as you used or dynamic as I did), the linked workbook will never know that it should display only the linked range.
Regards!

 

[pskumar]

So basically, you do not want to workbook to get opened when you click the hyperlink? If you mean by giving hyperlink to a portion of a working, you mean that you want to see the value of a cell in another workbook in your active workbook, you should be using a formula instead of a hyperlink. And the formula can be a direct link to the cell. On the other hand, if you still want it to be a hyperlink, but you want the value to be displayed, why don't you use the HYPERLINK function in Excel? That way, you can not only use a link to your destination, but also in the second argument, pass the value that you want to be displayed.

Having said that, if it is a hyperlink, and you still don't want the workbook to open up when you click on the cell, then you'll need to use VBA. Post back if you need clarity, or if I've not understood your requirement correctly.

Good day pskumay

Your hyperlink does as it should, the only difference is I would have put Sheet1! in it. If you are just wanting to display the cell with the data in that the link points to then you are going too have to use SirJB7's code and tweak the range code.

thank you very much for your help.
regards,kumar