auto rename tab

[Trevor3007]

good day,

i have the the following code:-

[/I]Private Sub Workbook_Open()If MsgBox("Do You Need To Insert Name ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Dim myValue As Variant
myValue = InputBox("Enter Name")
Sheets("2").Range("D1").Value = Application.WorksheetFunction.Proper(myValue)
End Sub




[I]

however , i am struggling to find how to apply the data from D1 so that it renames the applicable worksheet tab ?

can any one solve for me?

KR
Trevor3007

 

[Trevor3007]

hiya,'
thanks Dave

I am sorry I don't have a scoobie as to where

Microsoft Excel Objects window is ?​

the phrase ' I am as much use as a chocolate fireguard' comes to mine :/

hi Dave,

for some ?? reason I could not reply to your latest?

however, thanks for your guidance.

following your lead, it states:-

sheet 3 (Skyfall)

hope this is sufficient?

KR
Trevor3007

 

[dmt32]

so just to be clear

The tab name sheet Skyfall looks like this in VBA editor properties

sheet 3 (Skyfall)

Can you confirm sheet 3 has the space? Or does it really look like this

sheet3(Skyfall)

Also, the Tab Name Skyfall will be renamed by the inputBox code?

 

[Trevor3007]

so just to be clear

The tab name sheet Skyfall looks like this in VBA editor properties

sheet 3 (Skyfall)

Can you confirm sheet 3 has the space? Or does it really look like this

sheet3(Skyfall)

Also, the Tab Name Skyfall will be renamed by the inputBox code?

hi,

sheet 3 was called something else until I used your code. It Then got renamed after running your code. So yes sheet 3 is the one that will get renamed (more often than not)

ATM 'Skyfall' is just a tester & named because of my passion for Ian Fleming/Bond :]

orbis non sufficit

 

[Trevor3007]

hi,

sheet 3 was called something else until I used your code. It Then got renamed after running your code. So yes sheet 3 is the one that will get renamed (more often than not)

ATM 'Skyfall' is just a tester & named because of my passion for Ian Fleming/Bond :]

orbis non sufficit

yes sheet 3 will be renamed via the input box...sorry for not including in my previous

 

[dmt32]

yes sheet 3 will be renamed via the input box...sorry for not including in my previous

I not 100% convinced that you are understanding me - If the code name for your sheet is "sheet 3" that would be an illegal name as it has a space - you also state that it was renamed via the inputbox - The code only changes the Tab Name not the Code Name.

Place following code in a standard module & run it

Sub GetCodeName()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets


    MsgBox "Code Name: " & sh.CodeName & Chr(10) & "Tab Name: " & sh.Name
    
Next sh
End Sub

When you see the Tab Name of applicable sheet shown in MsgBox - above it will be that sheets Code Name. Please let me know what it is.

Dave

 

[Trevor3007]

Good morning Dave,

I thought I'd replied to your latest & having just checked it appears I have not?

I ran your code & it returned:-

code name:sheet 3
tab name: goldfinger

I also altered a previous VBA (highlighted in red) :-

Private Sub Workbook_Open()
Dim myValue As Variant




If MsgBox("Do You Want To Add Site Name ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub




myValue = InputBox("Please Input Site Name")
myValue = Application.WorksheetFunction.Proper(myValue)
With Sheets(2)
.Range("D1").Value = myValue
.Name = myValue
End With
End Sub

This changes the applicable tab, but & to include "-Codes" . So the tab name would (using the current tab name ) Goldfinger-Codes

This worked OK using previous:-

Private Sub Worksheet_Change(ByVal Target As Range)


''''''''''''''''''''''''''''''''''''''''''''


'Forces text to Proper case for the range d1


''''''''''''''''''''''''''''''''''''''''''''


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






On Error Resume Next


If Not Intersect(Target, Range("d1")) Is Nothing Then


Application.EnableEvents = False


Target = StrConv(Target, vbProperCase)


Application.EnableEvents = True


End If


On Error GoTo 0




If Target.CountLarge > 1 Then Exit Sub
If Target.Address(False, False) = "D1" Then
ActiveSheet.Name = Target.Value & "-Codes"
End If






End Sub

Are you able to sort so I have the best of both worlds?

My sincere thanks & appreciation for your help with my issue.

KR
Trevor3007

 

[dmt32]

Hi,
try this update

Private Sub Workbook_Open()
    Dim myValue As Variant
    Dim ws As Worksheet
    
    If MsgBox("Do You Need To Insert Name ?", 36, "Insert Name") = vbNo Then Exit Sub
    
'Sheet3 is sheets code name DO NOT CHANGE
    Set ws = Sheet3
    
    Do
'show inputbox
    myValue = InputBox("Enter New Name", "Enter Tab Name")
'cancel pressed
    If StrPtr(myValue) = 0 Then Exit Sub
'loop until tab name entered
    Loop Until Len(myValue) > 0
    
'change to proper case
    myValue = Application.WorksheetFunction.Proper(myValue)
    
'update sheet
    With ws
'add name to cell
        .Range("D1").Value = myValue
'change tab name
        .Name = CStr(myValue) & "-Codes"
    End With
    
End Sub

Dave

 

[Trevor3007]

Hi,
try this update

Private Sub Workbook_Open()
    Dim myValue As Variant
    Dim ws As Worksheet
    
    If MsgBox("Do You Need To Insert Name ?", 36, "Insert Name") = vbNo Then Exit Sub
    
'Sheet3 is sheets code name DO NOT CHANGE
    Set ws = Sheet3
    
    Do
'show inputbox
    myValue = InputBox("Enter New Name", "Enter Tab Name")
'cancel pressed
    If StrPtr(myValue) = 0 Then Exit Sub
'loop until tab name entered
    Loop Until Len(myValue) > 0
    
'change to proper case
    myValue = Application.WorksheetFunction.Proper(myValue)
    
'update sheet
    With ws
'add name to cell
        .Range("D1").Value = myValue
'change tab name
        .Name = CStr(myValue) & "-Codes"
    End With
    
End Sub

Dave

FANTASTIC.....Absolutely BRILL

wow...you have certainly been a great help.


All the very best...
KR
Trevor