In this video we're going to talk about controls which you can build into your Excel file for the user
to interact with.
Generally speaking not every user is going to be familiar with VBA so you don't really want those individuals
accessing your code when they don't understand it.
And most of them wouldn't know how to run a macro anyways.
So instead we can build controls into the file for them to use.
So for example in my Excel file over here I go to my developer tab and open up my visual basic window
we can see I've already inserted a module and I've pre coded a little message box here.
So I called this subroutine Hello message with a little message box.
Tell the User Hello.
And if you aren't sure how I created this you can reference the videos on creating a subroutine and
dialog boxes.
Otherwise I'll go ahead and give you a second to pause and copy all of this code if you want to follow
along.
Otherwise you can just follow along on the video.
So now let's go ahead and go back to our excel file normally to run a macro.
We would go to the developer tab click on macros highlight the macro that we want to run.
In this case Hello message and click the run button and we can see I get a little message box that says
hello user.
Your code has successfully run and this is how we expect the system to behave so click.
OK.
Now with this method you're expecting your user to have access to the developer tab and understand how
to use the macros button how to find the appropriate macro and run that macro off from this window.
You're also assuming that by giving your user access to the developer tab you're also giving them access
to your visual basic to all of your code in the hidden aspect of your database.
And that's not necessarily something that you want to do.
So instead we can build in a graphical user interface or G UI which you'll sometimes hear referred to
as a gooey.
Now this means that we can go ahead and use this insert button under controls in the developer tab God
and click on this first button control here we can see it says button form control and go ahead and
just draw a big square on your Excel file.
Now we're going to tie this to a macro and this will make sense in a second.
So just go ahead and click on the hello message and click ok and we can see now we have a really big
button that's appearing in our excel file.
Now the form control that we just entered onto our excel file if we look in the insert menu this button
is found under foreign controls.
There's a different version of controls called Active X controls now form controls are much much simpler
and they're built into the Excel system itself.
Active X controls are a little bit more flexible but lots of the company systems won't actually trust
the Active X controls and it will disable them as soon as you open up the excel file.
There are also Microsoft specific.
So they generally don't work on Mac operating systems.
So as a general rule of thumb you're going to want to use form controls to build your duty rather than
active X controls so let's talk about this button that we just inserted when I click on this button
it will automatically run my code for me it's because we've tied our code to this button click but let's
say I want to select this button and I want to do something say change the text to select the button
don't left click right click on the button and this will give you the option to edit the text to assign
a different macro to change the formatting of it.
So let's go ahead and change the edit text and we'll change this caption to read Message button and
we can go ahead and click out of the button and we'll see that the text has been changed.
We can also change the format of this so we'll go down to format control and we can see we can make
this superscript a subscript we can make things bold if we wanted to go through properties we can change
what happens the behavior of the button as you change things.
So if you want your button to be seated in a particular set of cells you might want to say move and
size with cells whereas if you want to just have your button set on your screen and be totally unrelated
to the cells you can use Don't move or size with the cells.
So there's a bunch of different things that you can do to format your button to behave however you want
it to behave changing the size changing the margins all of that sort of thing.
So for now we'll click Okay.
We can see my text has become bold because that's what I just changed in my format.
The last thing that you'll want to use if you right click is a sign macro.
So if we click on a macro we can see it pulls this window back up and this is that first pop up winter
that we got when we inserted the button initially and usually this will list all of the macros that
are found in this workbook.
So usually you'll have two or three different macros and you'll want to be careful about which one you
assign to which button.
So in this case if we wanted to change this to something else you could open up this window click on
whichever one you wanted to tie to your button and click OK to change it.
So again when I click on this button we see my code has been successfully run.
Now there are different types of controls outside of just the button control.
There are dropped down the list box combo box check boxes there's a whole bunch of different options
that you can use in these form controls.
So just as an example let's just click on this checkbox and draw a little checkbox on here somewhere
and we can see it pops up as checkbox 3 we can click it we can on click it to check and uncheck this
box.
So this is a different way that you might allow your user to interact with your code.
Generally speaking if you're going to be putting things that are more complex than just buttons on your
Excel file for user to interact with you're going to want to create a user form instead.
And there are a couple reasons for that.
The primary reason being a user form is much easier to control from the VBA window.
So just to give you an idea of what that might look like if I open up my VBA window I can insert a user
form and we'll talk about all of this in greater detail later but we can see that on my user form I
have the same set of controls I can insert a text box here I could insert buttons you can insert whatever
you want onto this user form so it's the same set of controls.
The difference between the controls appearing on your user form and the controls appearing on your Excel
file is that these controls on the user form are a lot easier to tie direct code to they act almost
like active X controls which are the more complex controls that we were talking about in this dropdown
window.
So generally as a rule of thumb it's best to put only button basic simple controls onto your user excel
file to create user dashboard and those buttons can be linked to more complex data entry fields like
a user form.
So that's the best way.
Generally speaking to set up your Gilly and the main thing that you need to remember is to use the insert
button and use form controls right click the control to assign the macro so I will go ahead and talk
a little bit about the active X controls just in case you ever do have a situation where you need to
use an active X control.
Let's go ahead and insert an active X button on here and we can see right off the bat.
This is already a very different control when I've inserted this button it didn't pop up and asked me
what macro that I wanted to tie to this button like it did when we entered our form control up here.
Now we can see this is also automatically activated design mode.
And what this does is I take this out of design mode.
If this button had a command or something to run with it a macro tied to it.
When I click on that button it would automatically run when I turn on design mode.
If I click on this button it instead selects the button for me.
So as we were talking about what this form control we have to right click on the form control to pull
up a window to interact with it with active controls.
You can turn on design mode and instead of right clicking them you can just regularly click on them.
Now when we do right click on this we can see we have a properties option here.
So we'll go ahead and click on properties our properties window has popped up and this is what it looks
like.
You'll notice this looks very similar to the properties window in our actual VBA window that we've interacted
with a couple of times in the past so we can see we can actually rename this button we can change the
color of this button.
The caption is the text that appears on the front of the button so we can change all of these different
appearance options of this button.
It is a little more in depth than just a regular form control button like we looked at earlier.
The other thing we can do when we write click on this.
We can view the code that's tied to this button and we can actually put code directly into this window
and this would be tied directly to our button that's appearing on our main fact sheet here.
So this is a little bit different than the farm control where we pre wrote the code and then came back
through and tied it to the button.
If you use Active X controls you can insert the button first and then Ty code and build it into this
button click itself.
So it is a little bit different.
It's a little bit more complex and this is actually how most of our user form controls are going to
behave.
And so we'll talk about that when we'd go through user forums but just in case you ever do need to use
an active X control for whatever reason.
This is how they would behave this is how you would interact with them.
And of course you would want to remember to turn off design mode whenever you're done building your
user interface.
So again in this video we talked about controls we talked about active controls and form controls.
We talked a little bit about user forms which we'll discuss more in a later video.
And generally speaking we decided if we are building a graphical user interface into our excel file
it's best to use simple form controls like buttons for your user dashboard and then put the more complex
controls like your active X controls into user form or some other method of interaction for your user.
And again we'll talk about user forums more in a later video.