PDA

View Full Version : Hyperlink - Parent and Child



iyerarunkuma
01-19-2007, 12:56 AM
Hi,
I have 2 Sheets. Test and Demo. Col AccA of Test matches with Col AccA of Demo. When there is a match I want to put AccA, AccB, AccC,AccD in a Sheet named Google.

I have the code for creating hyperlinks. Hyperlinks will be created for AccC of Demo Sheet.

When I move my cursor on AccA of Test Sheet, it should be able to match AccA of Demo Sheet and show the number of children(AccC). if 5 are present it should be like

xxxxxxxx
aaaaa
aaaaa
aaaaa
aaaaa
where xxxxxx is the first value of Test Sheet.
aaaaa are the values resultant of match between xxxxx of Test and xxxxx of Demo.

I am pretty confused with this. Is it possible?

Regards,
Arun.:banghead:

Charlize
01-19-2007, 05:30 AM
What do you mean ? If column A of demoworksheet matches an entry in column A of testworksheet ? If so, column A, B, C, D, E of demoworksheet must be copied to a googlesheet ?

Charlize

iyerarunkuma
01-19-2007, 05:42 AM
Yes.
I cannot put a user form for this Macro. I want the user to click on Column A in Test, this should be captured in a variable, compare it with the Demo Worksheet and paste all the four column of Demo Sheet in Google.
I have written the code for them, but it doesnot work.

2nd Part.
I wish to put a hyperlink for the values in GoogleSheet and when I double click on the cell A1 in Testsheet, it should show me the corresponding hyperlinks available for that value.

Please let me know how to do it. I have attached the Workbook with this.
I am newbie to VBA.

Charlize
01-19-2007, 06:07 AM
1 part : I don't believe there is a one_click event. There is a doubleclick event for the 2nd part. Is it worksheet change that you want ? Code will run on every change. Can be delimited to a certain area. Must the first part be run after everything is filled in or during the proces of filling in ?

2 part : in a messagebox or what ?

Charlize

iyerarunkuma
01-19-2007, 06:31 AM
Hello Charlize,
Thanks for the quick reply. Did you ever get a chance to run the code in the Workbook.

When I click on A1 in testsheet, it takes the value and compares them using Target.address. I am able to copy cells(2,1) and cells(2,2). I am however unable to copy value in Cells(2,3) to a variable. I tried commenting the code and trying to fetch only cells(2,3) in a messagebox. However I was unsuccessful.
After the above activity of copying is over it should invoke a messagebox asking user to doubleclick on the cell again and Step 2 should start.

Step2: For the second part, I want is in a messagebox. The cell value of A1 in Testsheet may be have 4 matching hyperlinks in demosheet. So when I dblclick on A1 in testsheet, I should get a messagebox which shows the 4 matching hyperlinks and I should be able to click each hyperlink and go to the column.

The thing I want is actually being pretty complex in explaining.

iyerarunkuma
01-19-2007, 06:39 AM
Else if someone can get me a code like this.

1) User clicks on A1.Value of test sheet is stored in a variable.
2) Value is checked with A1 in Demo Sheet.
3) if they match, values in C1 column are populated in a messagebox.
4) These will have hyperlinks.
5) These values will form Captions on the messagebox. like a Label Caption.
6) I can click on them and check the values.

Please try if this atleast is possible. I just got this turnaround, when I am typing for help. I will also work and keep you posted if I find a Solution.

Charlize
01-19-2007, 06:41 AM
past this in the worksheet_doubleclick of testsheet and doubleclick a value in column A.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then
Exit Sub
Else
'result to display in messagebox
Dim value As String
'used to search for matching value
Dim cell As Range
'last row of data
Dim lrow As Long
'no of items found
Dim no As Long
'message to display in messagebox
value = "Requested info ..." & vbCrLf
no = 1
lrow = Worksheets("Demo").Range("A" & Rows.Count).End(xlUp).row
For Each cell In Worksheets("Demo").Range("A2:A" & lrow)
'look for matching values
If cell Like "*" & Target.value & "*" Then
'build the string to display
value = value & no & " a." & cell.value & "/b." & cell.Offset(0, 1).value & "/c." & _
cell.Offset(0, 2).value & "/d." & cell.Offset(0, 3).value & "/e." & _
cell.Offset(0, 4).value & vbCrLf
no = no + 1
End If
Next cell
'if no match make string up with this
If value = "" Then
value = "No info found for : " & Target.value
End If
'leave editmode
Cancel = True
'show the message
MsgBox value
End If
End Sub
Charlize

Charlize
01-19-2007, 04:47 PM
Else if someone can get me a code like this.

1) User clicks on A1.Value of test sheet is stored in a variable.
2) Value is checked with A1 in Demo Sheet.
3) if they match, values in C1 column are populated in a messagebox.
4) These will have hyperlinks.
5) These values will form Captions on the messagebox. like a Label Caption.
6) I can click on them and check the values.

Please try if this atleast is possible. I just got this turnaround, when I am typing for help. I will also work and keep you posted if I find a Solution.
1, 2, 3 ok. 3. A, B, C, D, E are added to the messagebox and this is done for so many times as value of target is found in demo
4. Hyperlinks on a messagebox ???
5. The message (I presume) for the messagebox can be made.
6. mmmmmm ... no clue what so ever. (you can not make a car out of a bike)

Charlize

iyerarunkuma
01-22-2007, 11:07 PM
Hello Charlize,

Thanks so much for your help. The code works fine just as I wanted it to!

Just one doubt. I have hyperlinks for Col C1 on Demo Sheet. When it is populated on the messagebox wont it take the hyperlinks as well. I did find one example of Hyperlinks in this forum, All the hyperlinks populated in a form in a combo box. When the user selects on one item, label forms the value of the item selected. When the user clicks on the label it takes him to the appropirate site stored as a hyperlink for that variable.

Charlize
01-23-2007, 03:38 AM
Hope this will suite your needs. Fill in some samples in test sheet with corresponding name in demo sheet. Third column of demo is hyperlink. Hyperlink needs to be filled in as www.restofit.com (http://www.restofit.com). Never use http:// before it cause it is added in the code for sites that needs this kind of string instead of www. so for http://microsoft.help.com use microsoft.help.com

Doubleclick the line in the listbox to fire the hyperlink. Be patient cause it takes several seconds ...

Charlize

iyerarunkuma
01-29-2007, 01:33 AM
Hello Charlize,

Thanks for your help. This code was useful but I have done many alterations here and there to suit the way I wanted it to.

I have used follow hyperlink and have solved it.

Thanks again.

Regards,
Arun.