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

Sheet Names With Spaces In VBA

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

Macro To Delete All Spaces In All Sheet Names

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

Hyperlink Won't Keep Reference When Sheet Names Change?

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

Pre-Checking Valid Sheet Names In Hyperlink

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

Newly Created Tab Names Placed As Hyperlink On Content Sheet

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

Excel 2010 :: VBA - Hide Sheets Using Code Names Not Sheet Names

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

Hyperlink Names In Sheet 1 To Same Names In Sheet Two?

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

International Sheet Code Names

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

Referencing Sheet Code Names By Variable

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

Code To List Out Complete Sheet Names In Workbook

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

VBA Code For Listing The Sheet Names Of Deleted Sheets

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

Hyperlink In Cell Have Email CODE But Hyperlink Is Not Clickable

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

Hyperlink Index To Changing Sheets Tab Names

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

Dynamic Hyperlink To File Names Found On Local Drive?

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

Can Create Data Validation List Of Names Created In Name Box Or Sheet Tab Names

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

Create Hyperlink From Numbers In Column To Matching File Names In A Folder

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

Create Array Of File Names/sheet Names

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

List Sheet Names And Internal Names Within Workbook

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

Code To Remove Extra Spaces From Data.

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

Sequential Alpha Numeric Code In Batches With Spaces

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

Put Hyperlink Code In This Button Code

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

Spaces In Sheet Name Stops Macro

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

Reference Array Of Cells Into Different Sheet Without Spaces

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

Remove All The Dashes

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

VBA Code To Use Hyperlink Within Workbook

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

Hyperlink Function: Links To A1 Of Sheet 1 To A1 Of Sheet 2

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

Want To Add Dashes To Cell Text...

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

Return Cells That Have Exactly 3 Dashes

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

Look Up On Cell But Based On Value Between Dashes

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







Copyrights 2005-15 www.BigResource.com, All rights reserved