Vba code to sheet name equal cell name

[Lacan]

Simple Question:

Have in cell B2 the name of my customer that would like that sheet name return the same equal name in B2.

How to do so?

Thanks for the great advice.

 

[Lacan]

hiker95

The code you wrote works like magic.

Now my last question because it gave me an error:

Want to use the 2 vba codes only in one sheet both in the same sheet. Because paste them in the same sheet gave me an error.

How to write them properly to use them in the same sheet?

Code Vba 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C38")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(38, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

Code Vba 2

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/29/2016, ME982790
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
With ActiveSheet
If Not .Range("B2") = vbEmpty Then
.Name = .Range("B2").Value
End If
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


Great New Years Eve.

Thanks to all guys.

 

[hiker95]

hiker95

The code you wrote works like magic.

Lucan,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[hiker95]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C38")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(38, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

Lucan,

You can only have one Worksheet_Change event in a worksheet.

I am confused by your code.

Range("C38") is not the same cell as Cells(38, 2)?????

What is the target cell?

Please explain in detail what you are trying to accomplish.

 

[Lacan]

hiker95

Very well let me explain,


Im trying to build a workbook file for each of my customers.

In cell B2 have the name of my customer, for example, CUSTOMER1, which want the B2 cell name equal the sheet name.

After that in C38:N38 have a range of cells merged and centered to write what relevant happened when I did that sales meeting.

And for last to answer your question in cell B38 have the today date with format "dd-mmm" to record the date when that sales meeting happened.

The range and the cells are correct however what I did with your help was:

1. Create one Worksheet_Change event in a worksheet with code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C38")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(38, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

2. Create a Macro with code:

Sub Enter()
ActiveSheet.Name = Range("B2").Value
End Sub

With that Macro everytime put one name after that have to run the Macro and for that reason asked if there is any way to do it automatically instead of everytime rename B2 have to do Run Macro Dialog.

3. You told me the follow Vba Code:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/29/2016, ME982790
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
With ActiveSheet
If Not .Range("B2") = vbEmpty Then
.Name = .Range("B2").Value
End If
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

For last hiker95 you told me that its not possible to have more than one Worksheet_Change event in a worksheet so I guess that have to run Run Macro Dialog everytime rename B2 cell.

What do you think?

Thanks pal.

 

[hiker95]

Lacan,

Please answer all the following questions.

Each time you change B2, you want:

1. The worksheet name to be the value of cell B2?

2. And, after that in C38:N38 have a range of cells merged and centered to write what relevant happened when I did that sales meeting?

3. Is range C38:N38 already merged, and, centered?

4a. And, cell B38 have the today date with format "dd-mmm"?

4b. Or, have the merged cells C38:N38 with the today date with format "dd-mmm"?

 

[Lacan]

hiker95

Here it goes:

1. Yes.


2. Yes.


3. Yes.


4a. Yes.


4b. No, B38 has today date with format "dd-mmm".

 

[hiker95]

Lucan,

Here is a new Worksheet_Change event for you to consider.

Sample raw data in the Active Worksheet:

Excel 2007
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Sheet1
3
37
38
39
Sheet1

And, if we change cell B2 to Customer1 we get this after I add some text in the merged, centered, cells:

Excel 2007
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Customer1
3
37
38	01-Jan	write what relevant happened when I did the sames meeting.
39
Customer1

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 01/01/2017, ME982790
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  With ActiveSheet
    If Not .Range("B2") = vbEmpty Then
      .Name = .Range("B2").Value
    End If
    With .Range("B38")
      .Value = Now()
      .NumberFormat = "dd-mmm"
    End With
    With .Range("C38:N38")
      .MergeCells = True
      .HorizontalAlignment = xlCenter
    End With
  End With
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[hiker95]

Lucan,

In my last reply the merged cells in Range("C38:N38") are not displaying correctly in the MrExcel display area.

 

[Lacan]

hicker95

Thank you very much.

If need your help let you know.

Apreciate your job.

 

[hiker95]

Lacan,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.