Solution - Novice Challenge 5: You Name It

We’ll start out by admitting something to you: we messed up. That probably isn’t a big surprise to anyone. What is a surprise (and a nice one at that) is that we messed up in a way that made things harder on us, but easier on you. (Okay, that really shouldn’t be a surprise either: we have a bad habit of making things harder on ourselves.)

Oh, you want to know what we did wrong? Well, in this challenge we presented you with a pre-made form, all ready to fill out. All you had to do was add some code so that, when the OK button was clicked, the values entered into the text boxes would be used to rename the three worksheets in the workbook. Pretty simple, huh? Well, that’s where we goofed. It wasn’t supposed to be that simple. We forgot to mention that we also wanted you to close the form. And since we didn’t mention it, you didn’t have to do that to receive the goldfish for this challenge.

Is this your lucky day or what?

To those of you who added that extra bit of code just because you wanted to – thank you, that made testing go just a tiny bit quicker. And since we intended that code to be part of the solution, we’ll include it in the answer we came up with:

Private Sub CommandButton1_Click()

'If a value was entered for each worksheet, change the value of that sheet.

If txtSheet1.Value <> "" Then

    ThisWorkbook.Worksheets(1).Name = txtSheet1.Value

End If

If txtSheet2.Value <> "" Then

    ThisWorkbook.Worksheets(2).Name = txtSheet2.Value

End If

If txtSheet3.Value <> "" Then

    ThisWorkbook.Worksheets(3).Name = txtSheet3.Value

End If

'Unload frmWorksheets

frmWorksheets.Hide

End Sub

As you can see, we put all the code for this challenge in the CommandButton1_Click subroutine. This subroutine runs every time the OK button on our form is clicked. (That’s because, behind the scenes, our OK button is named CommandButton1. Could we have given it a better name? Yes, and in the real world you’d probably want to, but we got lazy and went with the default name that was assigned when we created the button.)

The first thing we do in our subroutine is check to see whether a value was entered in the first text box. That’s what we do here:

If txtSheet1.Value <> "" Then

If the value in the text box txtSheet1 isn’t an empty string – in other words, if a value has been entered – we run this line of code:

ThisWorkbook.Worksheets(1).Name = txtSheet1.Value

All we’re doing here is assigning the value that was entered in the first text box to the Name property of the first worksheet (Worksheet(1)) in the workbook.

That’s really all there is to it. We just need to do this two more times, using the values in txtSheet2 and txtSheet3 to change the names of worksheets 2 and 3, respecitively.

The last thing we did, and which some of you did also, was to close the form. There are a couple of different ways to do this. We went with simply hiding the form:

frmWorksheets.Hide

You could also have used the Unload method to unload the form, in this case represented by Me:

Unload Me

In Visual Basic, the Me object represents the current object, in this case the form.

And that’s it. Like we said, we made this one pretty easy. We’ll try not to make that mistake again. Although we’re sure we’ll manage to do something else soon that will make our lives more difficult.