Free Microsoft Excel 2013 Quick Reference

checking hyperlinks are valid

Actually, you don't need to open a file to check if it exists... Run the
macro below whenever your hyperlinks or their targets change:

Sub ChkHypLnks()
Dim wksHypLnks, wksBadHypLnks As Worksheet
Dim curHypLnk As Hyperlink
Dim curFile As String
Dim iBadHypLnks As Integer

Set wksHypLnks = ActiveSheet
Set wksBadHypLnks = ThisWorkbook.Worksheets.Add

wksBadHypLnks.Name = "BadHypLnks" _
& Right(wksBadHypLnks.Name, Len(wksBadHypLnks.Name) - 5)

For Each curHypLnk In wksHypLnks.Hyperlinks
If Dir(curHypLnk.Address) = "" Then
iBadHypLnks = iBadHypLnks + 1
wksBadHypLnks.Cells(iBadHypLnks, 1) = curHypLnk.Address
End If
Next curHypLnk
Application.DisplayAlerts = False
If iBadHypLnks < 1 Then wksBadHypLnks.Delete
Application.DisplayAlerts = True
End Sub

Just copy the code and paste it in the Thisworkbook module on the VBA
editor, then run the macro ChkHypLnks from the sheet where the hyperlinks
are. It will generate a list of bad targets in a new sheet. You will find out
that it is blazing fast.

Best,

Rafael

"Garbagh" wrote:

> I have had trouble getting the macro to recognise the opening of the
> hyperlink as this is a mouse function.
>
> I appreciate this may be difficult/impossible but i would really like a way
> of checking the links without fully opening the file attached .Excel carries
> out a limited version of this when warning of opening hyperlinks (the error
> message for asking whether you want to open the hyperlink is different to the
> one telling you that the address is invalid)
>
> Opening all of the 3000+ files at once would probably kill my PC.
>
> the files the hyperlink refers to are dynamic and change week to week (from
> a library of over 40,000)
>
> "Rafael Guerreiro Osorio" wrote:
>
> > I think the only way to do this is to write a VBA macro to cycle through the
> > hyperlinks and check whether the each reference file exist.
> >
> > Best, Rafael
> >
> > "Garbagh" wrote:
> >
> > > I have a large spreadsheet with hyperlinks to files on my system.
> > >
> > > I need a way to check that all the links work without going through and
> > > clicking them one at a time (for 3000+ hyperlinks this would be extremely
> > > impractical)
> > >
> > >


My hyperlinks are being changed when I hit save in excel. Eg a perfectly good hyperlink, which works well…
wwleeds-srvwwstandardsTechnical_GuidesPart_L_informationCIBSE_guide_F_Energy_efficiency_in_buildings.pdf

...changes to an unusable string of nonsense on hitting save:
../../../../../wwstandards/Technical_Guides/Part_L_information/CIBSE_guide_F_Energy_efficiency_in_buildings.pdf

I just don’t know why it changes at all when it saves, it puts all the back slashes to forward slashes, removes the server name and puts in a whole load of forward slashes.

Clicking on the saved hyperlink brings up the error message:
“The address of this site is not valid. Check the address and try again”

I have tried inserting the hyperlink by right clicking on the cell, selecting hyperlink and then browsing to find the file on the server that I’m after, then testing it to see if it works, and it does until I hit save. I then tried fixing it by editing the hyperlink. This also works until I hit save.

Please could you let me know how to solve this problem?

I've checked the microsoft website for help and I think that this is suggesting that I do what I have already done ( http://support.microsoft.com/kb/156893 ), if so it doesn’t work, if not please can you help me with plainer instructions? ( I don't think it works).

I have multiple cells in a sheet that have hyperlinks (=hyperlink(...)). Those hyperlinks point to folders on a network drive. These folders are quite often moved, making the hyperlink fail. I have been trying to find a way to trap the error that comes, so I could do a filesearch and that way located the new location of the folder. I have searched extensively, but not found anything similar. Can anyone help me on this?

best regards,

Arnar

I'll try to explain the best I can.


	VB:
	
 Hyperlink) 
    If Target.Address = "/public" Then 
        Range("$C$16") = Range("C16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$F$16") = Range("F16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$I$16") = Range("I16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$L$16") = Range("L16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$R$16") = Range("R16") + 1 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code works fine but here is the thing:

Im trying to create a click counter to track what hyperlinks are clicked in my excel document.
If my excel document is located on the "public" drive, the only click counters that work are the ones located on the "private" drives.
For example: If my excel document containing the code above is located on the "public" drive the only IF statements that work are 2nd and 3rd.

If I copy the document and place it on the "Private" drive the only IF statements that work are the 1st, 4th, and 5th.

Creating a shortcut to files on the "public" drive and placing them on the "private" drive, (and/or Vica Versa), and hyperlinking to them does not work because the document thinks the shortcut may be unsafe.

I have macros referencing a group of merged cells that contain the hyperlink. The macros are placed on a image/picture.

So basically whats happening is:

Someone clicks a picture that has a macro on it, that macro references a group of cells that contain a hyperlink. Once clicked the counter increases by one.

Im just wondering why the click counters only work on some of the hyperlinks, depending on what drive my excel file is located on. If I flip the drives, the ones that previously didnt work, now work, and the ones that previsouly worked, no longer work.

I hope that makes sense.

Let me know if you need any clarification.

Thanks

I have a spreadsheet which is a [shared] spreadsheet. Ever since it was
formatted as shared, the ability to create Hyperlinks is gone. The
spreadsheet has a lot of email addresses on it which we wish to make into
hyperlinks.

When going to the Insert menu, 'chart', 'diagram', 'object' and 'hyperlink'
are all greyed out.

Any help in this matter would be greatly appreciated.

Regards,

Greg

Hi all,

A simple function question.

What is a function that returns Truse if all the selected cells are
validated as True by ISNUMBER function and False if any of them is False?
Something like {=ISNUMBER(A1:A10)}???

Thanks in advance.

How can I check my Hyperlink's with sn "IF" formula?
I mean:
A
1 http://microsoft.com

Is there a formula that does "if(a1=ok,"Good Link","BAD LINK!!!")
tahnks.
Asaf.

Hi,

I have cell A1 display values based on data validation from a list that is
column B.

The values in column B are hyperlinked to different web address.

Is there a way to have cell A1 display tthe hyperlink when a value is
selected?

Maybe with a macro??

Thanks
Alex

I have written a VBA macro to check certain spreadsheets for valid syntax.
Certain cells are supposed to have hyperlinks, whose addresses are related to
the value of the cell, which in the case of cells with hyperlinks is also the
TextToDisplay value. I am currently adding a check for the hyperlinks.
Most of the time, the checking was fine, but I was getting a message for
some of the cells containing hyperlinks, that the hyperlink was wrong. When
I edited the hyperlink, it looked ok.
When I closed the hyperlink w/o making any changes, it then checked out as
valid!!!

I finally pinned down the problem. A clue : if I cancelled the hyperlink
editing, instead of closing it, it continued to get an error msg. I did
compares with Trim, and displayed Len, but could not find a problem there.
It turned out that the hyperlinks which give an error msg have a backward
slash () instead of a forward slash (/). When the hyperlink was edited,
they automatically changed to forward slash "/" in the editing window, and
were saved as such when the hyperlink edit was closed, but were unchanged by
a cancel. But when I did a compare on the hyperlink.address, to a string
generated by the macro which used "/", it of course did not compare as equal.

Also, when I hold the cursor over the cell (w/o clicking), which shows the
expanded hyperlink address, the separator shown is the backward slash (""),
regardless of what kind of separator is actually in the hyperlink. I am
embarrased that I looked at several Msgbox messages showing the actual
hyperlink.address, which showed the actual type of separator, w/o noticing
the difference.

I'm using an Excel workbook to record information for genealogical research. Each family sheet has grandparents, parents, and children in assigned cells. Hyperlinks are on the family sheets if further information is known and recorded on other family sheets. If information is not known or the sheet hasn't been created yet there will not be a hyperlink. I have used vb to help with the index sheet to re-hyperlink back to index after any row or column insertion or sort on the index sheet. I have vb to add links to names on a family tree sheet to their family sheet. I want to learn how to check if a cell has a hyperlink or not in vb. I know it must be simple, but I don't know what I don't know and MS Help isn't any help.

Hi guys,

I hope you can help me, I have a list of 7000 hyperlinks (string) and
some of them are broken.

How can I and check them and put their status in the next column?...
something like: "OK", "Broken"

Thanks
A.

On my userform (Excel 2003), users need to enter dates into various textboxes - these are dates of birth, start dates etc. I need to test that the date values entered are valid.

For example, I am checking that the date a user has input into a textbox called "StartDate" is a future date. So one of the checks uses the DateDiff formula. My issue is that if the textbox is empty when the checking subroutine is run, the DateDiff formula results in a mismatch error.

I can overcome this using "Goto" as shown below, but I hate to use Goto and it's messy if I am checking several dates. Can anyone suggest a cleaner way to code this?

If StartDate.Value = "" Or Not
IsDate(StartDate.Value) Then
        ErrorCheck = "Please enter a valid start date"
        GoTo skip
    Else
                If (DateDiff("D", StartDate.Value, Date)) >= 0 Then ErrorCheck = "Start Date must be a
future date"
    End If
    
    skip:
    end sub


Hello,
I am using excel 2010. This is my first post, so forgive me if my terminology is off a bit.
My situation:
I have a worksheet that contains both locked and unlocked cells. The unlocked cells I am having the user insert hyperlinks to external files. This worksheet also has some macros running. When I go to protect the worksheet, I have the options to allow the user to do many things. I check "select unlocked cells" and "insert hyperlinks" and proceed to protect the worksheet. I am leaving all the cells where I want hyperlinks inserted as "unlocked". All works fine except now that the worksheet is protected, my macros won't run.
I've done a lot of searching and have found a few ways to allow macros to run on protected worksheets, from entering some VB code on the open command (userinterfaceonly=true) to putting VB code that does the same thing at the beginning of each macro. All of this code unprotects the sheet first and then re-protects the sheet at the end. The problem is that when the code re-protects the worksheet, the options that I previously selected ("select locked cells" and "insert hyperlinks") are no longer selected. It defaults to unchecked.

So I guess my question boils down to this - can macros be run on a protected worksheet while still allowing users to insert hyperlinks in unlocked cells? How is this done? Can code be inserted that upon re-protecting the worksheet, my protection choices will still be valid?

Thank you very much for any help.

I have picked up a useful piece of code that helps me hide a selection of rows when the checkbox is selected. The problem I have is that I am trying to add fifteen check boxes that will show/hide different rows as they are checked/unchecked but I can only get the first one firing correctly.

I assume I am not putting the correct rules between each VBA instruction but being very new to this I cant figure out what to do.....can anyone help me please?
My code is as per the below.

	VB:
	
 CheckBox71_Click() 
    Dim Rng As Range 
    Dim MyCell As Range 
    Set Rng = Range("A27:J34") 
    For Each MyCell In Rng 
        If MyCell.Value = "" Then 
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn 
        End If 
    Next MyCell 
End Sub 
Sub CheckBox73_Click() 
    Dim Rng As Range 
    Dim MyCell As Range 
    Set Rng = Range("A36:J45") 
    For Each MyCell In Rng 
        If MyCell.Value = "" Then 
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn 
        End If 
    Next MyCell 
End Sub 
Sub CheckBox74_Click() 
    Dim Rng As Range 
    Dim MyCell As Range 
    Set Rng = Range("A47:J56") 
    For Each MyCell In Rng 
        If MyCell.Value = "" Then 
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn 
        End If 
    Next MyCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance ,
Chris

Hi,

I have a spreadsheet which is used as a template to upload data. The user
pastes in their data and, if the data validates ok, then a "flatfile" is output
for upload to a database.

As such I have data validation rules in place on various cells/columns across
the sheet. I managed this no problem at all. This validation is run by VBA code
from a command button as the data is ALWAYS pasted in, bypassing the normal data
entry validation. Most of the cells contents are items from various named drop
down lists from another tab on the sheet

Once the validation is complete I have written VBA to highlight the cells
that failed validation. This all works fine.

The problem is that now I have been asked to add validation to make sure that
certain "mandatory" fields are completed (i.e. Not empty). My issue is that to
do this I had to uncheck "Ignore blanks" in the validation rules options as some
cells CAN be blank while others are mandatory. This means I need to check the
previous validated cell to make sure it is not empty (to check the end of the
file has not been reached) and then apply the validation to the cell ONLY if the
previous cell was not empty.

I think I need to use the custom option in the validation rules but do not
know how to create a formula which only validates if the previous validated cell
is not null.

Any help or pointers would be greatly appreciated.

Thanks

I have a spreadsheet with 2 columns: company and URL. I would like to be able to have the spreadsheet check all of the urls and return a result in a third column (results) with a result by each url (live/dead).

It would be very useful to look at the spreadsheet and see the companies with their URLS and to see which are valid or not - instead of having to click on each one

Is this possible and if so could someone please point me in the right direction?

TIA

/tiempo

Hi Guys,

I am building a spreadsheet to hold dates, rooms and course events for an education program. The spreadsheet has a column for each day and there may be a number of events in different rooms in the rows for each day. This works so far!

I want to add some validation in so that it will check to see if I have used the same room more than once in a day. This really means that I want to check for duplicates in a column which represents a single day. My event name in the column is made up of a code :-

PRO-INS-7-A

The last letter is the code for the room and the only significant element I need to use in the duplication test. I can modify the structure of the code if it makes things easier.

I want to do this as a global test via a button. This is because I may need to make a number of changes and then test to see if those chnages are valid.

I am sure that this can be done in Excel but I don't know how.

If you can offer any suggestions I would really appreciate it.

Cheers

Tom Mcguire

I am using this spreadsheet that picks up a filename- full path (variable) with whatever is in column A of my spreadsheet, and then runs the macros in the subsequent columns and re-saves the file. Before running this, I wanted to include a macro that would check all of the filenames in column A to make sure they are valid file paths/filenames.. if not, highlight the cells that are bad paths. Does anyone know of a way to do, if even possible?

Thanks all for any suggestions!!

I'm hoping someone can direct me to a patch or tell me if there is a global solution to a quirky error. All of the company spreadsheets are set up with an "Int" equation formula to confirm totals are valid and returning conditional formatting if resulting answer does not equal Zero. What we've found is certain hard coded dollar values, I'll use 49.46 as one of the examples, doesn't calculate correctly if you have subsequent columns calculating values based on Vlookup results. However if you round up a penny or down a penny the calculation works correctly. Following is an example of what we're cross checking and the resulting error.

Hard code # Vlookupvalues Cross check formula
49.46 10.50 (2 columns) =abs(total of hard code + lookup values minus(total cost*100)/100))

In this case the total cost = 70.46 and is calculated by excel using the sum feature. When you apply the cross check formula, the result = 1.421E-14

I haven't experinced this error before so I'm lost as to why this is happening.

Hyperlinks are added to several rows in a worksheet and an admin column uses a fileexists function to give users a quick visual indication that the hyperlink file does indeed exist. When the hyperlink is first created the file will always exist (TRUE). The completed workbook and linked documents are subsequently moved to another drive/server. The data controller opens the workbook and 'should' see in the fileexists column whether the file does indeed exist - it may have been deleted in transit or moved to a sub-folder.

The function I have works OK but does not update the column when the workbook is opened at the new location or by pressing F9 - The only way I can get it to update is to double-click the cell but there are 1000's of cells.

How can I get this function and column to update each time the workbook is opened?

Any help appreciated.


	VB:
	
 
     'check if a file exists
     '
    Set fs = CreateObject("Scripting.FileSystemObject") 
    FileExists = fs.FileExists(Filename) 
    Set fs = Nothing 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

How can I check the #Ref validity of a Chart SeriesCollection?

I am writing some macros to automatically add and remove Series to a chart.
To remove data series, I first delete the sheet where data is contained. I would then like then to remove in the chart the series referencing the sheet not existing anymore, but I do not know the best way to do it. The solution that I have find for instance is:

	VB:
	
 Charts(1).SeriesCollection.Count 
[INDENT]Sheets(1).Range("A1").Value = Charts(1).SeriesCollection(n).Name 
If Sheets(1).Range("A1").Value = CVErr(xlErrRef) Then 
    [INDENT]RemoveSeriesCollection(n)[/INDENT]End If[/INDENT]Next n[/INDENT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I copy the SeriesCollection.Name to a cell, and then check if the value of this cell is = CVErr(xlErrRef)

-I would like to avoid having to copy data to a cell. If I check directly

	VB:
	
 ...[/INDENT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
that does not work. There should be some trivial solution, but I am missing it!

-I would also like to be able to do the check with SeriesCollection.Values and not SeriesCollection.Name. If the series have no Name, the macro will not work correctly.

Thanks for any help.

Patufet

Hi

My first post, so please be gentle...

I have a spreadsheet contains has 2 types of links as well as normal data captured cell contents.

The first type of link is Reuters links, which pull a price and a name into columns M, N & O of the spreadsheet

The second type are hyperlinks in columns Q & R, which provide links to word documents saved on a shared drive.

The problem I am experienceing is that other "non-linked" cells (generally in columns O, N, and I) are "becoming" links to documents that I've never seen before. This occurs in both blank cells as well as cells containing textual datacaptured information.

When I mouse over these "infected" cells the cursor changes from the usual white cross to a white hand and a drive path appears in the tooltip. Deleting removes the issue, but obviously this is only useful for the blank cells ^^

Has anyone experienced this before? Any help greatly appreciated...

cheers

Dear Smartest Excelers In The World,

Is there a way to create a data validation drop-down list of hyperlinks? So that every time you selected an item from the drop-down list the hyperlink brought you to a cell in a different sheet in the same workbook?

Basically I'm writing a fantasy football spreadsheet and I need to validate that each team contains no more than 2 players from any 1 club

Range D15:d25 returns the players clubs, but I need something that will check there are no more than 2 occurrences of say "liverpool" in those 11 cells

is this possible as a formula as I'd like it to be in an if statement so I can quickly identify any errors

Cheers

Hi there - i am using excel 2003.
I have created a userform with Checkboxes and command buttons.

Is it possible to do the following:

On command button "OK" Click - check to see that all checkboxes have been ticked before proceeding.

If all checkboxes have not been ticked - display message "Please tick your Checkboxes before proceeding".

There are 3 checkboxes in question.

Look forward to anyones response on this.
THank you.