Edit batches (tons) of hyperlinks display text

Hello Eric,

Welcome to the Forum!

If you have a workbook you can post, it would be a big help in providing you with a solution. Can you post a sample workbook ?

Hi Leith,

Thanks for the speedy reply, will attach a sample worksheet.

Also another good point of reference are the code boxes I provided above in my original post explaining what's really wanted to get achieved.

Of course though, the column names or positions will vary depending on the sheet, so this one is simply a sample, and so the point like was stated before is to be able to automate the editing of the display texts of long rows/columns of hyperlinks on a spreadsheet.

Please let me know if you've got any questions or if there is any more need for clarification.

Thanks
Eric

Hello Eric,

The macro below has been added to the attached workbook. It will allow you to only select a single column or row at time or single cell. Otherwise, you will get an error message. The macro uses a dynamic named range on Sheet2 called Link_Text. You can add the new text in this column. The macro is run by a button on Sheet1.

Please Login or Register  to view this content.

Wow Leith, thanks a lot for the awesome support, it’s really appreciated!

Now I did test out the macro and it worked exactly like it was described in my original post however there's a few points/questions that arose while I was testing it out...

1) Would there be any way at all to actually have the macro become like some sort of tab/button in the quick access toolbar like some of these macros/add-ins you can download online and add to excel then automatically use?

2) My mistake, however the spreadsheets I would be applying this macro on actually don’t have any Sheet2 or Sheet3, I’m really sorry about that, but actually if the macro can directly work on an empty specified range/column on a spreadsheet (same Sheet where the links are) that would be fine too.

My purpose in providing the sample worksheet was to give an example of how the links would look like on a spreadsheet.

Also is there any way to add this macro to spreadsheets that are already pre-created and already filled with hyperlink columns to edit? Just asking because I’m not really sure how I should add the code you provided onto Excel and start using it (unless of course if there is any way to make the macro into some sort of Excel add-in like in #1 above).

3) Another important thing to know too (unless again the macro can be made to become an Excel add-in of some sort) would be if there’s any way to delete this button that says “Change Link Addresses” after it has actually served its purpose of editing the links?

This would be important because the sheets would need to be clean with the final edited links, without any kind of button or extra sheet, etc, just the links, so not sure how to delete the button and simply save the sheet with the new final links.

But if there’s a way to make the macro as an Excel add-in that can become like a button in the quick access toolbar that’d be awesome, would really solve a bunch of things.

As far as no Sheet2 actually being there on any of the spreadsheets we could simply have that new range become like a blank column on the sheet containing the links itself and after done, simply delete the column/range and then save the sheet with the final edited links.

That or if there’s actually a way to pop-up a box or something where you copy/paste the display texts that will be added to the selected column/row of links (though I’m not sure this could even be done with Excel, but that’s just an idea).

4) That’s really just a minor issue I guess, but is there any reason the macro doesn’t edit more than 5 links? Tried it out a couple of times and seems it stops at 5 links, when there could be like 500 links to edit.

Well that’s all Leith, again your help is really appreciated, thanks a lot for being helpful.

Take care,
Eric

Hello Eric,

You idea about using an Add-In is a good one. It will eliminate the need to use space in the workbook with the links and also can be used with any open workbook without regard to sheet names.

The text could be copied into a TextBox on UserForm. This will allow the user to edit the copied text, add more, or delete text as needed. Since I don't yet own Excel 2010, I am not sure how to write the code for Quick Launch Bar. I can do it in Excel 2003 but it doesn't mean it will convert properly in 2010. But, we can try and go from there.

Hello Eric,

Thanks for the link. My main problem is my current system configuration won't support 2010. So, I am stuck for while till I can upgrade the old beast.

Hah alright, no problem. Worst case I'll simply use Excel 2003 if the add-in doesn't work in Excel 2010

Another solution too could be to get some help regarding compatibility issues from another moderator on this forum who has Excel 2010 if this is doable.

We're talking add-ins here, but not sure if something like this here...

Please Login or Register  to view this content.

...could be of any help to version compatibility issues? Or this would only work with macros and not add-ins?

Thanks again,
Eric

Hello Eric,

The Add-In, whether Personal.xls or other, still contain macros that would need to be evaluated individually for compatibility. Going from older to new versions is less likely to have issues than the other way around.

However, in the code I will be writing there is a another wrinkle which is the use of API calls. This may cause issues with the users, especially on networks. They may not have the necessary permissions to run the code.

Hello Eric,

Running the code on your own computer should not be problem, as long as you log on as the Administrator or with Administrator rights.

There is a very good chance that an add-in written in 2003 will run with no problem. The same can not be said for trying to run a 2010 add-in on 2003 platform.

Excel 2010 opens in compatibility mode (97 -2003) automatically when an Office document that is in binary format is opened in 2010. SO you shouldn't need to install Office 2003.

Hello Eric,

I have completed and tested the Add-in code. The attached zip contains the 2003 Add-In (.xla). Open the zip file then drag and drop the xla file into folder of your choice. Once you select the Add-In using (2003) Tools > Add-Ins... or (2010) Tools > Options > Add_ins..., a shortcut will be created in Quick Launch. The UserForm can can be minimized and restored like a window. Once the UserForm is closed, the user can reactivate it any time using the shortcut keys CTRL+SHIFT+K. Try it out and let me know how it works.

Hi Leith,

Thanks a lot for the add-in. Now on to a couple of things/possible issues:

1) So I'm able to add the add-in in Excel, and when it's added a box pops-up where I can add my display links to edit the selected ones on the worksheet which seems to be working good, so as far as that goes it’s all good, only question is do my links have to be in any specific format to have the add-in work on them?

I see it says there must be some kind of carriage/return feed line at the end of the link, what’s that?

Another thing too is the add-in doesn’t appear at all in the quick access toolbar nor in any tabs in the Excel menu, however CTRL+SHIFT+K does work and makes it pop-up when needed.

2) Now here comes the biggest hurdle, thing is, for the first time like I said I’m able to add the add-in and make it work.

However when the worksheet it’s working on is closed any other Excel spreadsheet/file that is subsequently opened first displays the message “Run-time error ‘1004’: Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.”, to which I can click “End”, “Debug” or “Help” and since I obviously don’t have any VBA knowledge I simply click “End” which opens up the spreadsheet/file.

From that point the macro doesn’t work at all anymore and is unaccessable unless I disable it in the add-ins option where it was added at first and then re-enable it.

From there it works again until I close the spreadsheet and then the whole process above repeats itself once more for all and any Excel file.

So I’d say that’s really the biggest issue here, do you think it’d help out in finding the issue if I attached one of the add-ins I have in Excel that are working fine with no issue which I use on a daily basis to automate tasks (it’s an add-in that renders a plain or “dead” column or row of links live from the click of a button, instead of having to render them live one by one manually)?

Another thing too, once the add-in is fixed, is it possible to have it open and working on all open spreadsheets and any spreadsheets that gets opened later on while it’s working?

I’m asking because from what I’ve seen it only works with the spreadsheet it’s open on, and if I want it to work on another spreadsheet I have to manually open it each time, so I’m wondering if there’s any way to have it work on all sheets by default, like having it act as a standalone add-in that isn’t attached to the spreadsheet it was opened on, meaning if I switch to another spreadsheet the add-in window stays where it is, whether it be a certain spot it’s positioned in on the spreadsheet or maybe a little lower or whatever, this way it doesn’t have to be opened on each spreadsheet, basically having it act as a window of its own that can be minimized etc.

Just trying to explain this as best as I can but hope I’m not being too detailed or confusing hah.

So that’s about it Leith, let me know if you need any more details from my side and I’ll do my best to help out with this.

This add-in is nearly done and is pretty awesome where it is right now, just a few little tweaks and we’re golden

Thanks again for the help Leith,
Eric

EDIT: Oh quick thing, you did say that the add-in would be added in Quick Launch which I guess in Excel 2010 means that it would show in the "Recent" files as a shortcut so that it can be opened up quickly.

What I meant in my earlier posts though for the quick access toolbar is that in Excel 2010 (not sure about 2003) there's like a little toolbar next to the menu items where you can add add-ins/macros you regularly use for quicker/instant access.

Like for example the add-in I described above that renders my plain links live has its place on my quick access toolbar where all I have to do is click it to have it render the selected column of links live, do you think this is something that could be done with this current add-in, adding it to the quick access toolbar?

Actually here's a link to how the quick access toolbar exactly looks:

Please Login or Register  to view this content.

Thanks again,
Eric

Hello Eric,

1) The Quick Access Toolbar and the Quick Launch menu are very different animals. The code to modify them isn't even close. Not having 2007 puts me at a disadvantage for creating and testing the code for the QAT, but I think I can manage.

The carriage return/ line feed combination is generated automatically by Excel whenever you press the "Enter" key. I included the statement in case data is copied from another OS or website. Not everybody uses the same new line character. Some use carriage return (ANSI code 13) and others use just a line feed (ANSI code 10).

Add-Ins only appear in the Add-In list where they are either added or removed as needed. Using a hot key is the standard method of calling or starting Add-Ins and macros. It is also possible to create custom menus with the macros that can be selected.


2) Answering this question is difficult because I am not sure I understand your terminology. I will explain a few terms first so you will understand what I am referring to and if it matches what you are referring to.

• Workbook - An Excel file that contains one or more worksheets.
• Worksheet - An object representing a spreadsheet that can contain formulas, graphics, and other programming objects like menus and toolbars.
• Open Workbook - A workbook that has been loaded by Excel and is accessible by the mouse, keyboard, and other input devices.
• Closed Workbook - An existing Excel file that is on the hard disk or network drive but is not open in the Excel program.
• Hidden Workbook - A open workbook whose visible property has been changed from True to False to hide it from view. The allows the workbook to only be accessed through code.
• Program Instance - A copy of a program that is loaded into memory. Some programs only allow a single copy to be present in memory at a time. Others allow multiple copies to be created. Excel allows multiple instances. A single instance of Excel can contain several open workbooks, but the workbooks are not the Excel program.

In my experience, I have never received the error you mention below:

However when the worksheet it’s working on is closed any other Excel spreadsheet/file that is subsequently opened first displays the message “Run-time error ‘1004’: Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.”, to which I can click “End”, “Debug” or “Help” and since I obviously don’t have any VBA knowledge I simply click “End” which opens up the spreadsheet/file.

Once an Add-In has been loaded, it is available to all workbooks in the current instance of Excel. Opening a new workbook should not trigger any errors due to the Add-In's presence. The only hidden workbook would be the Add-In itself, unless you have some macros elsewhere that hide and unhide workbooks.


3) Although you included this in question 2 it really is a separate issue:

Another thing too, once the add-in is fixed, is it possible to have it open and working on all open spreadsheets and any spreadsheets that gets opened later on while it’s working?

I’m asking because from what I’ve seen it only works with the spreadsheet it’s open on, and if I want it to work on another spreadsheet I have to manually open it each time, so I’m wondering if there’s any way to have it work on all sheets by default, like having it act as a standalone add-in that isn’t attached to the spreadsheet it was opened on, meaning if I switch to another spreadsheet the add-in window stays where it is, whether it be a certain spot it’s positioned in on the spreadsheet or maybe a little lower or whatever, this way it doesn’t have to be opened on each spreadsheet, basically having it act as a window of its own that can be minimized etc.

A true standalone is an independent program. Excel Add-Ins are helper files for Excel to expand it capabilities. They are not designed to function without Excel. It is possible to create the illusion that a separate workbook "looks" like a standalone when in fact it is a separate Excel instance running macros with only the UserForm displayed.

A Window or UserForm can be "locked" into position in one of two ways. That is to say, once it is displayed the user can not move it or the window stays on top of all other windows. I think what you are referring to is an independent window like you would find with a stand alone program.

The drawback with the stand alone approach is communication with separate instances of Excel. The way the Windows operating system works it does not normally allow one program to access another program's memory area. To do this requires a lot of API coding for two or more programs to share and modify each others information. If you open all the workbooks in the same instance, with the exception of the stand alone, the task is manageable.

Hello Eric,

This version of the Add-In takes care of the previous problems. The only part left is add the code to modify the QAT on the Ribbon. Try this out an let me know how it works.

This is awesome Leith, thanks a lot, you're the man!

Everything is working perfectly and exactly like it was described.

Now I guess the add-in is only acting like a macro (couldn't add it in the add-ins since it says it's a worksheet) and asking me to enable etc each time simply because you haven't added the QAT code yet right?

Well if that's the only remaining thing please do proceed, we're only a few code edits away from this

Thanks again Leith for taking all this time to help and code this, it's really appreciated.

Take care
Eric

EDIT: By the way, at the part above where I said the add-in acts like a macro I meant to say it actually is a macro as of now. It's not in the add-in format, so just wanted to specify this to make sure it's clear because I think the final version of this is going to be in an add-in format right?

Since we wouldn't want any of the spreadsheets/worksheets/workbooks we'll be using this add-in on to display the "enable macro" message like was specified in a few earlier posts.

So was just making sure for that and confirming.

Thanks again Leith,
Eric

Hello Eric,

You will receive the "Enable Macros" message if the Add-In is not in a "Trusted" folder. The default directory for this newly-saved .xla Add-In is the "..\Application Data\Microsoft\AddIns\" directory, which is not necessarily a trusted location and so may not run.

You can change which folders are "Trusted" within Excel 2007 (I assume this applies to 2010 also) via File > Excel Options... > Trust Center. Another alternative is to save your .xla Add-In in the Excel Startup or User Startup ("AltStartup") directory, which are automatically trusted locations by default.

Hello Eric,

You should be able to save the xls workbook as either an xla or xlam format on your machine. You can this by opening the xls workbook, and choosing "Save As...". The available file formats will be listed below the file name in the "Save As" dialog. Save it in either format to one of the "Trusted" folders.

Hello Eric,

I think adding the Add-In to the QAT should be the last step. The xla default folder is the Quick Launch folder which is displayed on the Task Bar in earlier versions of Excel, provided the user has chosen to display it. The QAT is completely separate from this folder and will launch the xlam Add-In when clicked.

Hello Eric,

The new version (ver 2) works correctly in Excel 2003. The problem was with setting the shortcut before the workbook was visible. The code was moved from the Workbook_Open event to the Workbook_Activate event. If you are still having this problem then I can only guess it has something to do with Excel 2007/2010. But exactly what it may be, I can't say.

Hello Eric,

That seems pretty simple. The problem is due to when the workbook first runs it creates a shortcut to where it is saved. When you resave the file an run it, it will look first at the previous short cut, which now doesn't exist. Probably we could trap the error at startup and it would be fine.

Hi Leith,

So I renamed the macro into .xlam (and I guess .xla would be fine too), and them bumped onto the usual error that I encounter after adding the macro into an add-in format and then opening any type of Excel worksheet/workbook, the error 1004:

Please Login or Register  to view this content.

So did some research and very likely (although don't know how to do this, that's why I'm posting this) deleting the shortcut function from the macro/add-in would/could solve the problem, can we try doing that?

From that point, if this does work, we wouldn't need a shortcut function anyways since we'd have added the QAT function.

Check out this thread where I found the above piece of info about deleting the shortcut function:

Please Login or Register  to view this content.

Thanks,
Eric

Hey everyone,

I'm sure Leith is busy assisting other members here who need help (hence why there were no reply on this thread for nearly two weeks now), but could at least any mod/experienced member look into the above please?

Really going to be needing this pretty soon, and the help I got thus far has been amazing, but there's still some little things that have been left out.

Thanks again guys, I know times can get busy, looking forward to some quick help so we can get over with this one.

Eric