I thought it would as i thought the change in the sheet done in line 60 would set this code off again. 'Application.EnableEvents = False 'Does not appear to need this. ' davesexel Private Sub Worksheet_Change( ByVal Target As Range) ( *** For this code I narrowed the Range it will work on to the first 5 cells in column E )Ĭode: ( It looks better in the VB Editor as all ‘Comments “go off” to the right. 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. _(vi) Open the file and select something like “enable macros” when a warning comes up. _(v) Save and close the File ( but make sure you save as a “with macros” type _. _(iv) Hit Alt + F11 to get out of the VB Editor _(iii) The VB Editor should come up with a large empty code Window. _(ii) Click Right with the mouse on the Tab of your sheet of interest, then select something like “Show Code” _(i) Copy the entire code from the Post Code Window to the Clipboard ( Select it all and hit Ctrl C ) This is what you would need to do first to put the code in an existing Excel File:Īs this code works on a Worksheet it must be put in a Worksheet Code Module, not in the more usually used Normal Code Module. Anchor:=Target, Address:=strURL, ScreenTip:=strURL, TextToDisplay:=strURLĮnd If If Not Intersect(Cells, Target) Is Nothing Then
Private Sub Worksheet_Change(ByVal Target As Range)ĭim strURL As String: strURL = Target.Value ( I was a bit surprised that my codes do not set off an infinite loop of the Worksheet_Change code !? !? ) Then I include a more detailed code and explanation for the unlikely case that someone with less VBA knowledge than me pops by.
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. ***_ b) note also in passing that a 255 limit in addition applies to any string it pastes out ) – It is strings within formulas that have that limit _a) in passing that for a formula it is still exactly a 255 limit. Except it does not work if the text is over 255 ( I note That is basically what my Excel seems to do anyway if the text “looks” like a URL. In brief an Events ( Worksheet_Change ) Program sets off a code that will change a text in a cell to a hyperlink. The best I have is a modification of a VBA solution workaround which someone did. įurther 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. ( 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”. Fundamentally there does not seem to be a workaround to get over the 255 Formula limit. I never managed to do any formula to get over the problem. So I thought it would be a benefit to others if i dropped off one solution that I now have. But, ( I guess due to the well descriptive Title ) I kept hitting on this Thread toime and time again while I was searching!!!. This Thread did not help too much as no solutions in it worked. I was looking at a similar problem just recently.