Automation Macro for updating formula when changing cell value

[Guest]

I have the following macro:
Sub Rename_Sheet()

ActiveSheet.Name = ActiveSheet.Range("M2")
End Sub
This macro will change the name of the sheet into what has been filled in
the cell reference.
Does anyone know how this can be done / run automatic when the Cell is
changed / filled in?

Thanks

 

[Guest]

Hi,

Try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$M$2" Then
If Target.Value <> "" Then
ActiveSheet.Name = Target.Value
End If
End If
End Sub


Right click the sheet tab, view code and paste this in

Mike

 

[Guest]

Hi Mike,

Maybe you can help me with 2 other issues?

1> I have an protection build in through VB, because of the grouping option:
Private Sub Workbook_Open()

With Worksheets("Myself")
.EnableOutlining = True
.Protect Password:="DAF", Contents:=True, UserInterfaceOnly:=True
End With
End Sub

This protection is looking at the sheet "Myself" / relating to the reference
in my earlier issue. What would be the possibility to have this macro working
for all Sheets without referring to a name (because the name can change)?

2> The sheet have to be filled in per individu (some sort of appraisal) if i
want to have the outcome in a sort of summery (data base) for all the people
i can use formulas reffering to the sheets, but sheets will be added,
removed and changed, do you know a macro who automatically will update the
data base / summery when a sheet is filled in?

Thanks