Hyperlink Code For Sheet Names With Spaces Or Dashes
Sep 7, 2007
I believe I have 99% of what I need. The code below has one bug. (See just the underlined text for a very quick overview. The rest is detail.)
My goal is to generate a TOC that:
1) has the VBA run from my Personal Workbook.
2) can be run in any worksheet of any open workbook.
3) can be run in a worksheet of any name.
4) will insert itself at the currently active cell.
5) has entries hyperlinking to the referenced worksheets - of any name.
Right now, I'm stuck with the last point. The macro below will generate the TOC at the currently active cell. The TOC will be hyperlinks to the referenced worksheets.
However, if the referenced worksheet contains a space, dash, or other special character then the generated hyperlink is broken. For example, if the referenced worksheet is "sheet1" then the hyperlink generated works just fine. However, if the referenced worksheet is "sheet-1" then the generated hyperlink doesn't work.
Sub IndexList()
Dim objSheet As Object
Dim intRow As Integer
Dim strCol As Integer
Set objSheet = Excel.Sheets
intRow = ActiveCell.Row 'Start writing in active row
strCol = ActiveCell.Column 'Start writing in active column
For Each objSheet In ActiveWorkbook.Sheets
Cells(intRow, strCol).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
objSheet.Name & "!A1", TextToDisplay:=objSheet.Name
intRow = intRow + 1
Next
End Sub
View 7 Replies
ADVERTISEMENT
Mar 4, 2009
I have been using the code below for a form. But now I need to change the sheet name to have spaces. But when I do. I comes up with the Run Time 1004 error on the “Ctrl.Text = Range(Ctrl.Tag).Text '*** “ line.
Why is it that this line is not allowing me to have spaces?
Private Sub Load_Controls()
Dim Ctrl As Control
For Each Ctrl In UserForm1.Controls
If Ctrl.Tag "" Then
Ctrl.Text = Range(Ctrl.Tag).Text '***
End If
Next Ctrl
End Sub
View 9 Replies
View Related
Dec 8, 2011
Is there a macro I can write that will delete all spaces in all the sheet names in a workbook?
View 4 Replies
View Related
Apr 30, 2014
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.
View 4 Replies
View Related
Jan 18, 2012
I have the following macro, which is assigned to ctrl+shift+h to allow users to make a list and to insert hyperlinks to other sheets that have more details than the list can show:
Code:
Sub linker()
Dim strDocument As String
Dim strSheet As String
Dim strAddress As String
Dim strLink As String
[Code] .......
I was surprised to see that even if the contents of the active cell is not a valid sheet name in the target document, the link is still inserted without error. If the link is subsequently clicked, the target document will open and an "invalid reference" error box will appear.
Is there any way to check whether the contents of the cell constitutes a valid sheet name in the target workbook at the point at which the hyperlink is created, without opening up the target document?
View 4 Replies
View Related
May 11, 2006
I attach an example worksheet with the code I have thus far.
In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.
I then change the name of the sheet to the name of the client.
I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".
how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,
View 8 Replies
View Related
Oct 15, 2013
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
View 2 Replies
View Related
Mar 27, 2014
I would like to hyperlink the names in sheet 1 to the same names in sheet two. Is there a way I could do this using a formula rather that do each one by one?
View 3 Replies
View Related
Dec 31, 2006
Reference Sheets in VBA.
If the default tab name varies from country to country, is the CodeName the same in all languages - "Sheet1", "Sheet2" etc.?
View 9 Replies
View Related
Jul 18, 2006
I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.
Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.
This is what I have so far
For J = 31 To 49
ResultSht = SheetJ.Name
Sheet1.Activate
ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select
It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?
View 9 Replies
View Related
Feb 20, 2013
I'm about get code for list out the sheet names in current workbook, list would be displayed in a new sheet at the end.
View 3 Replies
View Related
Oct 20, 2008
I have a workbook with 20+ sheets in it, I add sheets and delete sheets on a daily basis, except for one sheet that is like my summary sheet.
Is there a code, formula, or magic spell that will list the names of the sheets that I have deleted? For instance, if my workbook has 50 sheets and I delete 49 of them, I want to see cells A1 thru A49 (or where ever I wish to place them) filled with the names of the sheets I just deleted.
View 9 Replies
View Related
Jul 2, 2009
I 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
View 10 Replies
View Related
Jan 11, 2008
I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -
View 5 Replies
View Related
Mar 31, 2014
I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
[Code]....
View 1 Replies
View Related
May 7, 2012
Can I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies
View Related
May 21, 2013
I need a macro to create a hyperlink i.e....... I have an excel sheet with numbers in column A.....I have files in a folder that matches the numbers in column A....
How would I create a macro to create a hyperlink from the numbers in column A to the "matching" file names in a folder?
View 3 Replies
View Related
May 1, 2008
Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
View 9 Replies
View Related
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Mar 22, 2007
I have a macro which opens one excel file, then copies the data into another, dead easy. However the first file is 'downloaded' from a bespoke package, where (for whatever reason) the package appends a number of spaces (" ") after data in one of the columns,
So sometimes the data will contain one, ten or more extra spaces (no telling how many) ie, it could look like "AB ", "AB ", or "AB " etc
Ideally What i need is a small bit of code that once the data has been imported to my sheet it can run and 'strip' extra spaces from the column, lets say column f, to leave all the data in this column to look like:
"AB"
"AF"
"CD1"
"VFE"
I am drawing a blank, any simple lines of code?
View 9 Replies
View Related
Mar 12, 2013
What I am doing is setting up a product ratecard sheet to run with our CRM system in work.
Each product has 10 quantities, with 10 different prices, but each product must have the same product code running down in column A
I have thousands of products to put in to dont want to manually type each code in.
I am ok with Excel, but I can not program in VBA, so i am looking for a formula to use.
The code is ZTRA-00001, which will stay the same for 10 rows, then there will be a space of 1 row and the next code for the next 10 rows in the column would be ZTRA-00002 etc.
View 1 Replies
View Related
Dec 5, 2007
Sub Increment()
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row
If IsNumeric(Cells(Lr, "A")) Then
Cells(Lr + 1, "A").Value = Cells(Lr, "A").Value + 1
End If
End Sub
This adds 1 to the previous cell and displays in the next available cell.
How could I make it sao that when this number enters in the cell it hyperlinks automatically to the master file?
Before this button was introduced I used this:
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo Err_App_WorkbookBeforeSave
Dim hl As Hyperlink
For Each hl In Me.Hyperlinks
If Wb.FullName = hl.Address Then
Application.EnableEvents = False
Cancel = True..............
View 9 Replies
View Related
Sep 28, 2011
I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.
The user selects a destination cell as part of a form, which passes the variable to the following steps:
Code:
looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
View 8 Replies
View Related
Jun 24, 2014
I have attached what my problem is. Have a look, and let me know what you think...have spent hours trying INDEX, MATCH, IF, ISTEXT...
JamesExcel problem.pdfExcel problem.pdf
View 2 Replies
View Related
Jun 14, 2006
Have a column containing data which may have 1-3 dashes locates within
each cell value.
Such as:
125-457A
158-857-1116
124184A-1
125-8745-22D9
458-2145-002-7
Would like to remove all the dashes "unless" the last dash to the right
is followed by a just a single digit. The results would be as follows:
125457A
1588571116
124184A-1
125874522D9
4582145002-7
On the example data, it kept 2 of the dashes in the cell values,
because the were only one digit away from the extreme right, but
removed all others.
View 9 Replies
View Related
Dec 31, 2013
I need a code in VBA which I have to use the Hyperlink within the workbook e.g. "Place in This Document.
Assuming I have two Worksheets WS1 = Main, and WS2 = Reference respectively
In WS1=Main, I have a list in Column from E3 to E1000. let say E3=1, E4=2, E5=3, E6=4, E7=5..., so on and so forth.
In WS2=Reference, I have a list in Column from F5 to F1002. let say F5=1, F6=2, F7=3, F8=4, F9=5..., so on and so forth.
What I want is if I click the E3=1, E4=2, E5=3, E6=4, E7=5..., in WS1=Main if will jump to specific cell in WS2=Reference e.g. F5=1, F6=2, F7=3, F8=4, F9=5...,
View 1 Replies
View Related
Nov 15, 2009
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 Related
May 11, 2009
I have a column of city names, some are one word and others are two or more. I am trying to take the city name and add it to another word, add dashes in between them and make everything lower case. For example:
Column 1, Cell 1 is: San Diego
I'd like it to look like: books-san-diego (books being the keyword I'd like to insert before the city.)
View 3 Replies
View Related
May 27, 2009
I have a column of data (column A) with cells that have IDs (alpha-numeric) that contain anywhere between 0 to 5 dashes. Since this column will change weekly, I want to dynamically pull only those cells that contain 3 dashes.
Example:
10648145-3026718-t526363536m
09wcg7-s1s89-wtpxm33-uu6jj0-9y8mv-v1
10658575-2349769-
I would only want to have returned 10648145-3026718-t526363536m since it contains 3 dashes. Do you know of a nifty formula for doing this? I think an array would get me the dynamic piece (so they are not spaced out).
View 3 Replies
View Related
Jul 6, 2007
Here is the examples of values i have
TCH1 - John Smith - I - REG - Total
AV - John Doe - D - REG - Total
what i want to do is lookup on the value after the first dash and before the second dash to get the names only ; John smith, john doe
View 9 Replies
View Related