Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialog Box - Simple Question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialog Box - Simple Question
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialog Box - Simple Question
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialog Box - Simple Question
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. sharpie00 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Dialog box | New Users to Excel | |||
Simple input/display question | Excel Discussion (Misc queries) | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
dialog box to input data? | Excel Worksheet Functions | |||
Macro to pause for user input in dialog box | Excel Discussion (Misc queries) |