Automatic naming of tabs?

**Excel Query** · 08-25-2006, 06:31 PM

I am building a worksheet for paying vendors for my company. Upon receiving a small batch of bills, I'll open a workbook that has several identical blank forms. For each bill, I'll fill-out one sheet within the workbook using a drop down list to insert the vendor's name/address. Then I'll fill in the the dollar amount of the bill.

Now my question: Once a vendor is chosen, is there a way for the generic worksheet to have the tab automatically filled-in to match the vendor's name. Is this possible? I'm a noob to VBA, so if that is necessary, I'll be struggling to understand.

Thanks for any help you can offer.

**Excelenator** · 08-25-2006, 07:28 PM

Copy this code and paste it into the Sheet object in the visual basic editor. To get there right click on the sheet tab and select view code. ONce there paste this code in. When you change the value of cell A1 the worksheet tab name changes. You can adjust the cell that changes the worksheet name by changing the red text in the code to the cell value you want with the $ included.

Private Sub Worksheet_Change(ByVal t As Range)
    If t.Address = "$A$1" Then
        On Error Resume Next
        Dim NewShtNm As String
        Dim sh As Worksheet
        NewShtNm = Mid(t.Value, 1, 31)
                        
        For Each sh In Worksheets
            If sh.Name = NewShtNm Then NewShtNm = "NEW " & Mid(NewShtNm, 1, 27)
        Next sh
        ActiveSheet.Name = NewShtNm
    End If
End Sub

**Excel Query** · 08-28-2006, 05:26 PM

Thanks, that worked great.

It popped in the word "New" before the name, so I deleted that from the code and it appears to still work fine.

And one other question to anyone who sees this. Where is the best place on this forum to start learning lots more about the visual basic editor?

**Mark K** · 08-29-2006, 02:43 AM

Originally Posted by Excel Query

And one other question to anyone who sees this. Where is the best place on this forum to start learning lots more about the visual basic editor?

The Excel Programming forum would probably the best place to start.

**DCSwearingen** · 08-30-2006, 04:52 PM

I purchased a couple of books several years ago from Microsoft Press.

Both started bery basic and worked through to quite a bit more advanced.

Microsoft Excel97 Step by Step - came with a floppy with practice files to go along with the tutorials in the book.

Microsoft Excel97 Visual Basic Step by Step - Came with a CD with practice files to go along with the tutorials in the book.

I haven't looked recently, but I am sure they have somnething similar for today's products.

Originally Posted by Mark K

The Excel Programming forum would probably the best place to start.

**funkymonkUK** · 08-29-2006, 10:39 AM

Originally Posted by Excel Query

And one other question to anyone who sees this. Where is the best place on this forum to start learning lots more about the visual basic editor?

and of course from me

**Excel Query** · 08-30-2006, 03:25 PM

Excelenator,
The code works great, but stops working if the sheet is saved. What can I change on the code so it continues to work?

BTW, thanks Mark, thanks funky.

**DCSwearingen** · 08-31-2006, 10:32 PM

http://www.microsoft.com/MSPress/books/5163.asp

Originally Posted by Excel Query

Thanks, that worked great.

It popped in the word "New" before the name, so I deleted that from the code and it appears to still work fine.

And one other question to anyone who sees this. Where is the best place on this forum to start learning lots more about the visual basic editor?