renaming worksheet with userform

[buzz71023]

i have a user form that inserts job information in a worksheet (Date, Product Code, Lot Number, and Job Size) It all gets added correctly to my worksheet but i want to be able to rename my worksheet (with product code) with my userform instead of having to type it in again on the tab itself.

textbox for product code is txtProductCode

and the Code for the whole userform add is as follows:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("USE FOR NEW SHEET (2)")
'check for a part number
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(2, 4).Value = Me.txtDate.Value
ws.Cells(4, 4).Value = Me.txtProductCode.Value
ws.Cells(6, 4).Value = Me.txtLotNumber.Value
ws.Cells(4, 6).Value = Me.txtJobSize.Value
'clear the data
Me.txtDate.Value = ""
Me.txtProductCode.Value = ""
Me.txtLotNumber.Value = ""
Me.txtJobSize.Value = ""
Me.txtDate.SetFocus

End Sub

 

[Sektor]

Use programmatic name. You'll find it in Project Explorer. The name before parenthesis is programmatic name (in the Properties window it's (Name) property at the top - not Name property).

Say, you created new workbook. The programmatic name for first sheet is Sheet1. You can use it in your code and rename sheet name thru Excel.

Sub ProgrammaticNameTest()

    MsgBox Sheet1.Range("A1")
    Sheet1.Name = "My new name"
    MsgBox Sheet1.Range("A1")
    
End Sub

 

[buzz71023]

that might work but everytime i make a copy of my orginal it's not going to be sheet1. It's going to be sheet4 this time, next time it will be 5, then 6 and so on.

 

[Sektor]

...but i want to be able to rename my worksheet...

You talk about renaming, not copying.

 

[GTO]

I may not be understanding the question, but presuming product code is made up of legal characters for naming a sheet, and that it is unique:

ws.Name = Me.txtProductCode.Value

 

[buzz71023]

my fault, i have a macro that creates a new worksheet for each job (product code). when that button is pressed to exe the macro a userform pops up to label that job's worksheet at the top of the page. so what i want to be able to do is when I press the "add" button on the userform to label the top of the worksheet, I want it to rename the worksheet tab.


sorry i forgot some of the info.. i appreciate the help thus far, i have only been using VBA/macro for about a week and only even heard about it, about a 3 weeks ago. i'm trying to teach myself through videos and forums.

if you feel you need more info just let me know. thanks

 

[buzz71023]

product code is just numbers 5 to 7 characters

 

[Sektor]

Do I correctly understand that each time you press "add" button, you want to create new sheet and transfer data onto it?

 

[buzz71023]

No, there are really two add button. The first one is on a menu sheet that adds a new worksheet to my workbook each time it is pressed (the new worksheet will be named "USE THIS FOR NEW (2)" before it is renamed),i have also coded it where my userform pop ups at that same point. The userform has a seperate add button (all it does is adds my the date, product code, lot number, and job size when yo hit add on the userform). What i would like is when i hit add on the userform to rename the worksheet.

My actual steps: If I click "Add New Worksheet" it will make a new worksheet call USE THIS FOR NEW (2) and the userform will pop up I would enter in 6/1/11, 102301, 881111, and 50,000 lbs in their respective fields and hit "add" <--- this is where i would want to be able to rename the worksheet

My Code to my userform add button is as follows:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("USE FOR NEW SHEET (2)")
'check for a part number
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(2, 4).Value = Me.txtDate.Value
ws.Cells(4, 4).Value = Me.txtProductCode.Value
ws.Cells(6, 4).Value = Me.txtLotNumber.Value
ws.Cells(4, 6).Value = Me.txtJobSize.Value
'clear the data
Me.txtDate.Value = ""
Me.txtProductCode.Value = ""
Me.txtLotNumber.Value = ""
Me.txtJobSize.Value = ""
Me.txtDate.SetFocus
End Sub