i have a WB with over 28 sheets in is there any way of having a box wether it MsgBox or anything to give the user a choice of which sheet to goto , dont have room to add a list of hyperlinks , was wondering if this was possible atall
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies View RelatedI have a large amount of sheets in a workbook (, say 70). For example:
1 GrandMastersheet of pets
.....1 Mastersheet of dogs
.................1 sheet of little dogs
.................1 sheet of big dogs
.................1 sheet of blue dogs
......1 Mastersheet of cats
.................1 sheet of black cats
.................1 sheet of white cats
.....1 Mastersheet of snakes ()
.................1 sheet of cobra's
etc. etc. etc.
The names of the sheets in the lowest level are quite long. Creating a icon with a macro/hyperlink in it is not what I want. (Boxes are too big and there would be too many boxes.)
This is what I would like: A clickable dropdownlist in the Mastersheets, that only show the lower level sheets of that category. For example:
A dropdownlist on the dogs Mastersheet that only show the words 'little dogs', 'big dogs' and 'blue dogs'. These words should be clickable and insta-hyperlinked to the specific sheet.
This code is supposed to open a series of files through a list of hyperlinks, run a macro then save and close. But at the minute it says it cannot find the Macro and error's.
The macro is in each file named and is named 'RefreshStats', if I open a file seperately I can run the macro fine, but it can't find it through this looping code.
Another thing I found though, is that if I put a blank Module in the main workbook (that contains the list) and call this macro 'RefreshStats' the looping works fine but based on the blank macro. So this code seems to want to look in the workbook its in for the macro.
I would like to list all files in a directory (display the full path) and make hyperlinks to all listed files at the same time.
I came accross the code below which works well for files in a directory:
Sub HyperlinksToDirectory()
' puts hyperlinks to each of the files in a directory of your choice
' into the active sheet starting at the active cell
Dim stDir As String
Dim stFile As String
Dim R As Range
Set R = ActiveCell
stDir = InputBox("Directory?", , Default:=CurDir())
stFile = Dir(stDir & "*.*")
Do Until stFile = ""
R.Hyperlinks.Add R, stDir & "" & stFile, , , stFile
Set R = R.Offset(1)
stFile = Dir()
Loop
R.CurrentRegion.Sort key1:=R, order1:=xlAscending, header:=xlNo
End Sub
I need to make a Workbook that has hyperlinks in the first sheet that links to a cell in a second sheet and then back again. The only way I have been able to do this is manually create each link in each sheet. I am hoping that this process can be automated as they can not be 'filled' as far as I can see. find attached an example of what I am trying to achieve.
View 2 Replies View RelatedI have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
I have a spreadsheet that when an identity code is keyed in, it will display a make and model of a particular mobile phone, i want to put a hyperlink in the cell next to the phone name which will then open up an IE window to a website.
The thing i want ideally is if there is any way you can incorporate the websites search function into the hyperlink so when you click on it, the hyperlink will copy the phone details over to the website's search engine and take you straight to the online details of the phone?
wow this isnt making sense to me!
For e.g
The identity code shows up a Nokia 1500
The hyperlink next to it takes in the manufacturer/model details "nokia 5300" (these will obviously vary each time so takes in a cell reference should i say)
It opens up the ie window e.g "google" and automatically searches for that "nokia 5300" and comes up straight away with the search results.
Is there a way of using something like hyperlinks in VBA code to open a user form?
I presently am using hyperlinks for coworkers to open other pages that resemble a user form.
I would like to change this to actually using user forms.
I would prefer to simply click on a person's name and open the form.
I need returning a working hyperlink from a vlookup. I have a table with part #'s and hyperlinks. On another tab I want to type in a part number, have it search the table and return the working hyperlink. This will allow the user to just click it and go to that tab.
Here is my example spreadsheet : Book1.xlsm
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well.
My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference.
When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
When I click on a hyperlink inside an Excel spreadsheet, it is linked to a PDF file. When clicked, the screen flashes the Adobe Acrobat program for a split second and then disappears. But I can open the hyperlink same as which it is linked to a word file in the same directory.
I have gone into folder options and switched the PDF to open with Adobe reader 8, but that does nothing.
Any ideas of how to get the PDF to open and stay open would be most appreciated.
I tried to install adobe reader 4.0 without uninstall adobe 7.0 professional.
I use pdf reader 4.0 open the Pdf file in the window explorer, and then go to excel, open the same hypelink pdf file, and find it works now only opening pdf document by pdf reader 4.0 but not by 7.0.
But when I resinstall adobe 7.0 professional, and the problem still occurs. I have to open the original pdf file in the directory by adobe reader 4.0 again.
My case is like the following Macro in excel.
Setup hyperlink and open it . Then the problem is shown as my previous thread mentioned.
Sub Review() .....
The following is a simplified example of my problem: I have a list of product item numbers in column A. Next to each item number in column B, is a hyperlink to a PDF file relevant to each item. Elsewhere in the sheet, is a drop down box containing the item numbers. Next to the drop down box is a HYPERLINK(VLOOKUP) that fetches the relevant hyperlink depending upon the selected item in the drop down box. Problem is, when I select the ‘lookup’ link, I get an error message ‘cannot open the specified file’ (the original Hyperlink works fine). Does anyone have a solution to this problem (preferably without resorting to Visual Basic?
View 13 Replies View RelatedI have a database of two columns - a file name and a hyperlink for the folder path to that form. What I would like to do is a VLOOKUP type search by the form name and the result be the hyperlink itself. The problem is that vlookup basically provides a "Values Only" response and doesn't carry over the hyperlink format.
View 2 Replies View RelatedI made the mistake of creating hyperlinks to files on a server (using the \SERVERNAME convention) without using the "hyperlink base" option.
I've been sufficiently chastened, but the present problem is that I have many such hyperlinks that are now worthless because, when saved, they point to "../../[foldername1]/[foldername2]/[filename]", etc.
Need macro that would replace the first five characters ("../..") of all the hyperlinks on my sheet with //[MYSERVERNAME] ?
how to use a combo box to select hyperlinks to take you off to the selected website? I have this, which works but it's hard-coded - I'm looking for something a little more dynamic, i.e. pick up hyperlinks from a range on a sheet.
View 2 Replies View RelatedCan a message box support a hyperlink? I prompt a user with a message box with the text to a website, can the text for the website be a hyperlink in that message?
View 2 Replies View RelatedHave the following dilemma in Excel: If I enter a value in cell A3 and the value corresponds to a reference value in one of cells B4:B12, can I hyperlink data in cell range C4: C8 to elsewhere on the sheet (eg J4: J8).
Example.
I have 8 meeting rooms that each have a dedicated code, AU, BR, FOR, TO, M1, M2, M3, M4
When I enter a code in cell A3, (eg AU) I want the data in cells A4: A8 (date, start, end, event) to be reproduced in another table on the sheet.
In other words: If A3 is equivalent to a value in cells B4: B12, then can data in C4:C8 be reproduced in J4: J8?
I'm trying to build a workbook that has a drop down box with 5 options. I want to be able to select an option out of the drop down menu, and have about 5 cells populate on that same sheet based on the what I select in the drop down box. I used Data Validation and List to make my drop down box, so that part is no problem. I can use an if statement to populate cells with names based on what I choose in the drop down box, so that's not really my problem either. The problem I have is that I was to make each name that populates based on what I select in the drop down box to be a hyperlink to a separate sheet within the same workbook.
I need these hyperlinks to go to different sheets based on what I choose from the drop down box. To tell you exactly what I'm doing: I have 5 locations. Each location has 3 to 5 supervisors. Each supervisors has 6 to 8 laborers under them. I want to have a drop down box with my location names. When I choose a location, I want to see the names of the supervisors at that location populate. I then want to be able to click on the name of the supervisor and have a hyperlink to a different page within that workbook to tell me data on the laborers under the supervisor..
Is there anyway in VBA, that if a hyperlink is clicked on in the spreadsheet that it won't follow the link?
I need it to be a link in every other respect because I will be parsing the Text to Display, and utilizing the links address. I just would like to not have the link be clickable on the spreadsheet.
I have a workbook with 32 sheets. The first 31 sheets are hyperlinked to the 32nd sheet, which holds the summary from the other 31 sheets. Now, if I am in Sheet7 and click on the hyperlink in sheet7, it will take me to the summary sheet. That part is OK. But I want to know is it possible to go back to the sheet from where I reached the summary sheet (in this case sheet7 itself) with hyperlinks.
View 9 Replies View RelatedI am working on a database for a client. The database has a few worksheets with an overview (contents) worksheet. What I am trying to do is create a hyperlink from the contents page to the corresponding worksheet. I can do this no problem...my problem is that if I add more data to the worksheet the hyperlink is lost. I am linking the sheets via cells so if a cell is moved down to add a new cell the hyperlink is no longer valid. Is there a rule I can create to keep the hyperlink and not have to adjust it every time new data is added.
View 9 Replies View RelatedI have a database in excel. Each row in my database is usable information that has a link to Word files on our server and the database is saved on the server.
Whenever I move the original file which is on the server anywhere it causes all the hyperlinks to change. I have saved it on my desktop then moved it, and it still changes my hyperlinks. I don't understand why my hyperlinks don't just stay they way I created them.
Is there a way to keep my hyperlinks from changing? If you notice the immediate folder of the filename doesn't change.
Example:
Original hyperlink:
//server/reports/filename
Changed hyperlink:
C:/document and settings/user/microsoft/reports/filename
I have TextBox1 where I input a 1,2,3, etc...
In TextBox2, depending on the number input in Textbox1 I would like to assign a hyperlink to appear in TextBox2. I have been finding that this is nearly impossible. Are there any other viable options I should look at?
I have a database in excel that has hyperlinks to each document. The problem i have is that i need to move the documents to a new folder, therefore rendering the hyperlinks useless. I have over 1000 hyperlinks that needs to be updated.
what i need to do is the change the current hyperlink to insert the name of the new folder that these documents are to be placed in.
i.e.
c:1 management........
needs to become
c:Registered folders1 Management........
Is there a way of adding the name Registered folders to the path of the hyperlinks for every document automatically, as doing this manually will be too time consuming.
I have a worksheet with 28 sheets. I want the first sheet to be the only sheet displayed when opening the file and then want people to have the ability to click a hyperlink to unhide a particular sheet. Then when they go back to the main page (via another hyperlink), the sheet that was unhidden will be hidden again. I was able to put together some code from [URL]. When I click on a hyperlink on the main page, I get a "Subscript out of range" error and it highlights Sheets(strSheet).Visible=True from the Worksheet_FollowHyperlink code. I made a basic spreadsheet with just 3 sheets and plugged this code in and it worked fine so I'm not quite sure what the error is. I attached the spreadsheet I'm using.
View 3 Replies View RelatedHere the situation I have a list of around 2000 hyperlinks from my workbook and its various sheets. I have a pre made list of all the hyper links that there should be unfortunelty you know how things go and Im off by some here and there. What I need to figure out is how to match up my hyper link list to my pre made list. Im thinking an if statement looking for keys (truck #, Trailer #, Date) to get me the closest matches I can without having to go through every single hyper link....
Im thinking i need to match the criteria (truck #, Trailer #, Date) to that of the data i got from the hyper links. Ill explain more on my attached work sheet so it is more clear to under stand.....
In column A I have a range of file path extensions (i.e., C:UsersJSmithDocumentsModelsHic Sunt DraconesTest.xlsx). What I want is a macro that will use those file path extensions to create hyperlinks in column B.
So far this is what I have:
VB:
Sub Hyperlink()
Range("a1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Range("b1").Value, SubAddress:= _
"", TextToDisplay:="Link"
End Sub
This code works for the first cell, but does not work for the rest of the cells in the range. Also. Keep in mind that the range of cells in column A is dynamic - it changes in length.
I need to edit the hyperlinks in my excel sheet. I know how to do this manually and edit one at a time, but I need to change 3000 of them!
Is there a way I can do this without going through each one?
So I have a code that creates a refreshable list of sheet names in a TOC (I call it a "directory" in the code). Problem is it creates the ugly standard hyper link look. I can format the look of the cells, of course, but when the refresh button is hit, the formatting is erased and it all goes back to the standard hyperlink blue underlined look. How to keep the formatting permanent?
Here is the macro I am using - [Code] ..........