How to use a variable as a sheet name

brunces,

The code below is in the attached file.

Patrick

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [A1]) Is Nothing Then
        ActiveSheet.Name = Target.Value
    End If
End Sub

                                              

1:
2:
3:
4:
5:

patrickab, thank you for your answer.

I'm so sorry, but now, after your comments, I came to know that there are some pieces of information missing.

The only code that is in an ordinary module is:
Public strOldName As String

The rest is in the Sheet1 module.

The sheet supposed to get its name changed is Sheet2, not Sheet1, where the reference name is (A1).

I'm sorry for this missing explanation.

Thanks again. :)

brunces

You need to attach the code to the Sheet1 module not a seperate module as that is where the WorkSheet Change is detected. Make sure A1 Contains the current sheet name before you start or you will get an error.

brunces,

Exactly the same macro but in the code pane for Sheet2. It's in the attached file.

Patrick

patrick, thanks again, but I think I still haven't made myself clear and I'm sorry for that.

1) I type something in cell A1 in Sheet1;
2) Sheet2 is named with that name typed in cell A1 in Sheet1 automatically.

I've created an example file. See the attachment, please.

Thanks again.

brunces

The other attachment was wrong. Here's the correct one. Thanks.

brunces

brunces,

I gave you the VBA code so that you could use it. The file you have uploaded does not use the code I provided. So I have installed my vba code (as below) in the code panes of both sheet1 and sheet2. It's in the attached file.

Change the entry in cell A1 in either sheet and the worksheet name will change automatically.

Patrick

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [A1]) Is Nothing Then
        ActiveSheet.Name = Target.Value
    End If
End Sub

                                              

1:
2:
3:
4:
5:

patrickab, I didn't use your code because it's not what I want yet.

I won't type anything in Sheet2. The only cell in which I input data is cell A1 in Sheet1. The word I type in cell A1 in Sheet1 will be the name of Sheet2. That's it. :)

Thank you.

brunces

patrickab, thanks again for your answer.

I already new about this "Sheets(2).Name", but I really wanted to use the other way. But, if it is the only way, that's OK, I will accept your solution.

Anyway, I'd really like to learn how to use a variable inside those brackets.

Thank you very much.

brunces

brunces,

>I already new about this "Sheets(2).Name", but I really wanted to use the other way.

Do please explain, as I really don't know what you mean. After all you wrote:

"I won't type anything in Sheet2. The only cell in which I input data is cell A1 in Sheet1. The word I type in cell A1 in Sheet1 will be the name of Sheet2. That's it."

From that it was clear that you type a word into cell Sheet1!A1 and that then becomes the name of Sheet2.

Having done exactly as you requested, it appears that that is not what you want. As I've already said, do please explain.

Patrick

patrickab,

I already knew how to use the function Sheets() with numbers, as you have provided me in your example...

Sheets(2).Name = Target.Value

The point is that I really wanted to know how to use a variable inside the brackets. Like this:

Sheets(Variable_containing_sheet_name_instead_of_number).Name = Target.Value

Maybe it is not possible, I don't know. I mean, this is what I want to know. If it's possible to use a variable inside the brackets, how is it done?

I didn't say your last code was wrong. By the contrary, now it is perfect. I just wanted to learn the other way.

Thank you very much for your cooperation.

brunces

Thanks.

brunces,

Thanks for the grade.

You can use it like this:

Sheets("Sheet1").Name = Target.Value

BUT if you do then Sheet2 must be named Sheet2, so you would be able to use the macro only once. Which then makes it vitually useless. So you need to use:

Sheets(2).Name = Target.Value

as Sheet(2) is what the second sheet is referred to as. Note that Sheet(2) is the second sheet from the left in the order of the tabs.

If you always want to refer to Sheet2, irrespective of its location in the tab order, then you need to use:

Sheet2.Name = Target.Value

Patrick

brunces, you should not have closed the question so quickly with only one person commenting! Here is how to do what you wanted:

There is a VBA object you can use called "worksheet". Below example is how to use it.

In that snippet of code, the variable "BruncesSheet" will continue to refer to that sheet, regardless of name change or position change.

Hope this helps you ^^

Sub ForBrunce()
 
 
Dim BruncesSheet As Worksheet
 
Set BruncesSheet = Sheets(2)
 
ActiveCell.Value = BruncesSheet.Name
 
 
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Thanks, patrickab.

I thought maybe it was possible to use variables inside Sheets(), because I have already done this with Range(). For example:

Sub TestRange()
   Dim lngRow As Long
   lngRow = 10
   Range("B" & lngRow).Activate
End Sub

This activates cell B10. You see, there's a variable (lngRow) inside the Range brackets. I thought I could do the same inside the Sheets brackets. That was my point on all my previous posts. I wanted to know whether it was possible or not. And, if so, I wanted to LEARN how. But, according to your last comments, it seems there's no way to do it. That's it.

Thanks you very much.

brunces

ummm, brunces. did you see my comment? it is possible. Except the variable is not INSIDE the sheets() brackets, it is INSTEAD of the "sheets()", but it is still what you are looking for.

Well, actually, it is possible with .Select or .Activate...

Sub TestSheets()
   Dim strName As String
   strName = "Sheet2"
   Sheets(strName).Select
End Sub

What I don't understand is why it doesn't work with .Name.

brunces

carmalize, thanks for your comments. :)

I'm sorry, I didn't see your post because my page wasn't refreshed.

Guys, here's the deal... I can't use Sheets(2) because if I do so, I LOCK my code to work only with the second sheet and that's not what I want. I want it to be flexible and, believe me, I have reasons for that. I have a big workbook here with lots of sheets and I want a single code to work with ANY sheet. That's why I've insisted so much on that.

The code I've posted here, for you to analyse and help me is just a sample. So, if I got this sample to work, it will be just a matter of adaptation to my workbook. In other words, I just need to know how to make it work, I need to learn it. Then, I'll just adapt it. :)

Thanks again, carmalize. If you have a solution to what I want, I can open another topic, so that you can get the points you deserve (if you answer first, of course, or else it would be unfair to the others). :)

brunces

Guys, it worked!!!

It REALLY is possible to use variables inside the brackets. I was correct all the time. The problem was in another part of the code. I just changed a little thing, from Value to Text. I don't know why, but this was the problem and now it's fixed.

**********************************************************
Codes:

In an ordinary module:
Public strOldName As String

In Sheet1 module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    Else:
        strOldName = Worksheets("Sheet1").Range("A1").Value
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    Else:
        On Error Resume Next
        Dim strNewName As String
        strNewName = Worksheets("Sheet1").Range("A1").Text (HERE IS WHAT I'VE CHANGED)
        Worksheets(strOldName).Name = strNewName
    End If
End Sub
**********************************************************

Thank you all very much for your efforts. :)

brunces

P.S.: Problem solved. Topic closed.

Oops! I forgot to show above... :)

This line was changed to:
 strOldName = Worksheets("Sheet1").Range("A1").Text (HERE IS WHAT I'VE CHANGED)

brunces

brunces,

Personally I can't see the purpose of the code you have posted. I have installed it in the attached file. As far as I can see the code does nothing.

I come back to what you stated earlier:

"I won't type anything in Sheet2. The only cell in which I input data is cell A1 in Sheet1. The word I type in cell A1 in Sheet1 will be the name of Sheet2. That's it."

I really do not understand why you want anything more complicated than the macro I gave you to achieve your specified aim. The macro was:

' in the code pane of Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [A1]) Is Nothing Then
        Sheets(2).Name = Target.Value
    End If
End Sub

However it could also be as below if you want to re-name Sheet2 irrespective of where it is located:

' in the code pane of Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [A1]) Is Nothing Then
        Sheet2.Name = Target.Value
    End If
End Sub

Patrick

carmalize,

>In that snippet of code, the variable "BruncesSheet" will continue to refer to that sheet, regardless of name change or position change.

and you gave this macro:

Sub ForBrunce()
Dim BruncesSheet As Worksheet
Set BruncesSheet = Sheets(2)
ActiveCell.Value = BruncesSheet.Name
End Sub

Sorry to have to point out but the advice you gave was incorrect. When you refer to Sheets(2) you are referring to the second sheet in the tab order. If you want to refer to Sheet2 then you need to use Sheet2 in vba code and if you had done that then your code would have always returned the name of Sheet2.

However brunces stated very clearly that this is what he wanted to do:

"I won't type anything in Sheet2. The only cell in which I input data is cell A1 in Sheet1. The word I type in cell A1 in Sheet1 will be the name of Sheet2. That's it."

Mind you, it appears the requirements changed almost by the minute!

Patrick

patrickab,

"Personally I can't see the purpose of the code you have posted. I have installed it in the attached file. As far as I can see the code does nothing."

I made myself very clear on this post of mine - ID: 25259895. The code does nothing for you. For me, it does a lot. And, as I said before, the code I posted here is just a sample. As a sample, it really does nothing. I agree with you.

"I really do not understand why you want anything more complicated than the macro I gave you to achieve your specified aim."

Your macro works, but it wasn't what I wanted - use a variable inside Sheets brackets. I asked for this all the time. You showed me alternative solutions, but in any moment you answered or showed me what I wanted, maybe because you didn't know the answer.

"Mind you, it appears the requirements changed almost by the minute!"

Since my first post, I've specified what I wanted. Just to remind you, take a look at some of my words:

"It seems it doesn't recognize the variable within Worksheets()."

"I already new about this "Sheets(2).Name", but I really wanted to use the other way. But, if it is the only way, that's OK, I will accept your solution. Anyway, I'd really like to learn how to use a variable inside those brackets."

"The point is that I really wanted to know how to use a variable inside the brackets. Like this:
Sheets(Variable_containing_sheet_name_instead_of_number).Name = Target.Value
Maybe it is not possible, I don't know. I mean, this is what I want to know. If it's possible to use a variable inside the brackets, how is it done?"

"...I wanted to know whether it was possible or not. And, if so, I wanted to LEARN how."

As you can see, I always asked for the same thing, but it seemed you didn't know the answer. That's why you kept on giving me alternative solutions. If you knew the answer, it would very simple, I mean, you just had to say, "brunces, it is impossible to use variables inside those brackets!" or "brunces, it is possible to use variables inside thos brackets, but I just don't know how!". That's it. But you wouldn't throw in the towel, would you?

I repeat: you DID give solutions, but it was NOT what I wanted. You didn't answer my original question. You didn't teach me what I wanted to learn. If I insisted so much, it was because I needed it, not because I'm a contrarian.

Anyway, I've already solved the problem. That's what matters.

brunces

brunces,

>Your macro works, but it wasn't what I wanted - use a variable inside Sheets brackets. I asked for this all the time. You showed me alternative solutions, but in any moment you answered or showed me what I wanted, maybe because you didn't know the answer.

No, it's because your question was vague and then changed as time went by. Your later specification was at total variance to what you apparently actually wanted. It is small wonder that you didn't get the help that you wanted. If you want specific answers then you should put it in the original question. That is worth bearing in mind for your future questions.

Just so you know the usage of Sheets("Sheet1") as a type of syntax is totally standard and is used by almost everyone who writes VBA. Likewise to replace the "Sheet1" with a string variable is standard practice - but within the phrasing of your original question, irrelevant.

I have answered thousands of questions here and I am usually able to help those that do their best to help me understand their requirements. And that includes people whose first language is not English.

Patrick

patrickab, have you paid attention the title of my topic?

"How to use a variable as a sheet name"

As I told you before, that was what I wanted since the beginning. Since the title. But you insisted on giving me "Sheets(2)" which, by the way, locks and limits the code to a single sheet.

"Personally I can't see the purpose of the code you have posted. I have installed it in the attached file. As far as I can see the code does nothing."

There's no purpose to be seen. It's just a sample, an example of what I need. If I get the sample to work, it will also work on my original project, that's obvious. What did you expect me to do? Was I supposed to upload my entire project just for you "to see" the purpose and "to understand" it? Come on! You're an "expert", aren't you? You know there's no need of it. All you need is a functional example and that's what I provided you.

"I really do not understand why you want anything more complicated than the macro I gave you to achieve your specified aim."

So what? There's no need of understanding. Just answer my question - yes or no - based on what you got. Simple like that.

Anyway, all the time, my code was right. I mean, the thing I wanted to know was working perfectly, but I couldn't realize that because of another error in another part of the code. This other error made me think the problem was the variable, but it wasn't. When I solved the other problem, everything worked fine. As an expert, that's what you could have done this.

Well, I know when to stop talking after I've already made my point, so I'm done. No hard feelings.

Regards,

brunces

brunces,

Good fortune in your project.

Patrick