Hi ,
I was looking at a similar problem just recently. This Thread did not help too much as no solutions in it worked.
But, ( I guess due to the well descriptive Title ) I kept hitting on this Thread toime and time again while I was searching!!!.
So I thought it would be a benefit to others if i dropped off one solution that I now have.
I never managed to do any formula to get over the problem. Fundamentally there does not seem to be a workaround to get over the 255 Formula limit. (
As it is actually the “255” that Excel “sees” that is the problem, you cannot seem to do any splitting then joining as suggested here, as Excel still ends up “seeing” .
Further it appears not possible to change a cell value with a Function, so another often suggested approach, using a User Defined Function, UDF, also fails at that fundamental point.
The best I have is a modification of a VBA solution workaround which someone did.
Brief Details of Solution
In brief an Events (
Worksheet_Change ) Program sets off a code that will change a text in a cell to a hyperlink. That is basically what my Excel seems to do anyway if the text “looks” like a URL. Except it does not work if the text is over 255 (
I note
_a) in passing that for a formula it is still exactly a 255 limit. – It is strings within formulas that have that limit
and
***_ b) note also in passing that a 255 limit in addition applies to any string it pastes out )
So I give a simple code first of all that will make a sheet convert URL’s of over 255 characters that are entered into any cell convert automatically to a Hyperlink in a similar way to what Excel usually does.
Then I include a more detailed code and explanation for the unlikely case that someone with less VBA knowledge than me pops by....
(
I was a bit surprised that my codes do not set off an infinite loop of the Worksheet_Change code !? !? )
Code
Code:
[color=blue]Private[/color] [color=blue]Sub[/color] Worksheet_Change([color=blue]ByVal[/color] Target [color=blue]As[/color] Range)
[color=blue]Dim[/color] strURL [color=blue]As[/color] String: strURL = Target.Value
[color=blue]If[/color] [color=blue]Not[/color] Intersect(Cells, Target) [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color]
Target.Hyperlinks.Add Anchor:=Target, Address:=strURL, ScreenTip:=strURL, TextToDisplay:=strURL
[color=blue]End[/color] [color=blue]If[/color]
End [color=blue]Sub[/color]
Alan
_..................................
More detailed Code and explanations:
This is what you would need to do
first to put the code in an existing Excel File:
As this code works on a Worksheet it must be put in a Worksheet Code Module,
not in the more usually used Normal Code Module.
One way to put that in is:
_(i) Copy the entire code from the Post Code Window to the Clipboard ( Select it all and hit Ctrl C )
_(ii) Click Right with the mouse on the Tab of your sheet of interest, then select something like “Show Code”
_(iii) The VB Editor should come up with a large empty code Window. Paste the entire code in that Window
_(iv) Hit Alt + F11 to get out of the VB Editor
_(v) Save and close the File ( but make sure you save as a “with macros” type
__ .xlsm )
_(vi) Open the file and select something like “enable macros” when a warning comes up.
Then basically it should “work” such that when you Paste your Long URL in the Specified Cell Range*** it should automatically be converted to a Hyperlink, just as Excel would usually do if your URL had up to a 255 character length. ( *** For this code I narrowed the Range it will work on to the first 5 cells in column E )
Code: (
It looks better in the VB Editor as all ‘Comments “go off” to the right. )
Rich (BB code):
' davesexel http://www.excelforum.com/excel-general/1125569-creating-in-excel-a-hyperlink-to-a-route-made-in-google-maps.html#post4311156
Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False 'Does not appear to need this.. I thought it would as i thought the change in the sheet done in line 60 would set this code off again. This code line would have avoided that by turning off codes of this nature ***
10 Dim strDaveLongSExelHype As String 'Done at complile time to Tell VBA initially where to go when seeing strDaveLongSexelHype anywhere else in the code ( This may likely change as required to hause changing length values http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html
20 Let strDaveLongSExelHype = Target.Value 'Target is a reserved Word used for VBA for the Range Object corresponding to where you "Hit" ( or the Range which you selected before hitting hit Enter ) The .Value Property assigned to one cell returns the value in an appropriate type, ( in our case typically a String ), of what is in the cell. In other words what you typed in is "put in" strDaveLongSExelHype
30 Dim WhereIWantToInsertMyLongThong As Range 'Variable for the Spreadsheet Area, as a Range Object, where you are expecting to Insert your Long Things.
40 Set WhereIWantToInsertMyLongThong = Range("E1:E5") ' CHANGE this to suit your wishes! ####
'41 Set WhereIWantToInsertMyLongThong = Cells ' ##### This alternatve would result in the code working over the whole Worksheet
50 If Not Intersect(WhereIWantToInsertMyLongThong, Target) Is Nothing Then 'The VBA Intersect Method will return a Range Object that represents the intersection of two, or more, ranges. Nothing is a Special VBA Object for a "not yet assigned Object". If we Not get that then we have a Object, ( in this case a range Object ( a Cell ) ) that intercepts the entire Range where you wanted to be able to Insert your Long Thing ( That entire Range was specified in Line 40 ( or 41 ) )
' The Hyperlink Property of a range returns ( or refers ) to a collection of "all to do with" the Hyperlinks associated with that range Object. Amongst other things a .Add Method is available with 2 compulsory and 2 optional arguments. Based on the arguments, a specific Hyperlink is put in the Range. Anchor:= _____ says where the Hyperlinkk is written in. Address:=_____in this case is the URL itself. ScreenTip:=____is the bit of text in the cell which you usually put in as something like “Click Me”. But just for fun I used the whole text of your URL again. TextToDisplay:=___Is where you put what you want to be displayed in the Box which comes up when you hover over your Hyperlink cell with the mouse. Again just for fun I used your whole URL. I guess you might want just something Like “This goes to my Google Maps Pub Crawl Route when I click on it”
60 Target.Hyperlinks.Add Anchor:=Target, Address:="" & strDaveLongSExelHype & "", ScreenTip:="" & strDaveLongSExelHype & "", TextToDisplay:="" & strDaveLongSExelHype & "" ' " DaveSexuel's Workaround " Allowing a very long ( greater than 255 character ) URL to be put in an Excel Cell http://www.excelforum.com/excel-general/1125569-creating-in-excel-a-hyperlink-to-a-route-made-in-google-maps.html#post4311156
'70 Else 'Do Not need to do anything here... but just for fun, lines 70 and 75 would result in a message box poping up telling you that you selected outside the range you specified in Line 40 ( or 41 )
'75 MsgBox prompt:="Your Selected Range was not within Area " & Replace((WhereIWantToInsertMyLongThong.Address), "$", "", 1) & "" ' Address Property applied to Range Object returns a String including the $ bits. So a simple Replace withn that String , of a $ , with a special VBA String representing a string variable not filled yet , starting looking at character 1 ( first ) , and replace all of them ( that is what -1 means in this case )
80 End If
Application.EnableEvents = True ' I leave this in just to be on the safe side!! **
End Sub
Explanation:
Line 10 is just a Variable for VBA to “hold” the full URL as text.
Line 20 “Puts” your typed in URL in the Variable – It gets it from the Value in
Target.
Target is a reserved VBA Word which represents the Range ( a Cell in your case ) where you typed something in.
Lines 30 and 40 are talking about an aptly named variable,
Where I Want To Insert My Long Thong, which for no particular reason I have chosen as the cells E1 through to E5. You can probably guess what you need to do to change that desired Input Range.
( Line 41, which is not used as I “
‘commented it” out, would be the alternative to make the code work on the entire sheet, as in the simplified code. So Take out the First
‘ on Line 41 if you want to try that.).
Note if you are only were interested in putting a long URL in the first cell, then you could change the bit in Line 40 to be
__A1:A1
__ or just
__A1
__
Line 50 I try to put in a form of plain English:
__ Intersect(Range1, Range2)
__is a VBA Method which give you the Range where the Ranges within the brackets (
_____) cross or “Intercept”. If there is no cross it gives “
Nothing” as an answer. So code line 50 says
If it is
NOT the case that
Nothing is returned,
Then it does Line 60
( I think if you think about that, you will see it is just a complicated way of saying ..“do something if you typed something in the Range you specified in Line 40 ( or 41 if you choose that option )..”...
Line 60 is basically an adoption of a Code I reference in my first reference below. In English it Puts the Hyperlink you want in the cell which was
Target, ( where you typed the URL in ):
Anchor:= _____ says where The Hyperlink is written in ( which cell it goes in. - This is of course just the Target Cell in this case. )
Address:=_____In this case this is the URL itself ( as text )
ScreenTip:=____This is the bit of text in the cell which you usually put in as something like “Click Me”. But just for fun I used the whole text of your URL again.
TextToDisplay:=___Is where you put what you want to be displayed in the Box which comes up when you hover over your Hyperlink cell with the mouse. Again just for fun I used your whole URL text. I guess you might want just something Like “This will open up the web Site when you click on it”
( A more technical explanation here, for my practice:
The Hyperlink Property of a Range Object returns ( or refers ) to a Collection of “all to do with“ the Hyperlinks associated with that Range Object. Amongst other things, a .Add Method is available with 2 compulsory and 2 optional arguments. Based on the arguments, a specific Hyperlink is put in the Range )
Lines 70 and 75 you do not need. That is where the code goes usually if you typed anything in outside the Range you defined in Line 40 ( or 41 ). Originally I did a code bit to tell you with a pop up box that you had typed somewhere outside your intended Range for the Long Things. But that was a pain when it came up every time I did anything anywhere
_ Else
_ in the Worksheet. So I “
‘commented it” out. So Take out the First
‘ on those two lines if you want to try that.
_..........................
Here is the File: ( XL 2007 “DaveLongSExuelHypeHiJack.xlsm” )
https://app.box.com/s/x8tg7r4k2p48u1ivvowzeydiaop0gtni
References
Creating in Excel a hyperlink to a route made in Google maps [SOLVED]
*** http://www.mrexcel.com/forum/excel-...ions-evaluate-method-255-character-limit.html
http://www.mrexcel.com/forum/lounge...ould-like-see-excel-2016-a-4.html#post4276500
http://www.mrexcel.com/forum/excel-...ons-evaluate-range-vlookup-2.html#post3976690
