Defining a Named Range with VBA

rlansing

New Member
Joined
Mar 4, 2009
Messages
8
The only knowledge I have of vba is from browsing code snippets on the web. I need help with naming a range based on the worksheet name. Below is what I've tried, run from a command button:

Private Sub CommandButton1_Click()
Dim ProjID As String
ProjID = Left(ActiveSheet.Name, 4)
ThisWorkbook.Names.Add Name:=ProjID, RefersTo:="=$B$4:$K$23", Visible:=True
End Sub

This returns a runtime '1004' error: That name is not valid, on the red line. It works if I use an explicit string (e.g. "TestString") in place of ProjID, so I suspect the problem may be with how the Dim statement is used. But again, I'm vba illiterate.

Thanks for any help you can send my way.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hello and welcome to MrExcel.

That code works for me. What is the name of the sheet? Maybe you're generating an 'illegal' name.
 
Upvote 0
The sheet name typically starts with a number, followed by a description, for example: 9112 Joe's Bar and Grill.
 
Upvote 0
A name can't begin with a number. Try

Code:
Private Sub CommandButton1_Click()
Dim ProjID As String
ProjID = "_" & Left(ActiveSheet.Name, 4)
ThisWorkbook.Names.Add Name:=ProjID, RefersTo:="=$B$4:$K$23", Visible:=True
End Sub
 
Upvote 0
That works perfectly. Thank you. As a follow up, I tried replacing the "_" with "P", but receive an "That name is not valid" error. Is the underscore magical?
 
Upvote 0
Another rule with names is that they mustn't look like a cell address. So P9912 wouldn't be a 'legal' name. Starting the name with an underscore is a pretty sure fire way of making it acceptable.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top