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

 

[dmt32]

Hi,
see if this update to your code does what you want

Private Sub Workbook_Open()
    Dim myValue As Variant


    If MsgBox("Do You Need To Insert Name ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub


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

Do note that your code does not allow for illegal tab name length or character checking or if user presses cancel button.
Also, once you rename the sheet code will error (error 9) next time workbook opened if Sheet("2") no longer exist.

Hope Helpful

Dave

 

[Trevor3007]

Hi,
see if this update to your code does what you want

Private Sub Workbook_Open()
    Dim myValue As Variant


    If MsgBox("Do You Need To Insert Name ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub


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

Do note that your code does not allow for illegal tab name length or character checking or if user presses cancel button.
Also, once you rename the sheet code will error (error 9) next time workbook opened if Sheet("2") no longer exist.

Hope Helpful

Dave

good morning Dave,

thank you for your help & especially on your BH break.

I see the error upon 'renaming' is it possible to sort so the error will not display? Appreciate that an error will appear if

illegal tab name length or character checking or if user presses cancel button.

KR
Trevor3007​

 

[dmt32]

good morning Dave,

thank you for your help & especially on your BH break.

I see the error upon 'renaming' is it possible to sort so the error will not display? Appreciate that an error will appear if

illegal tab name length or character checking or if user presses cancel button.

KR
Trevor3007​

Yes is the answer but need to understand your workflow -

When user opens workbook & renames the tab named "2" what do you want to happen next time workbook is opened?

- Do you want the process to repeat i.e show the message box etc?

or

- Do you want to skip process altogether as tab has already been renamed?

Dave

 

[Trevor3007]

Yes is the answer but need to understand your workflow -

When user opens workbook & renames the tab named "2" what do you want to happen next time workbook is opened?

- Do you want the process to repeat i.e show the message box etc?

or

- Do you want to skip process altogether as tab has already been renamed?

Dave

Hi Dave,

Thanks Dave for getting back to me. In answer to your Q's:-

When user opens workbook & renames the tab named "2" what do you want to happen next time workbook is opened?​

They will have the option to change the name or not on the applicable worktab/sheet

- Do you want the process to repeat i.e show the message box etc?
)
Yes, as the workbook will be used by often . I know this will be annoying to some but hey ho they will have to live with it (unless you have an alternative of course?


or

- Do you want to skip process altogether as tab has already been renamed?​

No this must be requested each time


I would also like to prompt a message box to ask if they want to save the file after rename & if Y let then choose where as opposed to a default location?

Thank you Dave & hope you can sort.

KR
Trevor3007

 

[dmt32]

Hi Dave,

- Do you want the process to repeat i.e show the message box etc?

Yes, as the workbook will be used by often . I know this will be annoying to some but hey ho they will have to live with it (unless you have an alternative of course?


​

Trevor3007

Can you tell me the Sheets Code Name - In the Properties Window you will see under heading "Microsoft Excel Objects" your worksheets & their names like:

Sheet1(Sheet1)

The first (shown in RED) is the Code Name. The Second is your sheets Tab Name - I need the name of applicable sheet shown in RED.

Dave

 

[Trevor3007]

Can you tell me the Sheets Code Name - In the Properties Window you will see under heading "Microsoft Excel Objects" your worksheets & their names like:

Sheet1(Sheet1)

The first (shown in RED) is the Code Name. The Second is your sheets Tab Name - I need the name of applicable sheet shown in RED.

Dave

Hi Dave,

the sheet/tab name is -data (that is hyphen data) the sheet is always what the text is D1 …. hoping this makes sense.

its all a bit DER to me..I am canny on the idea/look of, but el naff on the code needed ;[

All the very best

 

[dmt32]

just to clarify

-data(Sheet1)

is -data showing in the Microsoft Excel Objects window same as above in RED?

or does it look something like this

Sheet1(-data)

I need the sheets Code Name which is shown to the left (RED)

Dave

 

[Trevor3007]

just to clarify

-data(Sheet1)

is -data showing in the Microsoft Excel Objects window same as above in RED?

or does it look something like this

Sheet1(-data)

I need the sheets Code Name which is shown to the left (RED)

Dave

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 :/

 

[dmt32]

right click worksheet tab from menu select "View Code"

This takes you to VBA Editor

In the Properties Window on the LEFT you will see above it the heading "Microsoft Excel Objects" This shows your worksheets & their names like:

Sheet1(Sheet1)

The first part (shown in RED) is the sheets Code Name.

The Second part is your sheets Tab Name

- I need to know the code name of applicable sheet where shown in RED.

Dave