Need Userform to create new worksheet

[jpar1983]

Hi all,
I'm working with Excel 2007 and Windows XP Professional and am needing a userform that will create a new worksheet, but only if a new worksheet is needed. I am adding new accounts and we want the data sorted by month on different worksheets. What I would like is a Date textbox that the Userform will then read the date and determine if a worksheet exists that would contain that date, and if not, then to create one. Basically if the text box says July 15, 2013, then it would either find the worksheet for July 2013 or create a new worksheet titled July 2013 which could then be accessed on future uses of said userform. I have no idea even where to begin with this!

 

[Jeffrey Lopez]

First, you don't really need a userform, you just need an input box, something that allows people to enter new sheet names... so here is a quick macro that will do just that, check to see if the worksheets exists, if not, it will create it for them.

Hope this helps:

PLEASE BACK UP YOUR EXCEL WORKBOOK BEFORE RUNNING THIS MACRO

Sub AddingSheet()


Dim strNewName As String
Dim ws As Worksheet
Dim boolFound As Boolean


strNewName = InputBox("Please enter a valid Worksheet Name:", "New Worksheet Name")
For Each ws In Worksheets
If ws.Name Like strNewName Then boolFound = True: Exit For
Next


If boolFound = True Then
MsgBox ("Name Already Exisits, Please enter a new name")
Sheets(strNewName).Select


Else
Sheets.Add.Name = strNewName
End If


End Sub

PLEASE BACK UP YOUR EXCEL WORKBOOK BEFORE RUNNING THIS MACRO

 

[jpar1983]

That sounds very useful in and of itself, but it is not really what I am looking for. I was thinking of something that would be more automatic, and something that could take user-entered information and reinterpret it to find or create the correct sheet. In other words if the user enters 7/15/13 or 7-15-2013, I want the form to search for or create worksheet "July 2013." Actually that input box code could come in handy there if the code could interpret multiple potential date formats to the same result of "July 2013." By the way, as an input box your code worked pretty well, but if you attempt to create a sheet that already exists it just closes the box, whereas if you still need to create a new sheet it might be better if it stayed open.

Actually after sleeping on it I think I am making this way more complicated that it needs to be. I had previously thought we were going to sort these by a different category, thus the userform would have been valuable since we could need to access several sheets each time we enter records, but since we decided to sort the records by date instead, the userform is superfluous. I will probably still mess around with it in my spare time just for my own benefit, but for now I think I should just let it go as a plain spreadsheet. We can easily manually create a new worksheet if it is needed, and I have a feeling my coworkers would prefer that to a userform anyway.

Just for kicks and giggles I may still try to use your input box, I just have to figure out a way for the code to interpret the date. Fun!

 

[Jeffrey Lopez]

but if you attempt to create a sheet that already exists it just closes the box, whereas if you still need to create a new sheet it might be better if it stayed open.

I have actually select the active sheet, we can do either... I did make a few few modifications to the code to better suit you...

Let me know if this works, and as always back up your excel file before running this:

Sub AddingSheet()


Dim strNewName As String
Dim strNewNameF As String
Dim ws As Worksheet
Dim boolFound As Boolean


strNewName = InputBox("Please enter a valid Worksheet Name:", "New Worksheet Name", Format(Date, "mm/dd/yyyy"))
strNewNameF = Format(strNewName, "mmmm yyyy")


For Each ws In Worksheets
If ws.Name Like strNewNameF Then boolFound = True: Exit For
Next


If boolFound = True Then
MsgBox ("Name Already Exisits, Please enter a new name")
Sheets(strNewNameF).Select


Else
Sheets.Add.Name = strNewNameF
End If


End Sub

 

[jpar1983]

I'm definitely going to save the inputbox for future projects, but for now I'm using a textbox within another userform to create the worksheet. Now my parameters have gotten a bit more defined - same date format, sheet creation, etc. but now it needs to be inserted always as the second sheet in the workbook (the first will be reserved for reporting, whereas the remaining sheets in the workbook will be a basic log in reverse date order. So I guess I just need to take the info from the textbox to create the new worksheet. I will mess around with it and post if I can make it work!

 

[jpar1983]

Got it! I basically took the code above and changed out the inputbox for the textbox that I needed to draw my data from, as well as the formatting. Here is the final form:

Private Sub Commandbutton1_Click()
'create new worksheet with month and year name
Worksheets(1).Select
Dim strNewName As String
Dim ws As Worksheet
Dim boolFound As Boolean
'pull sheet name from Textbox1
strNewName = TextBox1.Value
strNewNameF = Format(strNewName, "mmmm yyyy")
For Each ws In Worksheets
If ws.Name Like strNewNameF Then boolFound = True: Exit For
Next
'If sheet already exists
If boolFound = True Then
MsgBox ("Sheet already exists, please enter a new name or click on Cancel to close")
'If sheet does not already exist, add sheet
Else
Sheets.Add.Name = strNewNameF
'move worksheet to second sheet on list
Worksheets(1).Move Before:=Sheets(3)
'close userform if sheet added
Unload Me
End If
'userform still active if sheet not added
End Sub