Add a new worksheet and name it from a user form's text box value.

Question

I want to add a new worksheet and name it from a user form's text box value. Please help. Thank you.

Dim CampDate As String

CampDate = RegisterCamp.CampDate

If CampDate <> "" Then
    MsgBox (CampDate)
    Sheets.Add.Name = CampDate
End If

Please look into this thread: http://stackoverflow.com/a/20697790/7995847 — FishermansFriend, May 12 '17 at 10:03
Possible duplicate of [How to Add a Named Sheet at the end of all excel sheets](http://stackoverflow.com/questions/20697706/how-to-add-a-named-sheet-at-the-end-of-all-excel-sheets) — Pᴇʜ, May 12 '17 at 10:10
CampDate is the value of a textbox from a user form which is RegisterCamp in my case. — Hydes Yase, May 12 '17 at 10:59
Yes but what is its actual value when it errors? There are restrictions on sheet names. — SJR, May 12 '17 at 11:00
This is the error I get: Run-time error '1004 You typed an invalid name for a sheet or chart. Make sure that: ? The name that you type does not exceed 31 characters. ? The name does not contain any of the following characters: : ￥ / ? ＊ [ or ] ? You did not leave the name blank. — Hydes Yase, May 12 '17 at 11:10
Yes really. Now I understand. I was so stupid. I looked at the error as to be an error in my code. — Hydes Yase, May 12 '17 at 11:47

Zsmaster · Answer 1 · 2017-05-12T10:28:11.583

2

Add the .Text:

Private Sub CommandButton1_Click()
    Dim CampDate As String

    CampDate = RegisterCamp.CampDate.Text

    If CampDate <> "" Then
        MsgBox (CampDate)
        Sheets.Add.Name = CampDate
    End If
End Sub

edited May 12 '17 at 10:28

answered May 12 '17 at 10:03

Zsmaster

1,549
4
19
28

Still won't do mister. – Hydes Yase May 12 '17 at 10:18
Do you get any error messages? Because it works fine for me. – Zsmaster May 12 '17 at 10:27
Yes. Run Time Error 1004 – Hydes Yase May 12 '17 at 10:31

score -3 · Accepted Answer · answered May 12 '17 at 10:26

-3

This Code works fine:

Option Explicit

Private Sub CommandButton1_Click()

Dim strCampDate As String
Dim ws As Worksheet


strCampDate = UserForm1.TextBox1.Value

If strCampDate <> "" Then
  Set ws = ThisWorkbook.Sheets.Add
  ws.Name = strCampDate
End If

End Sub

answered May 12 '17 at 10:26

FishermansFriend

52
9

What does Option Explicit do? – Hydes Yase May 12 '17 at 10:31
'Option Explicit' forces you to declare all variables in your code. You can set in the options of the VBE. It's highly recommended, that yo use this option to avoid some pitfalls. – FishermansFriend May 12 '17 at 10:36
I receive the runtime error '1004' when a sheet with the new name already exists in the document. So you have to check this first before adding new sheets. – FishermansFriend May 12 '17 at 10:42
Are all run-time errors the same? I get this: Run-time error '1004 You typed an invalid name for a sheet or chart. Make sure that: ? The name that you type does not exceed 31 characters. ? The name does not contain any of the following characters: : ￥ / ? ＊ [ or ] ? You did not leave the name blank. – Hydes Yase May 12 '17 at 10:54
If you'd mentioned that in your initial post this would probably have been solved 55 minutes ago! – SJR May 12 '17 at 11:02
Tell us what you type in the textbox. – FishermansFriend May 12 '17 at 11:10
Okay. So now I understand. – Hydes Yase May 12 '17 at 11:12

Add a new worksheet and name it from a user form's text box value.

2 Answers2