Code won't work when sheetname is changed

I've been using this code in so many spreadsheets with me since it's a template. But whenever I change the sheetname, the VBA won't work.

1. Can someone please help me how to make this vba work even when sheetname is changed? And..
2. How do I make another VBA button that when clicked would copy the spreadsheet without going through the WRITE CLICK>MOVE OR COPY>CLICK TO COPY procedure using the mouse?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next
    If Not Intersect(Target, Range("b14:B131")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    On Error GoTo 0

End Sub
Sub HideRow()
    Dim lLastRow As Long
    Dim lCounter As Long
    
    ActiveSheet.Unprotect
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Sheets("SF 1").Shapes("Button 1").Visible = False
    
    With Worksheets("SF 1") 'Change to suit
    
    lLastRow = .Range("ar65536").End(xlUp).Row
    For lCounter = 14 To lLastRow
        If .Cells(lCounter, "ar").Value = 1 Then
            .Cells(lCounter, "ar").EntireRow.Hidden = True
        'Else: .Cells(lCounter, "ar").EntireRow.Hidden = False
        End If
    Next lCounter
    .Range("A14").Select
    End With
    
    Sheets("SF 1").Shapes("Button 2").Visible = True
     
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    
End Sub

Sub UnHideRow()
    
    ActiveSheet.Unprotect
    Worksheets("SF 1").Rows.Hidden = False
    Sheets("SF 1").Shapes("Button 1").Visible = True
    Sheets("SF 1").Shapes("Button 2").Visible = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Originally Posted by JBeaucaire

In the code above I see many references to Sheets("SF 1").... is that the name of the sheet module that this macro is installed into? If so, you can replace all the references with the word Me. and it will work generically.

How does that work sir? What if I would make a copy of the worksheet, would the codes still work?

Originally Posted by humdingaling

the answer is in the code itself

 With Worksheets("SF 1") 'Change to suit

if you want the code to work on any sheet name just change it to activesheet

Sorry for this very noob question, but can you dictate it for me?

Originally Posted by Norie

Which part of the code doesn't work when the sheet name changes?

Where is the code located?

Well, everytime i right click, move or copy, and copy the sheet, the buttons for this new worksheet won't work the same than the original one.

As you can see, my end goal are:

1. That when i rename the worksheet or copy the worksheet then the vba would still work.
2. That i could also ask from all of you a way to create a COPY WORKSHEET BUTTON aswell instead of doing the old fashioned way.

Originally Posted by Norie

So the code doesn't work at all when you copy the worksheet?

Yes, when I do the right click on sheet name, move or copy sheet, the buttons won't work anymore.

Originally Posted by Norie

Can you upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Here's my worksheet, the SF1 sheet works as it is the original but once I make a copy "SF1 (1)" sheet, error happens.

ECR.xlsm

Ok... Give me a sec...

Originally Posted by Norie

What error?

I thought you said the code didn't work at all?

have you tried going to the SF1 (1) sheet and press the buttons?

okay sorry for the confusion, the scenario is like this,

I have one worksheet where I placed these codes to work. and it does work on this worksheet. But it doesn't work anymore when:

1. I changed the sheetname manually into a new one.
2. When I copy the worksheet using the old fashioned way of move or copy etc, then the codes on this new worksheet won't work anymore (even though I didn't change the original name of the worksheet).

Originally Posted by Norie

The problem is that you have the HideRow and UnhideRow in the wrong place, they belong in a standard module so they are available to all sheets.

You'll also need to alter the code in the subs so they refer to the active sheet.

So, put this code in a standard module (Insert>Module).

Sub HideRow()
Dim lLastRow As Long
Dim lCounter As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    With ActiveSheet
    
        .Unprotect
        .Shapes("Button 1").Visible = False

        lLastRow = .Range("E65536").End(xlUp).Row
        For lCounter = 14 To lLastRow
            If .Cells(lCounter, "E").Value = 1 Then
                .Cells(lCounter, "E").EntireRow.Hidden = True
                'Else: .Cells(lCounter, "E").EntireRow.Hidden = False
            End If
        Next lCounter
        .Range("G12").Select

        .Shapes("Button 2").Visible = True
    End With

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub UnHideRow()

    With ActiveSheet
        .Unprotect
        .Rows.Hidden = False
        .Shapes("Button 1").Visible = True
        .Shapes("Button 2").Visible = False
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With

End Sub

Assign the UnhideRow macro to the button captioned Reset and the HideRow macro the the button captioned Hide.

Now when you copy the sheet the buttons on the new sheet(s) will call the Reset/Unhide macros and the code will be executed on the active sheet, ie the one the button you clicked is on.

at last it worked!!! thanks!!!

now one last thing, how do I use this reply sir Richard posted?

Originally Posted by Richard Buttrey

Hi,

You were given the answer in posts number #2, #3 & #4
Change the code to either ActiveSheet or Me

As for a macro to copy a sheet then

Sub CopySheet
   Sheet1.Copy After:=Sheet1

End Sub

And attach that macro to a button.

where do I actually insert that? and is that code already complete?
I'm really not fluent with VBA. Sorry for all the trouble.

Originally Posted by Norie

Do you want code that will copy the 'SF1' sheet when a button is clicked on that sheet?

YES!!! PRECISELY!!!

Originally Posted by Norie

Do you want the button to be on the new sheet?

I want it on the original sheet which when that button is clicked and a new sheet is created, that very same button is also there on that new sheet, the same way the other buttons from the original sheet are all working perfectly.

guys, the code only works when the SF1 sheet remains to be named as SF1, but when changed, as SF2, or SF3, the Copy Button won't work.

Originally Posted by Norie

Richard's code should work as it uses the codename for 'SF1', Sheet1 and that won't change when you change the name of the sheet.

How come I'm getting an error when I press F5 key on the VBA code page?

It says:

Compile Error: Variable not defined

Originally Posted by Norie

What's highlighted when you get that error?

this one:

Sub CopySheet()

Originally Posted by Norie

What's actually highlighted?

Have you changed anything?

No, I just pasted this code:

Sub CopySheet()
   Sheet1.Copy After:=Sheet1

End Sub

after the End Sub of the UnHideRow() module.

error.png

this is the error I get

error.png

can you see the image? I uploaded it.. but it seems not to show..

Originally Posted by Norie

Sheet1 is highlighted in blue in both images which suggests you don't have a worksheet with the codename Sheet1.

How did the code I posted not work?

Does this work?

Sub CopySheet()
    ActiveSheet.Copy After:=ActiveSheet
End Sub

I'm getting this error:

error.png

all the pop-ups are the name ranges that I placed on the original sheet..

how do I prevent this from happening and instead it automatically does so?

Originally Posted by Richard Buttrey

Hi,

Why don't you just upload the workbook then we're not all skating around in the dark?

Hello Sir!

Here's the file I am working on. Thanks!

ECR.xlsm

Originally Posted by Richard Buttrey

Hi,

See attached.

Your SF1 sheet has the VBA code name Sheet3 so it's hardly surprising that using Sheet1 didn't work.
I also noticed that there were getting on for 200 range names of which 100 were errors. The fact that there were range names on SF1 that's being copied means that an extra instruction Application.DisplayAlerts = False is necessary to prevent pop up warnings.

The revised file is attached.

Thanks a lot!!!!

Though I really didn't understand a thing about what just happened.

Originally Posted by Richard Buttrey

Hi,

See attached.

Your SF1 sheet has the VBA code name Sheet3 so it's hardly surprising that using Sheet1 didn't work.
I also noticed that there were getting on for 200 range names of which 100 were errors. The fact that there were range names on SF1 that's being copied means that an extra instruction Application.DisplayAlerts = False is necessary to prevent pop up warnings.

The revised file is attached.

Hello Sir Richard!!!

I tried to modify the worksheet you created with the copy sheet function along with Hiding Cloumns and rows...
I discovered that the newly generated copy sheet does not work with Boolean on Hiding columns, can you advise me what to do?

OH NO!!!

The COPY SHEET BUTTON you made Sir is included in the printed paper.
I didn't even notice it during Print Preview.. how do we fix that?

Originally Posted by Norie

Hide the button after you've created the new sheet.

I don't even know how to do that since he was using a Shape Art...
The buttons that i used in the hide and unhide rows are not showing in the printlayout even if i put it on the middle of the sheet.

Originally Posted by Norie

Why don't you use your, well I assume you added it, button captioned 'NEW SUBJECT'?

why didn't I think of that... Oh man!!! This is so embarrassing!

would it be possible if I would like to put a password on both the sheet and the vba codes?

Originally Posted by Richard Buttrey

Hi,

For the sheet use stuff like

Sheet3.Protect Password = "mypassword"

For the VBA set password protection with Tools, VBA Project Properties, Password.
Not sure whether this can be done with VBA code itself.

Would that make the sheets' code locked and hidden when I use the hid/unhide button?
Coz when I do it manually on the sheetname, after clicking either of the two buttons, it always asks for the unlocking password.. So bothersome..

i removed the Boolean feature for hiding columns, everything works as is and perfectly even when the new sheet is generated from the copy function you coded. the macros on that new sheet also are working fine. it's when I inserted the Boolean for hiding columns that this error 400 occurs on the new sheet.

Originally Posted by Richard Buttrey

..so are you saying it's not working. If so and as I mentioned before upload the workbook.

Yes sir!


Attachment 404129

and oh by the way, the password is "09193483966"

Were you able to check the file Sir Richard? I'm really having trouble with making this work...