Use New Workbook as Userform Output

[stroobie]

I need help creating the code for a userform that will generate a new workbook into which the userform data will go each time the userform is filled out anew.

Also, is it possible to name the new workbook using data from the userform?

I am teaching myself VBA as I go (so speak slowly...) and possibly biting off more than I can chew, but I've been Googling all day making only a little bit of headway. A bit of help with the code would help save me a ton of time at work. Thanks!

[moonsaga]

Can you provide a sample workbook with the userform. Will the new workbook be created each time the userform's submit button is clicked?

[stroobie]

I actually figured out how to do what I wanted to, only now I'm having a different issue. I'm not sure why it isn't working properly, because earlier in the day I seemed to have no trouble. I thought perhaps as I'd been going through trying this and that that I might have changed something to make it not work, but I had copied my 'working' code into a word doc once I figured it out so I'd have it saved in case I messed anything up, and incidentally it all still matches what's currently in VBA, so I'm not sure what is causing it to not work.

In any case, this is the code I am using, and so far everything is functioning as it should, except for the ChDir - it is saving to my desktop and not the specified folder.


------------------------------------------------


Private Sub submitbutton_Click()


'Checking for input

If Me.custnamebox.Value = "" Then
MsgBox "Please Enter Customer Name", vbExclamation, "Customer Name"
Me.custnamebox.SetFocus
Exit Sub
End If

If Me.projnamebox.Value = "" Then
MsgBox "Please Enter Project Name", vbExclamation, "Project Name"
Me.projnamebox.SetFocus
Exit Sub
End If

If Me.jobnobox.Value = "" Then
MsgBox "Please Enter Job Number", vbExclamation, "Job Number"
Me.jobnobox.SetFocus
Exit Sub
End If

If Me.sysqtybox.Value = "" Then
MsgBox "Please Enter Systems Quantity", vbExclamation, "Systems Quantity"
Me.sysqtybox.SetFocus
Exit Sub
End If


'writing input to cells


Set newbook = Workbooks.Add("C:\Users\kate\Desktop\new summary template.xltm")

ChDir "J:\Dumpster-Veyor\DV Summary"

With newbook
.SaveAs Filename:=Me.jobnobox.Value
Dim ws As Worksheet
Set ws = Worksheets("Summary")



ws.Cells(1, 6).Value = Me.projnamebox.Value
ws.Cells(3, 2).Value = Me.custnamebox.Value
ws.Cells(4, 2).Value = Me.jobnobox.Value
ws.Cells(5, 2).Value = Me.sysqtybox.Value

End With


Unload Me

End Sub

[Norie]

Use the full path and filename in the SaveAs.

PS Can you add code tags?

[stroobie]

sorry about that.

i want to be able to use data from the userform to name each new workbook - how would i combine the path with the variable file name?

[Norie]

All you need to do is add the path here.

Please Login or Register  to view this content.