change tabname from cell ref

[Trevor3007]

good evening,

I use the following code:-

Sub MyRename()
Sheets(2).Name = Sheets(2).Range("d1") & "-Codes"
End Sub

which does work, but I want it run as soon as D1 is changed without pressing f9 or running macro.

could some cool coder please sort?

KR
Trevor3007

 

[C Moore]

It's be a worksheet event that triggers when D1 changes. Google worksheet event.

 

[Trevor3007]

Good Morning C Moore,

Thanks for your help. I 'Googled' as you advised & yes 'hints/tips etc' are there but I don't have a scoobie as what to do .

I therefore politely request if someone out there can assist.

KR
Trevor3007

 

[Fluff]

How is D1 being changed?

 

[Trevor3007]

Hi there Fluff,

Data will be inputted manually

Many thanks in advance.

KR
Trevor3007

 

[Fluff]

try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(False, False) = "D1" Then
      ActiveSheet.name = Target.Value & "-Codes"
   End If
End Sub

 

[Trevor3007]

F A N T A S T I C!!!!


thank you & have a great day !!!
KR
Trevor3007

 

[Fluff]

Glad to help & thanks for the feedback

 

[Trevor3007]

more than welcome.

my issue with VB is that am fine with 'record macro' and can tweak a wee bit, but mostly I depend on Mr Excel & the 'Wizards Of VB' (IE YOU) to sort.

often I have the idea, but lack the 'how to'.

For example.

I am trying to find some VB that will email a certain worksheet contained in a workbook. but no look so far

Anyhoo..have a great day
KR
Trevor3007

 

[Fluff]

Have a look at Ron's site https://www.rondebruin.nl/