Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro to execute hyperlink Results

Cell F5 is being populated by a ComboBox. If cell F5 has the following text,
I want Excel to execute an associated hyperlink:

Cell F5 Hyperlink address
----------------- -----------------------------------------
Facilities C:Documents and Settings....Facilities.xls
Maintenance C:Documents and Settings....Maintenance.xls
Overhead C:Documents and Settings....Overhead.xls

For example, if the user selects “Facilities” from the ComboBox, the macro
would execute the hyperlink contained in the code, taking the user to the
Excel workbook C:....Facilities.xls.

What would the code be?

hello

i have in cell D10 phone number

i would like a macro, where when i click on a button, it dials the #

i have skype add-in in the toolbar, so in cell D10, when u right click, there is an option called "call this phone #"

i would like for the macro to execute that

i tried to record a macro (as i dont have much knowledge but didnt get ne where)
Sub dial()
'
' dial Macro

'
    Range("D10").Select
End Sub
can someone pls help

i have columnar text that is organized by catagory and sub-catagory. what i would like to do is have a macro to hide/unhide rows containing text under sub-catagories by clicking on the sub-catagory and hide/unhide rows containing sub-catagories by clicking on the catagories.
can this be done?
I'm looking for a way to execute macros to hide/unhide rows according to catagory/sub-catagory and for this macro executer to hide/unhide as needed.
Thanks.

Earlier this month, this board, and in particular user UHTMilk, helped me imensely by creating a macro to help me add links to PDfs in a spreadsheet. More specifically, this macro looks at a partial string of numbers in a column, searches in a folder to see if a correspondingly named pdf exists, then adds a hyperlink to it. The following code worked like a charm:

Code:
Sub CreatePDFLinks()

    ' The Usual Suspects
    On Error GoTo ErrHandler
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .StatusBar = "Please wait. Creating PDF Links..."
    End With
    
    ' Dim Vars
    Dim baseBook As Workbook
    Dim i As Long, objExcel As Object, objSearch As Object
    Dim strPDFLocation As String, strFoundPDF As String
    Dim strFoundFile As Variant
    Dim intCouponNo As Integer, intHighestCoupon As Integer
    
    ' Decalre Vars
    Set baseBook = ThisWorkbook
    Set objExcel = CreateObject("Excel.Application")
    
    ' Define the Location to Check for PDF's In
    strPDFLocation = InputBox("The Link creation process can take some time to run. Please enter the full directory path of
the folder to search for scans " _
& "(example: c:scans): ", "Scan for Coupons")
    
    objExcel.Visible = False
    Set objSearch = objExcel.FileSearch
    objSearch.LookIn = strPDFLocation
    objSearch.SearchSubFolders = True
    
    ' Loop Through the List of Ticket Numbers from A1 down on the First Worksheet
    ' Can easily be changed to used with a range passed to the sub
    ' depending onw hat works best for you
    baseBook.Activate
    baseBook.Worksheets(1).Activate
    i = 1
    While Range("A" + Trim(Str(i))).Value  Empty
        ' Check For Any PDFs with That Ticket Number
        objSearch.Filename = "0" + Trim(Str(Range("A" + Trim(Str(i))).Value)) + "-*.pdf"
        ' Its found some so find the Highest Coupon Number
        ' This bit isn't particulary elegant though
        If objSearch.Execute > 0 Then
            intHighestCoupon = 0
            For Each strFoundFile In objSearch.FoundFiles
                intCouponNo = CInt(Mid(strFoundFile, InStrRev(strFoundFile, "-") + 1, 1))
                If intCouponNo >= intHighestCoupon Then
                    strFoundPDF = strFoundFile
                End If
            Next
            ' Add the Hyperlink to the Cell but don't change the text
            Range("A" + Trim(Str(i))).Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=strFoundPDF
        End If
    
    objSearch.NewSearch
    i = i + 1
    
    Wend
    
    objExcel.Quit
    
ExitHere:
    ' And Now We Rest
    On Error Resume Next
    Err.Clear
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .StatusBar = False
    End With
    Exit Sub
    
ErrHandler:
    ' Catch Any Errors
    MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation
    Resume ExitHere
End Sub
Now I was wondering if it would be possibe to modify this somewhat as follows. I would like the macro to copy of any pdf it finds and save them to a (user defined) folder.

Is this a simple edit of the above code, or would an entirely new macro be required?

Hi
I have a Hyperlink associated with an Autoshape. The purpose of the
hyperlink is to to scroll to another part of the worksheet.
However, I want to target cells of the hyperlink to appear at the top of
the page instead of the bottom.

I found a good suggestion on the forum on how to do this - see below.

If I create a hyperlink on a cell, this works great and I can see that the
macro gets executed.
However for the hyperlink associated with the autoshape, the macro does not
get invoked.

Any Ideas why ? I am using Excel 2003.

1. start with a fresh, brand new, worksheet.
2. select A1 in Sheet1 and pull-down:
a. Insert > Hyperlink > Place in this document > B9
3. click the link to insure we get to B9
4. right-click the tab (sheet name) at the bottom and select View code
5. Paste this in:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim s As String
MsgBox ("re-scrolling")
s = ActiveCell.Address(ReferenceStyle:=xlR1C1)
Application.Goto Reference:=s, Scroll:=True
End Sub

Any help appreciated

Hi There,

can anybody help me out is above problem?? I need to execute a macro written
in VBA, through Text Hyperlink. I can access a macro through any object, but
how could I do this with text links? please guys, it is urgent.
--
Sheikh Saadi

We have a employee telephone roster on our intranet which list employee's name, work section, grade/title, and telephone number. The only column that we are permitted to update is employee's telephone numbers. I have created a desktop spreadsheet with VLOOKUP to locate the telephone number quickly. I am able to copy the HTLM on a spreadsheet of 1700 names. However, the telephone numbers remains hyperlink to our intranet. Since all the telephone numbers are hyperlink, this creates a drain of my system/memory resources. I would like to place a macro button on the spreadsheet to remove all the hyperlook for each row by running a loop down the column to the last row executing the macro to delete/remove the hyperlink. I using the following code to start off the macro but having problems looping to last row.
==================
Sub HyperlinkRem()
'
' HyperlinkRem Macro
' Macro recorded 11/19/2007 by  Cabrera

    Range ("D2")select
    Selection.Hyperlinks.Delete

'The Loop runs until there is nothing in the last row

Do

    Selection.Hyperlinks.Delete
    ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub
===========================
Can anyone help me complete the correct sequence?

George

Hello Everyone!

I recorded a macro that will go directly to the sheet of a selected cell with a hyperlink property. The issue with this recorded macro is, it has a fixed cell address so every time this macro run, it will execute the same cell that was recorded. I would like to have the cell address as a selected cell (where the cursor is).

Here is the recorded macro:


	VB:
	
 Create_New_Ship_Code() 
     '
     ' Create_New_Ship_Code Macro
     ' Macro recorded 1/19/2011 by
     '
     
     '
    Range("B17").Select 
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see, it has B17 cell address as fixed. I would like to have it as a selected cell meaning where the cursor is.

I hope you can help me on this!

Thank you in advance.

RNF

Hello. I work at a hospital where I have an excel spreadsheet of all inventory (computers/printers/etc). I use UltraVNC to remotely connect to my computer stations and troubleshoot. What I would like to have is a link or macro that would allow me to click the name of the computer I want to connect to in Excel and open up UltraVNC with the argument .

If you run a shortcut 'vnc.lnk' with a target "pathVNCviewer.exe " (without quotes or brackets), VNC will open and automatically attempt to connect to the computer with that name.

So I need to execute VNC.exe with a parameter to automatically input the workstations' name in the corresponding cell.

I've attempted a hyperlink which does work and seems like the easiest thing, however I get about 4 popups (warnings about macros, then viruses, then a popup asking where i want to 'move' the shortcut to, then where i want to 'copy' it to). If I cancel or ok all these VNC will popup and ask for a password.

I've also attempted a batch file which then runs the VNC shortcut. This also works, but the problem is that I have 150+ workstations and I don't want a separate shortcut and batch for each one. I want a single vnc.exe which I can place the computer name into.

I hope this makes sense. Thanks so much for any help! I'm not great with VB but I'm trying to learn. I'm willing to try whatever I need.

Hi everyone,

In my macro, I have setup a mechanism to execute a hyperlink that will execute a dos batch file. While that is working fine, I want to skip the process of having to confirm the execution of the link. The security option is currently set at "Medium". I've tried to use "SendKeys" but that doesn't seem to be the answer.

My sample code are as follows:

Sheets("DOS").Select
Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=True, AddHistory:=True
' A dialog will come out defaulting at "Cancel", so 1 "Left" and "Enter"
' should execute the dialog(manually) but the "SendKey" is not working

Application.SendKeys "{LEFT 1}", True
Application.SendKeys "{ENTER}", True

Any suggestion on how I could solve the problem?

Thanks in advance.

- Mang -

Hi,

I'm using the following code to insert a file name of an external file into
a cell in an Excel spreadsheet, and it works fine. What I would really like
to do is make that file name a hyperlink to the file so I can click and open
it. I've searched many sites and tried many things but I just can't get it to
work for me. Is there anybody out there who can help me please?

Thanks for looking at my question.

Regards
Paul

================================================== ===========================
'- SEARCH ALL FILES IN A FOLDER & FIND STRING IN FILE NAME
'- PUT NAMES INTO ACTIVE SHEET AT BOTTOM OF COLUMN A
'- (search is not case sensitive)
'- Brian Baulsom July 2008
'================================================= ============================
Sub FIND_DRAWING()
Dim FindText As String
Dim MyFolder As String
Dim MyFileCount As Integer
Dim MyFileName As String
Dim MyFileType As String
Dim f
Dim WS As Worksheet
'-------------------------------------------------------------------------
'- SET VARIABLES
Set WS = ActiveSheet
MyFolder = "H:SERVICE CENTRE DETAILSINSPECTION DRAWINGS and
DOCUMENTSMisc Drawings"
FindText = WS.Range("B2").Value
MyFileType = "*" & FindText & "*.*" ' = "*Test*.*"
'-------------------------------------------------------------------------
'- CHECK FILE NAMES
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.Filename = MyFileType
.SearchSubFolders = False ' True to search subfolders
'---------------------------------------------------------------------
'- RESULTS
MyFileCount = 0
If .Execute() > 0 Then
MyFileCount = .FoundFiles.Count
For f = 1 To MyFileCount
MyFileName = .FoundFiles(f)
WS.Range("H2").Value = WS.Range("H2").Value & " " & MyFileName

Next
Else
MsgBox ("Search for file names containing : " & FindText & vbCr _
& "No matches found")
Exit Sub
End If
End With

'--------------------------------------------------------------------------
'- finish
MsgBox ("Found " & MyFileCount & " file names.")
End Sub

Probably if anyone can help please let me know what when wrong and how
to solve the problem that I'm having currently. I have created a file
with a macro in excel 2003 platform. The macro has been tested and run
smoothly. The file is later uploaded to web (via FTP using Microsoft
Frontpage). I use hyperlink (in Frontpage) to link the page to the
file. To open the uploaded file I click the webpage and the msg prompt
whether to enable or disable macro.

The file later open but not in Excel proper but in web based. Able to
execute macro but it does not run per what is expected. I also
experiencing error msg "runtime error 1004". To overcome the problem I
have to save the file to local drive and re-open back to execute the
macro.

--
ariffin
------------------------------------------------------------------------
ariffin's Profile: http://www.excelforum.com/member.php...o&userid=16114
View this thread: http://www.excelforum.com/showthread...hreadid=275590

Hi there,I am looking suggestion as it is between word and excel.I have a word doc which contains similar words which needs to be changed one after the other, so for this I use excel sheet. its between word and excel filefindingtext1 from word and then go to excel (2003) and where it is mentioned text1, copy the related value and paste in word again,then look for text2.....and so onso to make simple In word first (as the main text is in word 2003)macro should ask what to find first, search that in word file (options YES or NO to look next value)then.go to excel and look in cell c1....c2..A100(say text.xls in c:) and find the related value for that word found textcopy from there the related cell valueand paste it in word (for the corresponding find what value it found and selected in word).FIND nextand.repeat the process till CANCEL TO STOP.In excel the range could be 200I have this code from reference but it copies html hyperlinks , i need here is text and replacement.

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
also it will replace where ever that name comes without first confirming the user(to replace or not for that found word in word doc), we need here in such a manner that after copy it ask yes to replace that word in doc if no go to next word...etc, and STOP to no to proceedthanks,

I have a Table of Contents Code that I am using for a database of patients.

The code works great except for (2) issues

1. Both the "Macros are not enabled" sheet and the "Directory" sheet are included in the code to build the table of conents - How do I exclude only these two sheets ("Directory" and "Macros".

Code:
 'This section builds the Hyperlinks
    
  Dim ShName As String
  Dim ShCt, N As Integer
  Dim Sht As Object

    ShCt = ThisWorkbook.Sheets.Count
    Range("B8").Select
      For Each Sht In ThisWorkbook.Sheets
         If Sht.Visible <> xlSheetVisible Then GoTo Skip
             ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Chr$(39) & Sht.Name & Chr$(39) & "!A1"
             Selection.Hyperlinks(1).TextToDisplay = Sht.Name
            ActiveCell.Offset(0, 1) = Worksheets(Sht.Name).Cells(7, 3)
            ActiveCell.Offset(0, 2) = Worksheets(Sht.Name).Cells(7, 5)
            ActiveCell.Offset(0, 3) = Worksheets(Sht.Name).Cells(11, 3)
         If ActiveCell.Column = 7 Then
            ActiveCell.Offset(1, 0).Range("A1").Select
            Selection.End(xlToLeft).Select
         Else
            ActiveCell.Offset(1, 0).Range("A1").Select
         End If
Skip:
    Next Sht
UPDATE: #1 Solved by adding the following code:
2.  The Table of Contents macro is activated via a button on the "Directory" sheet - However sometimes it executes
on random sheets in the workbook.  I have been unable to figure out why this happens.  Is there a bit of code I can add that
would only allow the macro to run on the "Directory" sheet?

Thank you for any and all help...

Hello,

I am currently using the following hyperlink macro for about 14 different columns in the same worksheet. The macro for each column is slightly different due to a different folder location and column location. This macro is currently not running automatically and I have to run each macro every time there is new data entered.

Now I am using a run all macros macro so I don't have to click each macro to run it. The problem is I would like the macros to run automatically. I also would like to know if there is a way to make this command run any faster. It is currently taking about 30 seconds to run. Any suggestions?

Sub CUST_PO_LINK_MAKER()

  Dim MyFolder As String
  Dim LASTROW As Long
  Dim FIRSTROW As Long
  Dim I As Long
  Dim f  As Integer
  Dim MyFileCount As Integer
  Dim WS As Worksheet
  
    MyFolder = "U:Engineering_AdministrationGENERALSpecial ProjectsTC CircleTrialCUSTOMER PO"
    
    Set WS = ActiveSheet
    FIRSTROW = 6
    LASTROW = Range("R" & Rows.Count).End(xlUp).Row
    
    For I = FIRSTROW To LASTROW
        FindText = Range("R" & I).Value
        
      '- IGNORE BLANK CELLS
        If FindText <> "" Then
        
           MyFileType = "*" & FindText & "*.*"      ' = "*Test*.*"
           
          '- CHECK FILE NAMES
             With Application.FileSearch
              .NewSearch
              .LookIn = MyFolder
              .Filename = MyFileType
            '- RESULTS
              MyFileCount = 0
                If .Execute() > 0 Then
                   MyFileCount = .FoundFiles.Count
                   For f = 1 To MyFileCount
                     MyFileName = .FoundFiles(f)
                     WS.Hyperlinks.Add Anchor:=Range("R" & I), Address:=MyFileName, TextToDisplay:= _
                     Replace(.FoundFiles(f), MyFolder & "", "")
                   Next
                Else
                     MsgBox ("Search for file names containing : " & FindText & vbCr _
                     & "No matches found")
                End If
             End With
             
        End If
        
    Next I
    
End Sub


So the problem is I have hyperlinks linking to range names. The range names were originaly assigend to the first occurence of a particular value. However after adding / removing data and then resorting the range name now is 'still attached' to the old cell address and not the cell with the first occurence. So far so good?
To overcome this I used excel functions MATCH and ADDRESS to 'find' the address of the first occurence. I then wrote a little macro to 'goto' the new address, but the macro pasted in a 'fixed result' ie the contents of the cell at the time of the macro being written. I need it to goto the contents of the cell as they are dynamically at the time of the macro being run...
Range("P24").Select
Selection.Copy
Application.Goto Reference:="R4C10"
End Sub
Basically the bit in Italics I need to be the contents of cell P24 right now, as the macro is executed.

Hope that all makes sense - I am now stuck.....

Thanks in advance.....

Hi All,
I've attached a sample sheet here.
Basically, I've got a caendar that will be created manually for each month.

The week 1, Week 2, Week 3 etc are all hyperlinks to seperate workbooks where each sheet is for each day of the week (Mon to Fri)

I've got the macro for clearing each of these weeks already working (no problems there).
I've also got seperate macros for showing "X"s in the days of Week 1 that are in the previous month (as in this example where Mon to Wed are in June).
I'll also have macros to do the same thing for Week 5 where days of the week are in the following month.

Right now I create the calendar by hand, run the macro to clear all of the 5 weekly workbooks & then depending on what day of week 1 is the First of the month I run one of the macros that will "X" Mon to the day before the First.
I've got macros to do this where each one clears Mon, Mon & Tues, Mon, Tues, & Wed etc.

My question is:
At the end of my code to clear the 5 weeks, can I execute the relevent macro to "X" the blank days of Week 1.
So basically I'd like a simple piece of code to add on to the end that looks at Friday of week 1, if that isn't blank it looks at Thursday, if that isn't blank it looks at Wednesday & so on back to Monday.
When it finds the first blank it will run the relevent macro to "X" those days of the workbook Week 1.

My macros to "X" the days of the week are called
Blank_Monday_Week_1
Blank_Tues_Week_1
etc.

Hope I've given enough info here - Sorry if it's too much.

Thanks for any help in advance
John

I am using the following macro to run whenever B2 is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
MsgBox ("hi")
Application.EnableEvents = True
End Sub

The macro works just fine; click on B1 and the macro runs.

B1 contains a hyperlink. The click also causes the hyperlink to be followed
(simultaneously??).

Is there anyway I can force the macro to execute completely before the
hyperlink gets followed?
--
Gary's Student

Click on a hyperlink to execute a VBA macro. How to setup the Macro
address and/or subadress fields for this? I remember seeing something
for this a while back.

Hello all,

I've been looking around and educating myself on excel macros the last few days to try and help me with this. I have thousands of pdf reports and essentially what I want to do is have the macro hyperlink all the pdfs in a given folder, but once hyperlinked I don't want it to display the whole path name, just the file itself, so I can copy and paste the hyperlinked reports into my log. Below I have listed of what I was able to find, but don't know how to have it do the name change.

Is there anything I can add to this macro to have it display just the file name when run? If anybody knows how to do this I will personally send a letter of thanks in the mail to your address! If I wasn't clear just let me know and maybe I can try and explain a little differently.

Thanks so much guys.
Sub CREATEPDFDIRECTORY()
Dim path As String
Dim f As Integer
path = "filepath"
f = 1
 
With Application.FileSearch
.LookIn = path
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
If UCase(Right(.FoundFiles(i), 3)) = "PDF" Then
Range("A" & f).Value = .FoundFiles(i)
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & f), _
Address:=.FoundFiles(i), TextToDisplay:=.FoundFiles(i)
f = f + 1
End If
Next i
End With
 
End Sub



No luck finding an answer? You could always try Google.