Re-populating Hyperlinks That Have Been Cleared
Nov 3, 2009
I have a worksheet that I have removed a good number of hyperlinks, using the remove hyperlink function. My problem is that every so often, the hyperlinks are back on the sheet, without seeing any wording for the hyperlink.
Some place, a long time ago, I saw that there was something in the program that either stopped this from happening or turned the function off or a way of restoring removed hyperlinks. Today, I can't find it.
If I run my cursor over the spreadsheet, even over blank cells, I will get the evil finger of fate (so to speak) indicating that there is a hyperlink in that cell and yes, there is. If I click the cell, the site will be activated.
How do I keep these removed hyperlinks from returning.
I've also noticed that at anytime, while working on the sheet, everything flickers just as if the links are being re-populated. After a second or two the flickering stops and yes, the links are back.
View 12 Replies
ADVERTISEMENT
Feb 21, 2007
I 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
View 6 Replies
View Related
Jul 22, 2009
I'm sure this has an easy solution, but I'm having a problem putting it into a working formula. Column I lists checks numbers. Column J contains the amount of the check. Column K contains a "Y" when the check clears the account, otherwise it's left blank until the item clears. Cell O2 is where I want the balance formula.
I want O2 to look at Column K & then sum the amounts from Column J where the corresponding Column K is blank. If it were just one cell, it's a pretty easy if(k4="Y","", j4), right? But since it's a range of cells, not sure how to tell it to look down the range for all uncleared checks and put the sum in one cell.
View 2 Replies
View Related
Jan 1, 2014
I have a macro that runs with worksheet_change if I delete a cell. It does not run if I just clear contents. Is it possible to have the macro run when I clear contents of a cell?
View 4 Replies
View Related
Aug 18, 2006
I have a rather large userform with numerous textboxes and comboboxes. Data entered into the text and combo boxes is written to the next empty row of a sheet. As the data to be entered into the next row may not "all" vary from the last I would like to have the data I have last selected or entered remain in the textbox or comboboxes for the next. I attach an example for a better explaination.
View 6 Replies
View Related
Nov 2, 2009
I need help with a code to detect deletion of a cell's value.
In cell I10, a user can choose one of six items from a data validation list. Right now, I have code in there to pop up a message if the user selects value "X" from cells F6:F10 and G6:G10, but cell I10 does not have value "A", "B" or "C" already selected.
What I want, is if cells F6:F10 and G6:G10 have value "X" in them, and the user deletes values "A", "B" or "C" from I10 or changes it in any way after it has been filled, then to have a message box pop up.
View 7 Replies
View Related
Aug 23, 2006
I have a legacy Excel VB application with multiple sheets. When the
users tab from sheetA to sheetB the paste buffer is cleared. This might
be due to the fact that sheetB has Worksheet_Activate processing that
does some validation and refreshes a pivot table (on sheetB).
Is there a way to reserve and restore the contents of the paste buffer,
and make sure the user can actually paste values from sheetA to sheetB
(using either Ctrl-V or Edit-Paste)?
View 3 Replies
View Related
Feb 19, 2014
I'm not sure if this is impossible (surely not) or if I'm just Googling the wrong terms (most likely). I have a sheet that we use to record staff shifts and when they take their lunch breaks. By default most of our employees have a 1 hour lunch and they take it 4 hours after they start. So far so good, I've set up a formula to work out what time their lunch would start and end based on this.
N237: =IF(AND(L237>0,L237<>"NWD"),SUM(L237+(1/6)),0) - Lunch start 4 hours after start time in L237 (NWD = Non working day)
M237: =IF(N237>0,SUM(N237+(1/24)),0) - Lunch ends 1 hour after lunch start time in N237
However, not everybody follows the default - some people take lunch later, or don't take as long due to working shorter hours.
What I'd like to do is allow users of the sheet to overtype the times generated by the formulas when applicable, but if they delete their own custom value for lunch breaks I would like the formulas to re-appear and thereby return lunch times to the default values.
I could do this through VBA with a button to look for blank cells, but I would prefer it to happen automatically. Is this possible and if so what code would I need to make the magic happen?
I should maybe have mentioned that this sheet has space for 250 names and an area for each day of the week, so these formulas would appear in 1,250 different places
View 4 Replies
View Related
Apr 25, 2014
I'm trying to use the Worksheet_Change event to replace all cleared cells with a default formula. This allows users to override the default, or restore the default when they need to:
If Not Intersect(Target, Range("Data")) Is Nothing And Target.Value = "" Then
Range("DefaultFormula").Copy
ActiveSheet.Paste
ActiveSheet.Application.CutCopyMode = False
End If
Here are the results with various different regions selected:
Single cell selected and cleared -- works. Multiple cells selected and cleared -- doesn't work if the first area selected contains more than one cell. Otherwise, it works, no matter how many other areas are selected.
Example1: Selected range is (D2:E2, G2) in that order, it does not copy the formula into the cleared cells. Example2: Selected range is (G2, D2:E2) in that order, the code does copy the formula into all the cleared cells as desired.
The "IF" condition is not TRUE when the initial area has more than one cell.
View 6 Replies
View Related
Mar 18, 2008
I am trying to create a way to easily maintain a list of outstanding checks. I am able to import cleared checks into the spread sheet but cannot find a way to compare the two lists and then delete the cleared checks off the original list.
View 2 Replies
View Related
Jun 29, 2006
I'm having a problem in a single line of code in which I'm using a Range Object to essentially select an area of cells whose contents I want cleared.
Here is the
Sheets(Left(c.Value, Len(c.Value) - 1)).Range(Cells(4, 2), " &:& ", Cells(Rows.Count, "b").End(xlUp).Offset(0, 9).Select).ClearContents
I want the range part to evaluate as (b4:whatever the last cell is before the first blank row).
View 9 Replies
View Related
Apr 27, 2013
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
Screen shot of after re-opening, no changes:
Report looks terrible (not real data):
View 2 Replies
View Related
Apr 8, 2008
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
View 9 Replies
View Related
Apr 13, 2012
I've run into a problem detecting empty cells. I'd like to search down a column of numbers and count the cells that contain numbers and ignore the cells that are empty. If the cells were made empty of their data by manually using the delete key before hand then my macro works fine. The problem is if the cells were manually made empty using the space bar to clear their contents.. then the macro seems unable to detect them as empty.
I have tried "", IsEmpty, and a few other things and nothing detects the cell as empty unless the delete key is used rather than the space bar. For this macro I really need the user to be able to clear values beforehand using the space bar, and to have those cleared cells be detected as empty cells.
View 6 Replies
View Related
Jan 26, 2008
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.
View 9 Replies
View Related
Feb 4, 2010
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.
View 9 Replies
View Related
Jan 31, 2013
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
View 2 Replies
View Related
Jun 20, 2008
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.
View 11 Replies
View Related
Jun 9, 2009
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() .....
View 6 Replies
View Related
Jan 21, 2010
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 Related
Mar 12, 2009
I 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 Related
Sep 8, 2009
I 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] ?
View 7 Replies
View Related
Oct 9, 2009
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 Related
Jan 13, 2010
Can 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 Related
Feb 2, 2010
Have 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?
View 6 Replies
View Related
Aug 28, 2012
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..
View 1 Replies
View Related
Oct 17, 2013
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.
View 9 Replies
View Related
Mar 15, 2007
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 Related
Apr 29, 2008
I 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 Related
Jun 4, 2009
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
View 9 Replies
View Related
