running macros

[tuli]

Hello,

I have an excel file that it is opened every time I open excel (let’s
call it PERSONAL.xls). In it, I keep many macros that I frequently
use.

I still have to learn how to have the macros in this file operate in
any other file that I open.

For example I have this macro in the PERSONAL.xls. It supposed to
create a link form the text in the cell (The file is a collection of
search results).

Sub Insert_Link()

For I = 1 To 1000
R$ = Str$(I)
a$ = Cells(2, R$).Value
Sheet1.Range(a$).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=a$,
TextToDisplay:=a$
Next I
End Sub

When I open the search results file, I would like to use this macro
(which resaides in PERSONAL.xls). Somehow I must miss to correctly
define to object.

Any ideas?

Thanks

Tuli

 

[Dave Peterson]

When I'm writing these types of generic macros, I'll try to limit the changes to
the activesheet--I won't have to care about names (or codenames) of the sheets.

In fact, with this kind of routine, I'd only make the changes for the cells in
the current selection. Yes, I have to remember to select the range before I run
the macro.

Option Explicit
Sub Insert_Link()
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection

For Each myCell In myRng.Cells
myCell.Parent.Hyperlinks.Add Anchor:=myCell, _
Address:=myCell.Value, _
TextToDisplay:=myCell.Value
Next myCell

End Sub

You may want to put some validity checks in this code to make sure that the
values in the cell are really text that can be used as hyperlinks.

 

[Dave Peterson]

ps.

I like to give myself a nice way to run the macros, too.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm