Another question

[Tark221]

A button on a user form which when clicked will create a new work sheet.
This possible?

Thanks

 

[Tark221]

ooops apologies forgot to mention excel

 

[Tark221]

Thanks for the quick reply

right I have that working using what you mentioned above, thank you btw

Private Sub CommandButton1_Click()
ActiveWorkbook.Sheets.Add
End Sub

Now I have a text box on that form is there a way to write say "newsheet" and when i click the button it names the sheet whats in the textbox at the moment when im creating the sheets there just generating default names

thanks

 

[Tark221]

This is my code now - thanks for the quick reply

Private Sub CommandButton1_Click()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets.Add

If Len(UserFormName.CommandButton1 & vbNullString) > 0 Then
ws.Name = UserFormName.CommandButton1
End If
End Sub

It says object required but still adds a sheet but doesnt name it just names it default name such as sheet6 - any ideas?

 

[Tark221]

Ah your star, works great, do u think there is a way when you create a new sheet it takes it from a template?

 

[Tark221]

so when looking at the code i currently have

Private Sub CommandButton4_Click()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets.Add

If Len(AddStaff.TextBox1 & vbNullString) > 0 Then
ws.Name = AddStaff.TextBox1
End If
End Sub


Is it this im looking at Set ws = ActiveWorkbook.Sheets.Add

Should it be Set ws = TemplateName.Sheets.copy

 

[Tark221]

ooo good tip I will work at this for abit thanks boblarson

 

[Tark221]

Private Sub CommandButton4_Click()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Template").Copy

If Len(AddStaff.TextBox1 & vbNullString) > 0 Then
ws.Name = AddStaff.TextBox1
End If
End Sub

Ive been working with this. I have a worksheet called Template which is the one i want to copy everytime i create a new worksheet, not having much luck with this. Keep getting object required bah !

 

[Tark221]

Thank you, dam I was no where near !

 

[Tark221]

Though I've managed to get the code to also enter the data from the text boxes into the spreadsheet after its made so yay!

Private Sub CommandButton4_Click()
Dim ws As Worksheet


ActiveWorkbook.Sheets("Template").Copy After:=Sheets(Worksheets.Count)
Set ws = Sheets(Sheets.Count)
If Len(AddStaff.TextBox1 & vbNullString) > 0 Then
ws.Name = AddStaff.TextBox1

Dim iRow As Long
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Enter details"
Exit Sub

End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox4.Value

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""

Unload Me
End If
End Sub

 

[Tark221]

When submitting the data into the newly created spreadsheet for some reason it's entering it into row 14

i thought this code made it so it entered it in the next free row

Dim iRow As Long
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

End If