UserForm General Structure

[SBF12345]

Greetings,

I am somewhat new to userform objects and how best to structure elements within the userform etc to allow for smooth operations.

I have many textboxes, listboxes, a few buttons and whole lot of variables.

Let me describe the structure I am trying to build...

First, I have a workbook saved under the name "Start Client"

On Sheet1 of this I have one button.

The purpose of this button is to open a new workbook to hold information pertaining to a new client

This new workbook then needs to include a userform (which I would like to stay open when the workbook is open) as the basis of inputting information to be saved to the workbook. If possible I would like to have the userform be shown within a worksheet when the specific worksheet is open.

Additionally, the userform includes buttons which call other subroutines to perform actions like interacting with another workbook, saving data from the userform to the new workbook, and declaring variables.

I think, but am not certain, that many of the variables I attempted to assign to textboxes, are not reaching the subroutines outside of the subroutines used in creating those variables.

Any comments appreciated,

SBF12345

 

[My Aswer Is This]

You said:

This new workbook then needs to include a userform ​

Well if this is a brand new Workbook it would have no UserForm.

To write some type script which would build you a new UserForm every time you open a New Workbook would be a very complicated script. Putting in all your controls and scripts into those controls.

Now you could build a Userform and put it in your "Personal" folder and then open it using a KeyBoard shortcut after opening your new workbook.

 

[SBF12345]

The code below creates a new workbook, names some worksheets and then shows a userform. The scripts below the first are also included in the workbook "Client Start" and include a bunch of variables. I don't think the variables are being recognized as the code sits right now because the "saveas filename:=" with the variables used to construct the name is giving me an error at runtime.

The bottom line at this point is that the variables Dimensioned (Dim) in other areas of the same UserForm Code are not being recognized.

Public Sub CommandButton10_Click()

Dim wb As Workbook


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet


Set wb = Workbooks.Add


wb.Worksheets.Add.Name = "Customer Information"
wb.Worksheets.Add.Name = "Payment Received"
wb.Worksheets.Add.Name = "Ordering"
wb.Worksheets.Add.Name = "Customer_Copy_Invoice"
wb.Worksheets.Add.Name = "Information Forward"
wb.Worksheets.Add.Name = "UserForm"


wb.Worksheets("Sheet1").Delete
wb.Worksheets("Sheet2").Delete
wb.Worksheets("Sheet3").Delete


wb.Worksheets("UserForm").Activate


AAA.Show


End Sub


Public AAA As UserForm

The first sub below is called and executed with another button

Public Sub SaveCustomerInformation()

'Save Customer Information to Customer Workbook


ActiveWorkbook.Worksheets("Customer Information").Select


Range("A1").Value = "Date Opened:"
Range("A2").Value = "First Name:"
Range("A3").Value = "MI:"
Range("A4").Value = "Last Name:"
Range("A5").Value = "Prefix:"
Range("A6").Value = "Email:"
Range("A7").Value = "Phone"
Range("A8").Value = "Fax:"
Range("A9").Value = "DOB:"
Range("A10").Value = "Billing Information:"
Range("A11").Value = "Bill to Address:"
Range("A12").Value = "Bill to City:"
Range("A13").Value = "Bill to State:"
Range("A14").Value = "Bill to Zip:"
Range("A15").Value = "Shipping Information:"
Range("A16").Value = "Ship to Address:"
Range("A17").Value = "Ship to City:"
Range("A18").Value = "Ship to State:"
Range("A19").Value = "Ship to Zip:"
Range("A20").Value = "Password:"
Range("A21").Value = "Notes:"


Range("B1").Value = a
Range("B2").Value = b
Range("B3").Value = c
Range("B4").Value = d
Range("B5").Value = e
Range("B6").Value = f
Range("B7").Value = g
Range("B8").Value = h
Range("B9").Value = i
Range("B11").Value = j
Range("B12").Value = k
Range("B13").Value = l
Range("B14").Value = m
Range("B16").Value = n
Range("B17").Value = o
Range("B18").Value = p
Range("B19").Value = q
Range("B20").Value = r
Range("B21").Value = s


 ActiveWorkbook.SaveAs Filename:=d & b & c & a & ".xls", FileFormat:=xlWorkbookNormal


End Sub
Public a As Variant
Public b As Variant
Public c As Variant
Public d As Variant
Public e As Variant
Public f As Variant
Public g As Variant
Public h As Variant
Public i As Variant
Public j As Variant
Public k As Variant
Public l As Variant
Public m As Variant
Public n As Variant
Public o As Variant
Public p As Variant
Public q As Variant
Public r As Variant
Public s As Variant


___________________________________


Public Sub TextBox1_Change()


a = TextBox1.Value 'date opened


End Sub


Public Sub TextBox10_Change()


Dim j As Variant


j = TextBox10.Value ' bill to address


End Sub

 

[Dryver14]

I really am not sure of the userform elements but It would be worth saving the workbook as a template then calling that when starting a new workbook

 

[My Aswer Is This]

You script may open a UserForm.

But a new workbook does not come with a Userform
Unless you have a Workbook made as a Template with a UserForm already designed to do what you want.
This line of code in your script creates a sheet name "UserForm"
A UserForm is not a Worksheet.

wb.Worksheets("UserForm").Activate

 

[mikerickson]

You said:

This new workbook then needs to include a userform ​

Well if this is a brand new Workbook it would have no UserForm.

To write some type script which would build you a new UserForm every time you open a New Workbook would be a very complicated script. Putting in all your controls and scripts into those controls.

Now you could build a Userform and put it in your "Personal" folder and then open it using a KeyBoard shortcut after opening your new workbook.

An alternative would be to have the userform and all the template sheets in the Master workbook. The NewBook routine would SaveAs a copy of the Master and delete those parts that weren't needed in the Client Workbook.

 

[SBF12345]

Thanks for the responses, let me see what I can do given the replies thus far...

An alternative would be to have the userform and all the template sheets in the Master workbook. The NewBook routine would SaveAs a copy of the Master and delete those parts that weren't needed in the Client Workbook.

I was using a similar template method for some time

 

[SBF12345]

Hi mikerickson,

I am again getting into the userform project. Could you elaborate more on the structure of the "Master Workbook"? Would the hypothetical Master Workbook open with a userform everytime? And at the point the Master Workbook is saved with a new filename (client workbook) upon reopening the client workbook would the userform open too? would textbox contents be present?

 

[mikerickson]

First, make a typical client workbook, with its userform. Get this workbook exactly the way that you want it.

Then, add sheets and new modules and all to create a master book. Don't change or modify any of the client code or sheets. All new modules.
When you copy that larger workbook to make a client book, delete the sheets/modules that were added after your client workbook was done.

What opens when is up to you.