Need help with Hyperlink question

[studiocity]

Hi all,

My first post :hi:
i hope someone can help me if at all possible and i will do my best to explain what i'm trying to do...

Im using Excel 2003. In a column i have a reference to an external link. However this external link always changes.

So for example: studiocity dot com\35533 will be a hyperlink in one cell and cell contents will be 35533. Then the cell below will be
studiocity dot com\35534 and the cell contents will be 35535.

What i'd like is for to be able to go into the cells and just type in the 5 digit reference number and it hyperlinks to the URL.

Is there a way of doing this please?

Any help would be much appreciated.

thanks! :thumb

[Bob Phillips]

Add this worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$5" Then

Target.Hyperlinks.Add anchor:=Target, Address:="studiocity%20dot%20com\" & Target.Value
End If
End Sub

[studiocity]

Wow, many thanks for the super fast reply!

But how do i add a worksheet event code? :wot

[Bob Phillips]

On the target worksheet, right-click, select View Code, and paste the code in the window that pops up.

[studiocity]

I have done that...But what is supposed to happen?

[studiocity]

I enabled macros but when i type in the 5 digit number and click on the cell nothing happens?

[Bob Phillips]

Then you cannot have done it correctly; I tested it and it worked.

[studiocity]

Could you please e-mail me a sample so i can compare?

Many thanks

[Bob Phillips]

I suggest that you post the non-working version that you have, with the code.

[studiocity]

I have attached the file.
Basically in the link section i have used imdb as an example.

[Bob Phillips]

My code used C5 as the target cell, you actually use I17, so you need to change the code to reflect that.

[studiocity]

Thankyou.
What if i want the cells in a whole column...How would i reflect that in the code please?
So for example the column in red in the attached file?

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
Target.Hyperlinks.Add Anchor:=Target, Address:="studiocity%20dot%20com\" & Target.Value
End If
End Sub

[studiocity]

Hi there,
Many thanks for the great help. That all works.
However there are a few things that i'm not sure about.

The first is that when you delete the contents of the cell you get the text that randomly appears in cell I22 or for that matter any cell that you delete the contents of.

Lastly when you type in the 7 digit reference the font size reverts to 10 even though the cell is formatted for font size 8?
once again many thanks for all your help :)

[Bob Phillips]

Best to explicitly force each issue


Private Sub Worksheet_Change(ByVal Target As Range)
With Target

If .Column = 9 Then

If .Value = "" Then

.Hyperlinks.Delete
Else
.Hyperlinks.Add Anchor:=Target, Address:="http://www.imdb.com/title/tt" & Target.Value
.Font.Size = 8
End If
End If
End With
End Sub

[studiocity]

Absolutely amazing...Thankyou sooooooooooooooooooooooooooooo much

[studiocity]

I have one other question if i may please?

In the attachment i have three worksheets and a column called 'Resolved' If you click on the cell you can either choose Yes (turns the cell green) or No (turns the cell red).

So sheet 2 the cells are red (No) and sheet 3 the cells are green (Yes) If for example all the values are either green or red is it possible that the cells in sheet 1 automatically change colour to reflect the overall values.

So if any cells are red then that will be the prevailing value and would show in sheet 1?

[Bob Phillips]

So you want Sheet1!C13 to be red if there are no greens in Sheet2!H:H, green if no reds?

[studiocity]

Yes, if thats possible!?? So essentially in the column H any reds it should be red. Only time it should be green if its all green.

[studiocity]

Hi xld, i was wondering if you had a chance to look at the problem i had?

[Teeroy]

Hi Studiocity,

I think what you need is:


Sub test()

Dim testRng As Range
Dim tst As Range
Dim red As Boolean

red = False

Set testRng = Sheets("sheet2").Range("H:H")
For Each tst In testRng
If tst.Interior.ColorIndex = 3 Then
red = True
Exit For
End If
Next

If red Then
Sheets("sheet1").Range("c17:c17").Interior.ColorIndex = 3
Else
Sheets("sheet1").Range("c17:c17").Interior.ColorIndex = 4
End If

End Sub

Remember that this test is for the basic palette colour red. If you use a more advance palette colour you'll need to use .Interior.Color and a different value to test for and you may cause errors if you try to use the spreadsheet on pre-2007 excel.

[studiocity]

Hi Teeroy,

Apologies for the delay. I don't think that is working. I have attached the xls file, i was wondering if you could make the changes to it so i can see how it works please? Many thanks

Also XLD, on sheet 2, column I i have an imdb hyperlink...so i can just type in the last digits of the reference for a movie and it hyperlinks to the URL.

If i want to do the same in columns M & O what would i need to do please?

Many thanks in advance...

[Teeroy]

Hi Studiocity,

Modifying XLD's code to suit multiple columns is quite easy. See below.


Private Sub Worksheet_Change(ByVal Target As Range)
With Target

If .Column = 9 Or .Column = 13 Or .Column = 15 Then
If .Value = "" Then
.Hyperlinks.Delete
Else
.Hyperlinks.Add Anchor:=Target, Address:="http://www.imdb.com/title/" & Target.Value
.Font.Size = 8
End If
End If
End With
End Sub

You had not mentioned previously that you were setting colors via conditional formatting. The code I gave you previously won't work for that as the interior formatting of the cell isn't actually changed. It's quite complex to test whether the conditional formatting is in place so for your example where it's based upon a validated list it's easier to test the value see below. I suggest you place this in the Worksheet_Activate even of "Sheet1" and it would run whenever you select the sheet tab.
Sub test()

Dim testRng As Range
Dim tst As Range
Dim red As Boolean

red = False

Set testRng = Sheets("sheet 2").Range("H:H")
For Each tst In testRng
If tst.Value = "No" Then
red = True
Exit For
End If
Next

If red Then
Sheets("sheet1").Range("c13:c13").Interior.ColorIndex = 3
Else
Sheets("sheet1").Range("c13:c13").Interior.ColorIndex = 4
End If

End Sub

[studiocity]

Hi Teeroy,

Many thanks for that...

I still cannot get the conditional formatting to work. Would it be possible for you to attach a working xls so i may take a look?

Many thanks in advance :)

[Teeroy]

A copy of your spreadsheet is attached that does the hyperlinks. It also checks "Sheet 2" for "No" values and colors c13 in "Sheet1". The code is in the Worksheet_Activate event of "Sheet1".

[studiocity]

That's absolutely great!!!
I will work on this.

Also going back to the original question i had.
I have attached a spreadsheet with hyperlinks in the various columns.
Its an imdb link. So if i type in 468569 in the cell (F9) it will hyperlink to the URL.

That works fine. But when i delete the contents i get the error 'Run-time error 13 - Type mismatch.

In addition if i share the workbook the hyperlinks do not work and if you goto view code the error project is unavailable is generated...

Any help would be greatly appreciated. I have attached the spreadsheet.
Thanks Teeroy :)

[Teeroy]

Sorry, can't help you. When I looked at your attached file it was blank (values) and the code was not viewable. This may be a contributing factor.

I have not used shared files in excel but a quick check says that it is unstable and buggy in excel 2003. If you're using excel 2010 then it's shared via SharePoint and that's done nothing for me except make me want to toss computers through a window!

[snb]

Avoid any merged cells when working with VBA.
Avoid shared workbooks if you value integrity of the data in the workbook.

[studiocity]

Hi Teeroy,

Please see the attached file.
I have unshared the document so you can see now.

I hope you can help :friends:

[Teeroy]

Avoid any merged cells when working with VBA.
Avoid shared workbooks if you value integrity of the data in the workbook. You only followed one part of SNB's post by unsharing the spreadsheet. When I unmerged columns F & G it worked correctly. Merged cells can cause very strange responses from VBA.

In your case the error is understandable as the target (merged cell) for the Worksheet_Change event is actually range F9:G9. A multicell range object cannot have a .Value property and so it throws an exception.