Novice Challenge 5: You Name It

Office Programs Required	Microsoft Excel 2007
Goldfish Awarded	5
Deadline for Submission	Monday, April 27, 2009 (8:00 AM Pacific Daylight Time)
Files Required	NoviceChallenge05.xlsm in the OfficePalooza Challenge Pack.

In this challenge we’ll be working with VBA Forms. But don’t worry, you don’t actually have to create the form – that’s been done for you. All you have to do is make it work.

The file from the OfficePalooza Challenge Pack required to complete this challenge contains a macro named NameWorksheets. When you run this macro, a form will appear:

You can enter data into the fields shown in the form, but when you click the OK button, nothing will happen. That’s where the challenge comes in – you need to make something happen.

If you look at the bottom of the workbook you’ll see that there are three worksheets: Sheet1, Sheet2, and Sheet3.

The form allows the user to change the names of each of these worksheets. For example, suppose the user types values into the form like this:

 

Suppose he or she then clicks the OK button. After the button is clocked the worksheet tabs should look like this:

 

In other words, your challenge is to add code to that will change the worksheet names based on the values entered on the form for Sheet 1, Sheet 2, and Sheet 3.

When you open the Visual Basic editor (which you can do by pressing Alt+F11), there should already be a window open with the subroutine CommandButton1_Click showing. This subroutine will run automatically when the OK button on the form is clicked. Insert your code into this subroutine. (If you don’t see this subroutine, click on frmWorksheets under Forms in the Project pane on the left under VBAProject (NoviceChallenge05) and click the View Code button.) Do not create any other subroutines – all your code should go in here.

Important: Do not add code to save the spreadsheet.

Submitting Your Entry

The OfficePalooza sweepstakes is over, but you’re welcome to try the challenges and learn on your own. Good luck!