UserForm and VBA Subroutine Integration Brainstorm

[SBF12345]

Greetings,

I am trying something new and wanted to share my ideas and get some feedback and potential resources that could improve results before I jump into construction of userforms and subroutines.

I would like to build a tool that is GUI friendly using Excel Userforms and VBA subroutines. What I envision is a userform with potentially multiple tabs that allows assignment of a number of integer variables as well as other types of variables. With the variables defined I would then like to be able to select a variety of subroutines to process sheets of data given defined variable inputs.

When I create a new userform I can view the object and the code. The userform sub is default "private" along with added textboxes, etc. I would like to declare everything public for the sake of ease and eliminating the potential hassle of experiencing constraints in accessing defined variable data in the userform needed in a data processing subroutine. Is there any good reason for retaining a private sub as an individual user of a machine with disregard of another entity executing macros, viewing information, etc.?

I would like to add stuff to the userform and allow for the assignment of various types of variables(via dropdowns, manual entry, and checkboxes) to be used in one or more data processing type public subroutines. I would also like to include buttons that run public subroutines to the userform, and most importantly create an environment that allows for any and all variables defined in the userform to be accessed by subroutines (also with execution buttons within the userform) processing of numerical data.

Ultimately I would like to be able to launch the userform from a button on a worksheet in a workbook.

Not totally sure what I am looking for here, but maybe if there are glaring pitfalls that the reader of this thread has experienced you could share some of your userform experiences, resources, etc.

Thanks!

 

[AFPathfinder]

You should keep the subroutines for the controls/userform as private as they are referencing specific objects that should only apply to that userform. I can't see a purpose for expanding their scope. You can have public subroutines and functions without messing with the control events.

You can also pass arguments (values & objects) to other subroutines and functions from those control events. Functions can return values and objects back to the caller, as well (I think subs can, too, but never tried). If you have code that you would like to run from multiple sources, it's probably best to put it into a location that can be accessed by the various sources.

If you're main goal is to retain configuration values so those changes are maintained between userform instances, there needs to be a repository for that information. Some use a hidden worksheet, such as in xlam and xlsb files. Others might use a txt file to house all those settings.

A lot of variables are probably defined dynamically by the code and don't need to be defined by a user, such as the last row in a dataset or the name of the active workbook. I try to limit the user to only the values that cannot be obtained without input from the user to reduce user error.

 

[SBF12345]

A lot of variables are probably defined dynamically by the code and don't need to be defined by a user, such as the last row in a dataset or the name of the active workbook. I try to limit the user to only the values that cannot be obtained without input from the user to reduce user error.

I'm with ya here.

I'm still a little hung up on the subject matter below.

You should keep the subroutines for the controls/userform as private as they are referencing specific objects that should only apply to that userform. I can't see a purpose for expanding their scope. You can have public subroutines and functions without messing with the control events.

I would like to set up the userform as a series of steps using a branching structure that, depending on what is chosen, the subsequent steps will require information relevant to the previous choices. Many of the variables defined in the userform will be selected from ranges defined in other workbooks. Could I run into issues if the range of "dropdown menu" options is defined by another worksheet?

Also, what are my options for opening a userform? I was going to put a button on another spreadsheet to run/execute/open the userform. Can I create a desktop icon for a userform?

Thanks!

 

[AFPathfinder]

It's all about how you code the events. I use If statements to control the various conditions that need to be controlled. If you need to keep a user from filling out a control too early, bind the enabled property of that control to the value of a different control. In a couple cases, I've changed the .Visible property of groups of controls to keep users from getting confused or skipping necessary steps.

A lot of it is the design of the form and if the controls are logically ordered, but it's amazing how others use those forms.

You can place a button many places in Excel (quick access toolbar, ribbon, worksheet, another userform, etc) that can execute a macro or open a userform. You just need to build a little helper macro and assign it to that button.

Sub UFOpen()
    Load UserForm1
    UserForm1.Show
End Sub

 

[SBF12345]

Interesting, I will have to think about how this might work. I'm still sort of leaning toward setting variables for textbox, dropdowns, etc as public subs as the variables set in the userform will be referenced in one or more public subroutine events also executable via the userform. The If approach might also work...something like IF textbox1.value <> "" Then OK. I'm not in a rush so I want to make sure that the logic is spot on.

Thanks for the little helper macro too.

 

[AFPathfinder]

If you're running into problems trying to pass variable values outside the userform, I remember being at that roadblock before, too. I just depends on if you want the variable values to exist after the userform is closed. If it's just to use the values while the userform is still active, you can pass arguments to subs and functions by listing them in the parameters:

Function IsHoliday(ByVal oDate As Date) As Boolean

When you call the function from a userform event, you would need to pass it the value as noted in the parameter:

bHoliday = IsHoliday TextBox2.Value

Definitely keep at it as it sounds like a grand learning experience in VBA.