Sheetname variable

  • Thread starter Thread starter Garry Jones
  • Start date Start date
G

Garry Jones

I would like to name a worksheet, but the worksheet that shall be named
is dependant upon which button is pressed.

What I want from this code is is

Sheet1.Name = "NewName"

(ie if command button 1 is pressed)

What am I missing here?
____________________________________________

Private Sub CommandButton1_Click()
setar (1)
'when Command Button 1 is clicked it sends the number 1 as a variable to
the code
End Sub
____________________________________________

Private Sub setar(ArgValue as Integer)
("Sheet" & ArgValue).Name = "NewName"
'Missing something here
End Sub
____________________________________________

I am working with Swedish Excel, in Swedish the worksheet control name
is known as "blad", I seem to remember it was "sheet" in English, if I
am wrong my posting may be confusing. Maybe its "worksheet", anyway,
that doesn't really matter, I just need the code and then I can write
"blad" anyway.


Garry Jones
 
Garry,

Try this

Private Sub setar(ArgValue As Integer)
Worksheets("Sheet" & ArgValue).Name = "NewName"
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob said:
Try this
Click to expand...
Private Sub setar(ArgValue As Integer)
Worksheets("Sheet" & ArgValue).Name = "NewName"
End Sub
Click to expand...

Thanks Bob.

I'm still lacking something. I am getting "index out of interval"
(roughly translated from Swedish).

This is the code I'm trying. What is it that I am missing?

__________________________________________

Public Sub CommandButton1_Click()
setar (1)
End Sub
__________________________________________

Public Sub CommandButton2_Click()
setar (2)
End Sub
__________________________________________

Private Sub setar(ArgValue As Integer)

Worksheets("Sheet" & ArgValue).Name = "NewName"

End Sub
__________________________________________

When button 1 is pressed I want

Sheet1 to be renamed to NewName

When button 2 is pressed I want

Sheet2 to be renamed to NewName

Syntax problem here I guess. How do I change the name of the worksheet
from a user form command button?


Garry
 
The error is telling you that setar() can't find a worksheet named
"Sheet1" or "Sheet2".

Check the spelling of your sheets and make sure there are no
extraneous spaces, etc.

Also, since you're not returning a value from setar, your
parentheses around 1 and 2 are unecessary. Use

Public Sub CommandButton1_Click()
setar 1
End Sub
 
J.E. McGimpsey said:
The error is telling you that setar() can't find a worksheet named
"Sheet1" or "Sheet2".
Click to expand...

Thank! Nearly there now.

I have misunderstood the naming used in worksheets.

Each worksheet has two names in properties

(name) and name

In the project window my sheet is shown as

Sheet1 (NewName)

My code is looking for the name "sheet1" where it says "newname".

When I have changed the name with my code to "NewName" the other name
remains "sheet1". I thought - wrongly - that my code would identify
sheet1 by its listed first name (which is still sheet1).

So I presumebly need another identifier in here in place of "worksheets"
Something that means that Sheet1 is referring to this initial name.
__________________________________________
Private Sub setar(ArgValue As Integer)

Worksheets("Sheet" & ArgValue).Name = "NewName"

End Sub
__________________________________________

But what?

I really am most gratefull for all this help I am receiving.


Garry Jones
 
You can use the CodeName (see VBA Help) directly:

Sheet1.Name = "NewName"

Note that CodeName cannot be changed programmatically at run-time
 
J.E. McGimpsey said:
You can use the CodeName (see VBA Help) directly:

Sheet1.Name = "NewName"

Note that CodeName cannot be changed programmatically at run-time
Click to expand...

Most interesting

Okay.

Each worksheet has two names in properties

(name) and name

My first worksheet is called

Sheet1 under (name)
and
newname under name

If I use the direct code Sheet1.Name = "NewNameAgain" directly it
changes the secondary name.

That's what I want to do, but I want the 1 (in sheet1.name) to be a
variable. I do not want to change the primary name "sheet1", I want to
change the secondary name (currently newname). Surely this is possible?

But

("Sheet" & ArgValue).Name = "NewName"

Is not valid syntax.

Any ideas?

Garry Jones
 
Garry,

Try something like

Sub ChangeSheetNameByCodeName(Ndx As Long, NewName As String)
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.CodeName = "Sheet" & Ndx Then
WS.Name = NewName
Exit Sub
End If
Next WS
End Sub

You can then call this with code like

Sub Test()
ChangeSheetNameByCodeName 2, "TheNewName"
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com (e-mail address removed)
 
Back
Top