VBA Making Me Update Values When Using Values with Spaces

CtrlAltRage

New Member
Joined
Aug 23, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that creates new sheets when a new value is entered in the first sheet. It then make the value that was entered in the first sheet clickable so when I select it - it automatically takes me to the newly created sheet.

For some reason - the only way I can get it to work is if I do not use spaces for the values I enter in.

Otherwise I get this prompt:

Screenshot_1.png


The script is below:
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then
        Sheets("Project Template").Copy after:=Sheets("PROJECTS")
        ActiveSheet.Name = Target.Value
        ActiveSheet.[A1] = ActiveSheet.Name
    End If
    Me.Activate
    Target.Offset(, 2).Formula = "=" & Target.Value & "!D1"
    Target.Offset(, 3).Formula = "=" & Target.Value & "!G13"
    
End Sub
    'ADDED
    '--------------------------------------------------------------------
Private Sub Worksheet_selectionChange(ByVal Target As Range)
    If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then Exit Sub
    Sheets(Target.Value).Activate
End Sub

Any and all advice would be greatly appreciated, thanks!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Z51

Well-known Member
Joined
Dec 12, 2007
Messages
699
Office Version
  1. 365
  2. 2016
When referencing sheet names with spaces, you need to add single apostrophes like so:
VBA Code:
    Target.Offset(, 2).Formula = "='" & Target.Value & "'!D1"
    Target.Offset(, 3).Formula = "='" & Target.Value & "'!G13"
(Between the = and " signs, and between the " and ! signs.)
 
Upvote 0

CtrlAltRage

New Member
Joined
Aug 23, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
When referencing sheet names with spaces, you need to add single apostrophes like so:
VBA Code:
    Target.Offset(, 2).Formula = "='" & Target.Value & "'!D1"
    Target.Offset(, 3).Formula = "='" & Target.Value & "'!G13"
(Between the = and " signs, and between the " and ! signs.)
You have saved me so much frustration - thank you! It's amazing how that one simple fix alleviated all the time I was spending trying to figure it out.

The only thing I will say though - when I update the script with the ' between the =" and "! the cell doesn't take me to the target sheet when I click on it. Am I missing something?
 
Upvote 0

Z51

Well-known Member
Joined
Dec 12, 2007
Messages
699
Office Version
  1. 365
  2. 2016
You have to add the apostrophes in the SelectionChange event, too...
VBA Code:
If Not Evaluate("=ISREF('" & Target.Value & "'!A1)") Then Exit Sub
 
Upvote 0
Solution

CtrlAltRage

New Member
Joined
Aug 23, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
You have to add the apostrophes in the SelectionChange event, too...
VBA Code:
If Not Evaluate("=ISREF('" & Target.Value & "'!A1)") Then Exit Sub
You are a wonderful person haha

Thank you so much for the help!
 
Upvote 0

Z51

Well-known Member
Joined
Dec 12, 2007
Messages
699
Office Version
  1. 365
  2. 2016
While you're at it, you may just want to add them to the Evaluate line in the Worksheet_Change event, too.
VBA Code:
If Not Evaluate("=ISREF('" & Target.Value & "'!A1)") Then
 
Upvote 0

Forum statistics

Threads
1,186,318
Messages
5,957,163
Members
438,291
Latest member
Mel2023

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top