I have a workbook with a template worksheet. I can generate new worksheets using the Template, and create hyperlink to the new worksheet (Let's call it "New"). However, if an user changes the name of the worksheet from "New" to "SomethingElse", then the hyperlink breaks. How do I get the hyperlink to be automatically updated to "SomethingElse"?
View 4 RepliesIs there a way to prevent the Workbook_SheetChange event macro from triggering when I run Excel's Solver?
The solver goes through 100 or so iterations changing multiple cells each of which seemed to trigger the Workbook_SheetChange event. If there was a Solver Run event, I could turn off application events, but as far as I know such an event does not exist.
VB:
Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("C1").Value
End If
Next
End Sub
I change my tab names with the above but than my hyperlinks breaks.
Is there anyway to turn off hyperlink. Everytime I put in an @ symbol in the cell and hit enter, it changes it to a hyperlink and changes the font and size. Is there anyway to turn this off or prevent this without having to click on the cell everytime and choose, remove hyperlink.
View 3 Replies View RelatedI'm working with text boxes in Excel that have hyperlinks attached to defined names on a worksheet. Basically just to navigate around a large sheet.
They work fine, but when the mouse is hovered over the text box it automatically displays the address of the defined name, which looks messy and I want to get rid of it.
I have tried inserting a single space in the screen tip box, but that displays a small rectangular box. I have also tried "" hoping nullstring maybe interpreted as not to display, but it instead displays the speech marks.
Has anybody done this before? I've seen it done, but I have no idea how the model creator did it.
Using 2003 - Is it possible to prevent cell data entry automatically creating a hyperlink for email/web url's. Using
View 4 Replies View RelatedI have a Sheet in a workbook that has about 250 Unique supervisors in it (column A) It has about 2300 rows of employees. Is there a macro that can take the employees supervisor column (A) and break the rows associated to that supervisor into a new sheet & and name the sheet with the supervisor’s name? Can excel have 250 sheets (2003 version)?
View 3 Replies View RelatedIn my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name.
I have a macro that exports the data in g3:g6 to another workbook and then deletes it.
What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).
I have a spreadsheet that is very large and formated a certain way. I want to make sure that a user cant put the cursor in the lower left corner and copy cells by dragging, copy & paste, or do anything other than input data and be able to highleght text and reinput data in case they mistyped or misspelled the data.
ex: user clicks on cell E33 and types in '1234568' and then move to next cell. The user can click back on E33 and can correct by retyping. That is all the user is allowed.
The cells in question are also in ranges if this helps. EX: 'E33:AJ67'
I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.
The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc. I have the following code in the ThisWorkbook section:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim sOldName As String
Application. ScreenUpdating = False
sOldName = ActiveSheet.Name
If Target.Address <> "$B$1" Then Exit Sub
sName = ActiveSheet.Range("B1")
On Error Goto ErrorHandler
ActiveSheet.Name = sName
On Error Goto 0
Sheet8.Select 'this is the overview sheet
Cells. Find(What:=sOldName, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate........................
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies View RelatedI have a question regarding hyperlinks...now what i wish to do is force a hyperlink in what i thoguht would be esily dne in a worksheet change event...hw wrong was i.
Lets say i type 'test' in A1 and press enter...now because i ave pressed enter A1 is no longer the active cell so the code in the sheet change event inserts the code into A2 but because A is blank the 'Text to display' becomes the hyperlinks address.
I wrote a simple with/ end with thinking it would work but it doesnt because of the issue above (Hope i am making sense here)
I got round it by copying the activecell (which would be the cell underneath) and pasting it back to the original target cell, which because it contains text does what i wantbut i am hoping for a much cleaner solution.....
My target column is '1' and only one i will just need to exit the sub anywhere else on the sheet
I'm using Excel 2010. Is there any solution which cover requirements given below.
1) I've converted a range in to table format (using Format as Table)
2) Need to Prevent pasting on data validation cell.
3) Need to Prevent change in cell format after pasting data (other than data validation cells as mentioned above) from same workbook or other workbook
4) Need to insert row in the table as per requirement.
When changes are made to one sheet, I want it to update a table in another sheet. However, a good portion of the changes will involve adding hyperlinks to my original sheet. However adding hyperlinks doesn't seem to fire the worksheet_change event.
Is there a way to listen for when a hyperlink is added (and to which cell)?
I have a question regarding hyperlinks. I created a combo box where if I select a product (s), the sales data will change in another cell by using the offset formula. I was wondering if there is a way where each data that changes in the cell can have a hyperlink to a different location in the workbook? Can you do this by a formula or only use of VBA?
View 1 Replies View RelatedIs there a way that under "Type the Cell Reference" that you can make it automatically change to the cell the hyperlink is on?
The reason why i ask this is because I have hyperlinks linking to there current cell but once i delete a row above that... the cell refernece doesnt change therefore changing the cell reference to the cell above it
I am trying to hyperlink to a cell, & i want to change the color of this cell after the hyperlink activated.
I have a multisheet workbook where I am trying to modify all hyperlinks.
The links are all to jpgs and are currently on a web server, but I want to change them all to reference a local folder.
Eg: [url]
Obviously, I'll leave "file.jpg" alone as I don't want the specific image to change, just the place where it is trying to find the image.
I have a column with entrys of 2 kinds. Some being Hyperlinks and others with normal type data. All cells are locked with password except for cells that will possibly have entrys made in them.
Is there a way to move the active cell to R1C1 after any hyperlink clicked? R1C1 is unlocked.
I created this hyperlink function. It links to a1 of sheet 1 to a1 of sheet 2. However, when I click on the a1 cell in sheet 1. an error message appears. I have attached the spreadsheet...
View 2 Replies View RelatedIs it possible to prevent a particular sheet from being deleted?
I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.
I have been searching for a way to prevent a user from changing a worksheet name. I ran into some code from the post below (see link) but I find that the code lets the user change the name and save the changed name of a sheet; only when the user selects a different sheet does it change the name back to the set name (sname) from the code.
I'm sure there must be a way to make this happen without deactivating the sheet, but I can't figure it out. Is there some other sort of event that could cause this to happen.
I have an Excel sheet that displays 50 rows and 20 columns when it is opened the first time. These rows and columns are the only ones that I want the user to be able to see. The user shouldn't be able to navigate the sheet by using the scroll bars or the wheel on the mouse. I have of course already removed the scroll bars but I don't know how to handle the scroll wheel problem.
View 2 Replies View RelatedIs it possible to make it so that a user cannot delete or add sheets to a work book, except through buttons assigned to macros? I've tried simply locking them, but these sheets need to be accessed to input and manipulate data. I have the macros necessary to delete/add sheets, but right now they are redundant since the user can simply do it manually.
View 3 Replies View RelatedI'm an intern working at an aerospace manufacturing house and I need to automate a lot of the excel files I created last summer.
Anyway, here is my problem:
I've been making macros but I am worried about people moving the worksheets around or changing their names which will have the potential to mess up the macros. What preventive measures can be taken/ changes in the code? I feel like there is something very simple I am missing.
I have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?
View 3 Replies View RelatedIs there a method by which I can prevent a user from renaming a worksheet by doubleclicking on the sheet tab ? Please note that I do not wish to use workbook protect method.
View 9 Replies View RelatedI have a created recording sheet (attached) for the purpose of recording moths. I send this sheet out to those recorders who live in my county and at the end of the year they send the filled in sheet back to me. Over time I have put safeguards onto the sheet to ensure that I get the cleanest data returned to me. However, there is still one area which is giving me problems - users entering duplicate records.
Some formula which will stop recorders from entering those duplicate records. There are many columns on the sheet which recorders have to fill in with information, but as far as the duplicate records are concerned it is where the same information is entered into columns C, F and J that I'm having a problem with. It would be great if, when the same information was entered in these columns that the affected cells were highlighted in some way and if an error message appeared saying 'Duplicate record, either delete or alter grid reference'
I have entered two test duplicate record on the 'macro moths' sheet of the attached file : Test Recording sheet.xls‎
on sheet 1, i have 6 combo boxes ... one for each semester of Grade 8, Grade 9, and Grade 10.
Each is completely independent of all the others and each shows the subject codes for that grade in that semester.
what I would like to do is ..
if I click on (let's say ) box 3, and choose the 4th subject code down the box, I would like to be 'teleported' to sheet that bears that same subject code.
Is this possible, and is it a problem that the other 5 combo boxes will still have a subject code in their display window from previous viewings ?
This HAS to be a simple fix. Could not find a string with this solution in it yet.
Trying to set up a hyperlink to reference another location in the same sheet.
Used the example in Excel as a model to set this up so placed this formula in cell B1:
=hyperlink([Book1]Sheet1!a1,"LINK")
this defaults to =hyperlink(Sheet1!a1,"LINK") since Book1 is the current wb.
clicking on "link" in cell B1 produces "Cannot open specified file" error.
I need the hyperlink to be dynamic, so I can't use the CTRL+K hyperlink function.