Excel:create new worksheet from userform & enter text box entry

[david0087]

Hi
I have been searching through post but i didnt find any.
I would like to be able to add txt box entry to new sheet . I will appreciate if someone help me .

Private Sub CommandButton1_Click()
 
 Dim ActiveSheetNumber As String
 
    ActiveSheetNumber = SquadronMissionNumber
    NewSheetName = "Mission " + ActiveSheetNumber
 
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = NewSheetName
 
End Sub
 
Private Sub TextBox1_Change()
 Sheets.Add After.Range("a2") = TextBox1.Value
End Sub

[Stewart Ross]

It is not clear what you are wanting to do from what little you tell us. If it is to place the value in textbox1 into cell A2 of the new activesheet it is incorrect to use the Add method of the Worksheet object.

In line 14 above refer instead to the activesheet itself:

Activesheet.Range("A2") = Textbox1

-Stewart

[david0087]

Thank you so much for reply.what I am trying to do
is when I click on command button it saves the textbox value in a new sheet and name the sheet as day1 day2 day 3 so on .since it not correct if you let me know if it is possivle to do anyother way .thank you again.

[Stewart Ross]

Hi. I don't know enough about your application to be able to give a detailed response to you on creating separate sheets labelled Day 1, Day 2 and so on. This I must leave to you.

I have listed below a variation on the sub you already had in your post to give some ideas - but this is likely to start at Day 4 or similar (as usually there is more than one worksheet already created in a new workbook, so the worksheet count is greater than one). The value from the textbox is copied as the last step (line 6).

I notice that you cannot be using the compiler command Option Explicit, which you should have at the top of your code module. It will guard you against undeclared variables such as NewSheetName and SquadronMissionNumber that are included in your sample code.

Private Sub CommandButton1_Click() 
  Dim NewSheetName as String
  NewSheetName = "Day Sheet " & Worksheets.Count
  Sheets.Add After:=Worksheets(Worksheets.Count) 
  ActiveSheet.Name = NewSheetName 
  Activesheet.Range("A2") = Textbox1 
End Sub

-Stewart

[david0087]

So impressive, it works just as I wanted. Thank you so much.Thank you again.