Seemore,
...or is there a way to save it?
This code becomes part of the workbook/file you created the module in. When
you close and save the workbook, it is saved.
.... Everytime I create a new worksheet, do I
have to create another vba? for each one ...
That depends on what you mean by "a new worksheet".
Let me go over my lingo for XL (whether or not it is the correct lingo, that
is another matter). I use the terms workbook and file interchangeably. For
me, an Excel file is a workbook, a workbook is an Excel file. A
workbook/file contains worksheets. Many people refer to worksheets as
"tabs" because each worksheet does have a tab in the lower left corner of
the XL window. The tab has the worksheet's name on it (and you can change
its name by double-clicking the tab) and you can change its color (If I
could, I would try to get everyone to refer to worksheets as "worksheets"
and quit using "tabs"). And those people, who usually call sheets "tabs",
will sometimes call workbooks/files "worksheets".
If you mean creating a new worksheet in the workbook/file we added this code
to when you say "create a new worksheet", then no. The code is part of the
file and can be used on any sheet in the file.
If you mean creating a new workbook when you say "create a new worksheet",
then not necessarily, but I might help out alot if you were to move that
code somewhere else. Let me explain:
I designed this code to work on the active sheet of the active book. So,
you could have 3 different files open and use this code on any sheet in any
of the files (you are not limited to using this code in the file where it is
stored), BUT the file where the code is saved needs to be open. So, if you
want to use this code in another workbook, this workbook where you stored it
needs to be open.
If that is undesirable, then we can move this code to your "PERSONAL.XLS"
file. The personal.xls file is a file that is usually opened automatically
when XL starts and is hidden so many people don't even know it is there. It
is meant to put code that is meant to be used on any workbook or to do other
things not even related to workbooks.
If you want to go this route, then follow my instructions, in one of my
earlier posts, for creating a new module in the VBAProject for your file
using the Project Explorer, but instead of creating this new module in
"VBAProject (Your File Name.xls)", create it in "VBAProject (PERSONAL.XLS)".
Then you can copy this code into this new module in the personal.xls
vbaproject. If you don't see "VBAProject (PERSONAL.XLS)" in Project
Explorer, then it might not exist yet (I'm not quite sure if it exist by
default or if it is automatically created the first time we record a macro
to it). We can try to create it by recording a macro to it:
1. Flip back to XL
2. Tools menu > Macro > Record New Macro... (hopefully you are not using XL
2007...if you are, I can't help you too much, but the process will be
somewhat similar)
3. In the "Record Macro" dialog box, the only thing I would change is the
"Store macro in:" dropdown to "Personal Macro Workbook", if it is not
already there. Just leave the default setting for everything else.
4. Click the OK button.
5. A "Stop Recording" toolbar should appear somewhere. Mine only has2
buttons on it: a Stop Recording button (just a blue square) and a Relative
Reference button and I can only see the first 2 letters and part of the 3rd
of the word "Stop Recording" in the title bar of the toolbar.
Now we are in the Recording Macro mode. Everything you do will be recorded
and XL will generate VBA code to reproduce everything you record.
Now make some changes. It can be just as simple as selecting a different
cell, but you can do anything: type text/numbers/formulas into cells,
change formatting, etc... what ever you want. We aren't going to keep this
macro/code, we are just doing this to create the personal.xls file. When
you are done making changes, hit the stop button on the Stop Recording
toolbar.
Now flip back to the VBE and you should see the "VBAProject (PERSONAL.XLS)"
in the Project Explorer and it will have a "Modules" folder in it with and
probably a "Module1" in that folder. You could just paste my code into this
Module1 instead of creating a new module. If the Module1 code window isnot
open, double-click "Module1" in the Modules folder of the "VBAProject
(PERSONAL.XLS)". When you open this module, you will see the VBA code XL
created when you were in recording mode. You can delete that code if you
want...probably won't use it again.
Now that you've added code to the personal.xls, it doesn't mean that those
changes are saved. If you are doing something that causes XL to lock upand
you need to force XL to quit, any changes that you made to the personal.xls
will be lost. To manually save the changes, make sure any item in the
"VBAProject (PERSONAL.XLS)" in Project Explorer is selected and click the
Save button on the VBE toolbar (if you hover your mouse over the save
button, a tooltip will pop up saying "Save PERSONAL.XLS (Ctrl+S)"). When
you make changes to the personal.xls and forget to save them, when you go to
close XL, it will remind you that changes have been made and ask you if you
want to save them.
Also, depending on your Security level setting in XL, you may not be able to
run any macros/code that you create. To check the setting, in XL click
Tools menu > Macro > Security... and then I would choose the Medium setting
on the Security Level tab.
Now, every time you open a file with macros/code in it, you will be asked to
enable/disable macros in a Security Warning. If you ever receive a file
from someone you don't know/trust, you should disable the macros. They
potentially could cause damage.
Now that this code is save in personal.xls, it will be available for you to
use whenever you have XL open. And you don't even have to open the VBE to
use it. When you are working on a workbook that has hyperlinks that you
want to change:
1. select the hyperlinks
2. click the Tools menu > Macros > Macros... (or do [Alt] + [F8] key
combination)
3. select either "All Open Workbooks" or "PERSONAL.XLS' from the "Macros
in:" dropdown box
4. select the "PERSONAL.XLS!RemoveLinks" macro from the list box and click
the Run button (or double-click the macro from the list box).
AND BINGO!!! all of the selected email hyperlinks will be replaced withthe
email addresses.
HTH,
Conan
See my response to your last post....
Seemore,
I have a solution but it is a little more complicated than the previous
one,
but it still uses the VBE.
First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.
Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):
Option Explicit
Sub RemoveLinks()
Dim prngCell As Range
For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")
Next prngCell
End Sub
10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a
CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.
As long as you HAVE SELECTED ALL OF THE CELLS that you want to change
the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove
the
link.
Please write back if you have any problems.
HTH,
Conan
On Jan 30, 9:00 pm, "Conan Kelly"
Seemore,
Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the
next
line) in the VBE when you do this.
That code should delete all of the hyperlinks in column A of the
active
worksheet of the active workbook.
To make things easier, make sure there is only one XL file
...
read more »- Hide quoted text -
- Show quoted text -