Is it possible to make a macro that can search for a file with the same name as a cell's text and then link that cell to the file?
Example, if I had cells that said "One" "Two" "Three" "Four" and "Five" and had a folder in my C drive with 5 files names "One.jpg"... etc could I make the macro search a folder for that file and link to it?
I have "Sheet1" with thousands lines of data like following
ID Customer value date etc,......
2434 ABC 500 2012 data
2654 DEF 600 2013 data
and another sheet "sheet2" to hilight attention to a risk or issue like following
ID description status to do by etc,....
2434 missing items follow-up and fulfill Eng. A
I need to add a hyperlink to the ID number in BOLD to link me to the line matching in sheet1.
I basically need a formula in one tab of a spreadsheet that if another cell contains text it will search for that text in another tab and display a useable hyperlink to it.
So I want a formula in A1 in Sheet1 that will search for the text contained in A2 in Sheet2 and display a useable hyperlink to the cell in which that text is contained, in Sheet2.
This may not even be possible, but if it is then that would save me SO much time.
Is it possible to make a hyperlink that will jump to a section that is not based on cells (A10, etc..) but rather on column (A) and a certain value in the cell, for instance, 2.1 or 2.2 or 2.3. The reason I want to do this is that when I have to add in new rows I do not want to have to redo all the hyperlinks since the cell shifted.
View 2 Replies View RelatedI'm trying to add code that create a hyperlink to a file in a recently created folder in the path of L:Elec Dept ProjectsRELEASED FOR CONSTRUCTION". The code below should find the newly created *BOM*.xls created and created a hyperlink in the next available row starting on row 27 and column O on my worksheet "BOM". The code currently runs with no errors, but I'm not seeing any resulting text with path / links created.
View 5 Replies View RelatedIm am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
I have a hyperlink generated from a vlookup that is generated from the result of a combo box click, so the reference in the hyperlink cell will change to whatever sheet the vlookup dictates.
Then I have a macro assigned to a button ( Called 'Go' ) that when pressed clicks on the hyperlink and takes me to that corresponding sheet.
When I recorded the macro, it clicked on the hyperlink cell when it was hyperlinking to let's say Sheet 'April', but if I click on let's say September, in the combo box, then of course the hyperlink now links to the September sheet, but if I click the 'Go' button with the macro attached, it still takes me to 'April'.
surely, the macro simply follows the steps I recorded and therefore should now be clicking on the hyperlink that now says 'September'. Surely, the macro shouldn't REMEMBER that it originally clicked on 'April'.
I have a workbook with over 40, identically formated, sheets. Each sheet has 96 pages. One page for each roll of carpet. To hunt for the next blank page to use, is time consuming.
So:
On each sheet, Range B207:B303, I have IF formulas that will display the carpet ID number for each page, if one is present, or the cell remains blank. In Range C207:C303, I have Hyperlinks to the pages on the sheet.
I need a macro that will search the Range in column B for the first blank cell, then automatically use the Hyperlink next it in column C. Then I can assign the macro to a button, copy and past the button on all 40 sheets. With one click of the button, I will instantly be taken to the page availabel for data entry on that sheet.
I have a workbook open and my A column cells are filled with (example) Mike-1 in the first row Mike-2 in the second row and so on. Now I have another folder on the computer with Word documents that I want to hyperlink to. The corresponding Word file is named mike1. So I want Excel to go through column A and Hyperlink to the corresponding Word file.
View 6 Replies View Relatedclarify here i attached file.
Hyperlink not running while am using hyperlink formula.
Hyperlink_Dout.xlsm
How do I hyperlink a text box ( button ) to an existing macro?
View 6 Replies View RelatedI have a macro that basically captures all of the errors in my spreadsheet and summarizes them under 3 columns (Value in Cell, Sheet, Cell) so that I can quickly identify the error message as well as it's location. I wanted to take it a set further and add a hyperlink for each error so that the user can quickly go to the error location to investigate. I really don't care where the hyperlink is located as far as which 3 columns.
Range("G2").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
curRow = ActiveCell.Row + 3
[Code] ........
I have the following
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
updateall = MsgBox("Do you want to update the previous balance?", vbYesNo)
If updateall = vbYes Then
ActiveSheet.Unprotect
' clear previous payments
Range("U3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
' update previous balance
Range("H54").Select
Selection.Copy
Range("G11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D7").Select
ActiveSheet.Protect
End If
Application.ScreenUpdating = True
End Sub
Now, this works just fine if the user clicks the sheet itself. However, generally they are accessing sheets via hyperlinks, which do not seem to activate this code. Is there a better/different way to ensure when the user gets to a sheet via hyperlink, they still see the msgbox?[/b]
can i use a hyperlink to call/invoke a macro...?
View 5 Replies View RelatedI want to use a macro (assigned to a button) to open a file named in a hyperlink.
When I record the macro it results in the file being opened, but when I run the resulting macro it only selects the cell, it doesn't follow the hyperlink.
adds the file name into column A if it could also hyperlink it to that file. 2nd, Change it so it doesn't start a new workbook and worksheet. I would like it to just run in the Workbook it is in and each time the macro runs or the workbook opens it updates any info that has changed.
View 5 Replies View RelatedIve got this macro used to search for a particular code in an excel sheet with about 4000 codes, each assigned a hyperlink.
my goal is to make the user enter the hyperlink automatically in the code upon hitting search.
So far this is what i have:
the problem is that it only works with hyperlinks which are assigned a link (right click assign hyperlink) and not codes which work with a formula for example =HYPERLINK("http://www.google.com", "ABC123")
Sub searchsheet()
'Opens box and ask what do they want to search
searchthis = InputBox("Type in a location keyword.", "Property Search")
'Tells where to search
[Code].....
I have this macro that I use for taking information based column B grouping it together and putting in a separate worksheet.
What I would like to do is in the master sheet (sheet1) to create a hyperlink for those listed in column B so I can click on it and it takes me right to that worksheet.
Sub exporttoWS()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
Sheets("Sheet1").Select
Range("A1").Select
'looking at the full length of the file
LR = Range("A" & Rows.Count).End(xlUp).Row
'sheet needs to be named sheet1, all data should begin on row 3
[code].......
I'm trying to create a macro that I can assign to a clip art pic that will pop up the Insert Hyperlink prompt when I click on the picture. So basically anytime I click on the pic for the 1st time I'd like to be able enter the url address of my choosing but if I were to click on that pic again I want it to go to the Hyperlink address I previously entered.
I should note that I tried recording a macro by first by clicking on Record Macro then press ctrl + k and then click Stop Recording but it wouldn't stop recording. I had to first click Cancel on the Insert Hyperlink prompt and then I was able to Stop Recording so this attempt was unsuccessful.
I know there are better ways of doing this but I am working with an existing form and I need it to look the same.
Column O on Sheet1 has a link that use to open a document, now all the information is on Sheet2. On Sheet2 I have a macro that hides every Project except for the Project Number the user selects in B4 of Sheet2.
Can I create a link that will:
1) Select Sheet2
2) Enter the Project Number from Sheet1, current Row, column D to Sheet2 cell B4
I know a link can select a worksheet so I have #1 covered but I am stuck from there.
I am building a dashboard and would like to be able to have shapes I have created be clickable with a dynamic link to a document on our network. Something along the hyperlink() function.
In a cell, the function looks like this: =HYPERLINK(Worksheet!I34,Worksheet!F34).
The data in I34 changes, depending on the input from a drop down using a vlookup command. Is it possible to create a macro and assign it to a shape to make the shape a clickable link?
I have an office map with a somewhat large number of hostnames similar to the structure shown below. I would like to have a macro scan through the hostnames (they all start with "BIGENTERPRS") and change them into a clickable hyperlink that points to "\hostnamec$". So for example, one hostname is "BIGENTERPRS0001". I want the macro to hyperlink that name to the address \BIGENTERPRS0001C$
View 2 Replies View RelatedI have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have a macro that would create a hyperlink for every one of the files in a folder that a specific cell gave the url address for.
Recently I got moved to a new pc and forgot to migrate my macro's over. In combing back through previous threads... I found the macro, but now it won't work.
[Code] ......
The error that I get is:
Compile error:
Invalid use of property
I get this error at row
Range (Cells(Rows.count, "U").End(xlUp).Offset(1, 0).Address)
Why did the macro used to work and not anymore?
I want the macro to:
1. Create a new worksheet when data is entered into a cell (ie. entered text "ACC2013" into cell A5)
2. Rename that worksheet to correspond to the data in the cell (ie. change worksheet name from sheet2 to "ACC2013")
3. Create a hyperlink between cell A5 and sheet2
I'd like for this macro to loop through a range of cells so I don't have to have a long code. Let's say my data range is from A5:A23. If this requires multiple macros that is fine.
Is it posible that when data or text is entered into a cell and the enter key is pushed that this can either run a macro or activate a hyperlink to go to another worksheet for user to follow further instructions?
OR instead of going to another worksheet a pop up message appears with instructions and then can be OK'd to remove message and proceed?
how to open a Hyperlink directly. I have writen some code which allows me to copy over certain cells from a large list of hyperlinked files. However I am struggling to figure out how to open the hyperlink without actually writing the hyperlink address into the code. The hyperlinks are the file names and not the file paths. In essence I'm aiming to just get the macro to "click" the hyperlink to open it. Once the file is open the rest of my code will work, it is just this one sticking point.
View 2 Replies View RelatedI would like to write a macro that would insert a hyperlink to another file an would prompt the user to select the location of the file once the macro was run. I am not sure how I would get my macro to prompt the user to define the location (which is located on a network drive).
View 7 Replies View RelatedI have a workbook that has 2 worksheets. Sheet1 has a data table that is filled from an SQL database. I would like the first column of the data table to have each cell be a hyperlink. The destination of the hyperlink is Sheet2, cell A2. I would like to modify the hyperlink, so that when the user clicks on the hyperlink, the text-display of the hyperlink is copied into sheet2 cell A2. Initially, I thought this was simple. I added the event code shown below:
[Code] ........
This worked, however there was a problem. I manually added the hyperlink to all cells in column1 of sheet1. However, this made the TextToDisplay property the value of the first cell for all hyperlinks. So, solution to add hyperlinks to all cells in the first column, with TextToDisplay property set for each individual cell. What is the best way to do this with the fact that the number of rows in column1 will vary as the size of the SQL query changes?
I use the following to try to access a secure website ( https )
ActiveWorkbook.FollowHyperlink Address:=Hyperlink, NewWindow:=False
Where hyperlink is a string that is valid. ( i tested by doing a cut and paste to the browser and it works ! ) When i launch the macro, it always bring me to the login screen. But if i do a cut and paste, it will bring me direct to the content i want.