Using a variable as a sheet name

[2_nisia]

Hello,

I have an excel with few hidden and unhidden tabs. I have written a VBA code for one of these tabs and then I hid the tab.
I will send this excel to someone else, and that someone will change the name of this tab.

How can I make the VBA code to get this variable name and run the code?



Sub Hide_Unhide()

Application.ScreenUpdating = False

Sheets("MyTab").Activate


If Range("E60").Value = 0 Then
'MsgBox "E60 is true"
Rows("26:26").EntireRow.Hidden = True
Else
Rows("26:26").EntireRow.Hidden = False
End If



If Range("E61").Value = 0 Then
'MsgBox "E61 is true - Nybyg, M1, >=5000"
Rows("28:29").EntireRow.Hidden = True
Else
Rows("28:29").EntireRow.Hidden = False
End If



Application.ScreenUpdating = True
End Sub

 

[HaHoBe]

Hi,

instead of using the sheet name (as seen on the tabs) use the codename of sheet (can be found in the VBE in Properties, F4 if not visible).

Sheet "MyTab" has the codename Sheet2 then use

Sheet2.Activate

instead of

Sheets("MyTab").Activate

HTH,
Holger

 

[2_nisia]

Hi,

instead of using the sheet name (as seen on the tabs) use the codename of sheet (can be found in the VBE in Properties, F4 if not visible).

Sheet "MyTab" has the codename Sheet2 then use

Sheet2.Activate

instead of

Sheets("MyTab").Activate

HTH,
Holger

I used this formula to find the sheet codename: =CELL("filename";A1)

And apparently the sheet codename is "MyTab". So I wrote MyTab.Activate and when I pressed the button to run the macro I got the following message:

Object required


Do you have any idea why that might be?

 

[HaHoBe]

Hi,

the formula will always rely on the name displayed on the worksheet tab. You would need to change into the VBE, look for the sheet in the Project Explorer (press CTRL+R if not visible), select that sheet, then look for the Properties (if not visible press F4). (Name) stands for the codename, Name for the sheetname:

HTH,
Holger

 

[2_nisia]

Thank you very much! That worked great!

 

[2_nisia]

Hi,

the formula will always rely on the name displayed on the worksheet tab. You would need to change into the VBE, look for the sheet in the Project Explorer (press CTRL+R if not visible), select that sheet, then look for the Properties (if not visible press F4). (Name) stands for the codename, Name for the sheetname:

View attachment 75682

HTH,
Holger

Hey Holger,

I have an additional question:

I received the excel back from the other person, and they said that there was an error when they tried to run the code.

When someone runs the code, the Ark11(Taks_NKB) is unhidden, duplicated and hidden again. But I realised that both the code and the macro assigned to this new duplicated sheet (Ark19 (Taks_kil ol)), are still linked to the Ark11.



Is there a way to assign and set automatically the Ark, both in the code and in the macro?

Thanks in advance!

 

[HaHoBe]

Hi,

please confirm if the macro you posted first is the one which is relevant and if the button is placed on the sheet which needs to be worked on.

It seems you put the code behind the sheet, to my knowledge you should place the code in a standard module where it would not be coped if the sheet is copied. If the code is to be run on the active sheet you may rely on using ActiveSheet and no activation of the sheet would be necessary.

Ciao,
Holger

 

[HaHoBe]

Hi 2_nisia,

from the opening post understood that there is a hidden sheet which will be made visible and renamed (this is the very same sheet). That is why I came up with the CodeName of the sheet which stays the same no matter what the Sheetname on the tab says.

From your last post I learned thast you will not only make the hidden sheet visible but copy it as well. And both the Sheetname as well as the Codename for the newly copied sheet will be different from the original sheet (you can see this looking at the project explorer in the VBE).

And like in my post above: will the code from the button always be applied to the sheet which is active? Is there any reason to use a button (there is nothing wrong but if you enter the values into the cells by hand you could trigger an event, if the cells hold formulas you might check if a calculation took place to have the process started from ThisWorkbook instead inserting a procedure to the sheet)?

And for a quick try by yourself: open a new workbook, rename the Codename of one sheet to read Work1. Then please add one or two new worksheets as well as copy the renamed sheet one or two times and look at the result in the project explorer. New sheets will be called as they normally would, copies of the renamed worksheet will have Work as the start and be numbered. We might take advantage of that in order to limit the use of any procedure used.

Hopefully I could be a bit clearer on what information I would need in order to come up with any kind of solution. Up to now it's obvious for me that using the CodeName will not solve this problem.

Holger

 

[2_nisia]

Hi Holger,

Thanks a lot for the reply, and I am sorry my questions are not clear (still new to this group and to excel). I appreciate a lot the help!

I guess it would solve the issue if the macro was placed in a sheet that doesnt get changed. And I will try to think if I can actually implement such a solution and if it would make sense for the project.
The sheet with the code and macro, is asking from the user to make 3 choices (type of house, size of house etc) and then the user needs to press the button, so the code can run and will hide the rows that are not relevant for him.
So now that I am thinking it, even if I place the macro in a different sheet, it will still need to refer to the new sheet the user created (the whole unhide-copy-hide is done so there is a backup sheet that never gets edited, in the case that the user makes a mistake and wants to delete the sheet he created and do the whole process from the start).

I dont know much from VBA code, but might not be easy to write a code that assigns the sheet and macro to an autogenerated codename :/
Perhaps I should let the user use the original version, and make a copy and hid that instead. But that again would give issues when the user will try to do the process for a second time (the code of the hidden copy will not be relevant).

Thanks in advance and hope we can figure something!

 

[HaHoBe]

Hi 2_nisia,

...but might not be easy to write a code that assigns the sheet and macro to an autogenerated codename

Any user would need to allow changes to the VBA-Project so I don't think that would be the way to go.

What I have in mind by now is staying with your original plan of having one sheet as a "backup" which holds the button as well. As I tend to have one procedure (and not one behind each sheet that will be created) I would prefer to have the code in a standard module (just one place where any change needs to be done and not many). The button will be assigned to that macro. The CodeName of the hidden sheet should be changed to something like BackUp1, WorkCopy1 or Original1 (just a different name which is used by the system like Ark)

And instead of referring to a sheet name or a Codename the code will check if the codename of the ActiveSheet starts with BackUp, WorkCopy or Original. If not no further code is executed. And as the procedure would be visible in the Macro List it may be started but would only work if it is started on one of the sheets where it should run.

Ciao,
Holger