Input Dialog Box - Simple Question

[sharpie00]

Good morning,

It's been a while since I've dabbled in VBA and I am in need of help.

I have created an Excel workbook with different sheet tabs. When the
user first opens the workbook, I would like to have a dialog box open
up and ask them the name of their employees (as soon as they enter 1
name and hit OK, the next box pops up). These names will then replace
the Sheet Tab names already created in the workbook.

For example, when I open up the workbook for the first time, it should
prompt me:

"What is the name of your employee?"
I would enter Sam and hit OK.
Then the Sheet Tab already created which is named "Rep1" should be
renamed to Sam.
This would continue until all employees have their own sheets (I have
Rep1 through Rep12 already set up in the workbook allowing up to 12
employees).

Once it's completed, and the workbook is saved, the dialog box would no
longer appear since all the employees are already entered by the
manager.

Thank you in advance for any help you can provide.

 

[Dave Peterson]

You could do this, but it sounds to me like it would be much better just setting
up that workbook manually one time.

If I'm Sam, I could answer Sam, Samuel, Sammy, ... and end up with lots of
different worksheets for the same person.

 

[sharpie00]

Actually, it will be for managers to use for their employees.

In essence, each employee will have the same worksheet within that
workbook. The only difference is that each sheet tab will have a
different name. It will be up to the manager to input the names
correctly.

Thanks.

 

[Dave Peterson]

I still think that it's a job better done manually--no matter who does it.

But...

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Dim myName As String
Dim repWks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) Like ("rep*") Then
'keep going
Set repWks = wks
Exit For
Else
MsgBox "no more sheets to rename"
Exit Sub
End If
Next wks

myName = InputBox(prompt:="enter a name")

If Trim(myName) = "" Then
Exit Sub
End If

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myName)
On Error GoTo 0

If wks Is Nothing Then
On Error Resume Next
repWks.Name = myName
If Err.Number <> 0 Then
MsgBox "Invalid name, rename failed"
Err.Clear
End If
On Error GoTo 0
End If
End Sub

When you're happy, you could get rid of that msgbox about no more sheets to
rename.