• 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.

Help greatly appreciated! Displaying excel table in a pop up window

Status
Not open for further replies.
P

pkrish

New Member
Hi Everyone,


I was wondering if there is a way in excel-vba to do this.


In sheet A, I have 10 tables. Each table is assigned a table name and has two columns (a list of fields in column 1 and the corresponding data types in column 2). I have added a hyperlink to the cell which contains the table name ('Sheet A'! C 2:D 2) . When you click on the link, it takes you to Sheet B and the highlights the range where the actual table exists ('Sheet B'! I 18:K 25).


I was wondering if there is a way by which the table 'Sheet B'! I 18:K 25 can be displayed in pop-up window when the hyperlink ('Sheet A'! C 2:D 2) is clicked.


Any other ideas to display the table in a pop-up window also welcome!


Thanks for your help,

PK
 
PK


Firstly, Welcome to the Chandoo.org Forums


You can do what you want by adding a UserForm in VBA

The Userform will have a ListBox control as well as an Ok button


You simply load the Userform by using the

Code: 
Me.ListBox1.List = Worksheets("Sheet B").Range("I18:K25")

Command in the Userform_Initialise event.


Can you post a sample file so that we can see how to change it to work with multiple ranges?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
PK,


Hui's option will look more elegant for sure. Here's one more option you can try.


It is based on the following:

You have set Hyperlinks to the whole Range and not to top corner cell.


Implementation:

1. Select the sheet which has hyperlinks.

2. Right click on its tab and select "View Code" and then paste this code.

[pre]
Code: 
Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

'Delete if this shape exists
On Error Resume Next
Shapes("MyShape").Delete
On Error GoTo 0

'Copy Hyperlink range
Selection.Copy

'Paste it as picture and name it so as to delete next time
With Sheets(Target.Range.Parent.Name)
.Select
Target.Range.Offset(0, 1).Select
.Pictures.Paste.Name = "MyShape"
Application.CutCopyMode = False
End With

End Sub
[/pre]
 
Hui and Shrivallabha,


Many thanks to both of you. I really appreciate your help!


Hui:


I tried your method. However I kept getting this error "Run time error 381: invalid array property". Could you please tell me how to fix this?

Also, I have uploaded a sample file.Could you please tell me how to work with multiple tables?

https://hotfile.com/dl/193953709/839fb53/Sample_file_pk_chandoo(1).xlsx.html


shrivallabha:

I ran your code. the picture of the destination table was getting pasted on the source table and information from both the tables was overlapping. So it was difficult to see which row belonged to which table. Nevertheless, I really appreciate your help. I definitely learnt something new .


Thanks a lot !

PK
 
Pk


Have a look at your file here

https://www.dropbox.com/s/lxt8tkh6tzgpxcp/Sample_file_pk_Hui.xlsm
 
hi,
i need help generating a popup window(table) in excel. i want a window to popup displaying everything within a specific range say from A1:X30 from a different sheet in excel when i click on a command button. i tried with msg box but not everything showed. i need your help. anyway sir am not good with VBA for excel.
Regards.
 
Status
Not open for further replies.
Back
Top