Basic drawing objects and attach macros to those instead on a worksheet and finally we'll end up with a look at how you can design your own ribbon tabs and toolbars which gives your users the.
Opportunity to run macros whichever workbook they happen to be in so let's get started in previous videos we've shown you how you can run your VBA code from.
Within the Visual Basic editor which is a really handy thing for you as a developer to be able to do but not much good for your end users so in this video we're going to focus on ways you can.
Make it easy for your end users to run the code that you've written for them one of the simplest things you can do for your users is assign keyboard shortcuts to run your Macros so to do.
Version of excel you need to head to the tools menu choose macro and then choose macros you can also just press alt and f8 there again as well back to 2010 if I click on the macros.
Button the list of macros then appears and you need to select the one that you want to assign a keyboard shortcut to and when you have done that click on the options button on the dialog box and.
Type in the letter that you would like to use for your keyboard shortcut now you need to be quite careful about which letter you choose to type in here my macro that I'm using is.
Called create and label new sheet so I might think it's a sensible idea to to assign the letter c to uh to run my macro and that means that later on I would have to press contrl and C to run.
My macro back uh the people who use keyboard shortcuts a lot already will have spotted a problem with that control and C is also the keyboard shortcut for copy and if I.
Posts Related:
- Usuário padrão ADMIN - Planilha Cadastro de Membros e Controle de DÃzimos.
- System Variables
- Webinar Build a Custom Web App with Make
Use the letter c my keyboard shortcut
Will take precedence over the existing keyboard shortcut so when I press okay and then close down the macro dialog box if I press contrl C on the keyboard now.Rather than copying a cell it actually runs my custom macro instead so I'm actually going to change that I'm going to go back to the macros dialog box select create and label new sheet and.
Then choose options one way you can avoid that from happening or make it less likely you'll encounter an existing keyboard shortcut is to type in capital letters so if I.
Type in a capital letter c that means that to run my macro back I need to use controll and shift and C so if I click okay and then close down the macro dialog box again I'll just quickly.
Delete this sheet sheet and then press contrl and shift and C that will run my macro back contrl and C by itself will now copy a cell as it should do now keyboard shortcuts have a variety.
Of drawbacks first of all you as you've just seen there's the risk of overwriting an existing keyboard shortcut there's also the added problem of having to remember the new sequence.
Of keyboard shortcuts to run your Macros as well it's also not immediately obvious to a user that a keyboard shortcut exists in a workbook that they can use to run your Macros so for all.
Those reasons you might find it more sensible to use buttons that your users can click on to run your code to draw a button that can have a macro attached to it in Excel 2007 or.
Later again you'll need to be on the developer tab of the ribbon and look for the insert tool when you click on the drop- down arrow you'll see a list of various different objects you can add to.
To your spreadsheet they're actually
Divided into two separate sections in this list the top section is referred to as form controls and the bottom section is referred to as active X controls.We're going to be dealing with active X controls in a later video they're a little bit more complex to use than the basic form controls so for now just stick to the top half of this this.
Window if you're in Excel 2003 or earlier you can display the form controls toolbar so if I quickly switch into Excel 2003 if I right click towards the top of the screen near any existing.
Toolbar I should find that there's a forms toolbar that I can display the corresponding toolbar for the active X controls is referred to as the control toolbox but again we're.
Going to avoid using the active X controls here so if I choose forms I should see a new toolbar appears it's actually already been nested at the top of my screen but you can see this is the.
Forms toolbar with a similar list of controls as I was seeing in the later version of excel so back to Excel 2010 as I'm in to draw a button all I need to do is from my list and the insert tool.
Click on the first object here which is a button looks like a little boring gray brick and then move the mouse cursor over the screen and simply click once again to draw the.
Button from the dialog box I now have to choose which macro I'd like to attach to to that button so I select the only one that I have available here and then click okay and finally my button will.
Appear so while I have it still selected I can do the standard things as I can to any drawing object I can resize it by clicking on the resizing handles I can select the text that's in the button as.
Well I can click and drag to move it around I can select the text as I was trying to do there and I can overt type that text with a new slightly more descriptive phrase so create new sheet.
Perhaps I might need to resize it again a little bit more if you're particularly fussy about where your button sits you can make it line up to the borders of cells while you click and drag to move.
It around if you hold down the ALT key on the keyboard the one to the left hand side of the space bar you should find that your button will now snap to the borders of cells and the same is true.
When you resize it as well instead of moving smoothly it will jump and snap to the edges of cells so that's in case you're a perfectionist and you want to get things to line up neatly when You'.
Finished modifying your button what you can then do is click on any other cell in the worksheet and that effectively activates the button so the very next time I hover.
The mouse over it I get the standard hand sort of hyperlink symbol and when I click on the button itself it will now trigger and run my macro if you find that you need to.
Modify a button after you've added it to your spreadsheet or if you need to move it around of course you can't select it anymore by simply clicking on it because doing that will trigger your macro again.
So to select the button the easiest thing to do is simply rightclick on it and from there you have a variety of choices you can choose to edit the text which lets you modify the information.
You've typed in you could choose to assign a different macro to the button so as you saw earlier on you get the assigned macro dialog box you could also choose to do some basic formatting of.
The control there's quite little you can do actually with these basic form buttons you can essentially change the font size color and and type face face that you're using a bit of alignment.
Changes and size but you can't do fancier things like change the background color or change the Border style and in fact in this version of excel if I head to the format tab the.
Drawing tools format tab you'll find that all the fancy options are all grayed out so it really is a case of just being able to format the text here if you wanted an object which you.
Could use to run your code and you could format with the fancy options such as fill and outline and shape effects then it turns out that you can actually attach a macro to any basic drawing.
Object that you can add to your spreadsheet so if I had to the insert tab in this version of Excel or in an earlier version you might simply use the drawing toolbar so you can find the auto.
Shapes tools you can essentially draw any basic shape that you want to using the shapes menu so I could draw a fancy rounded rectangle or a 3D shape or even heart and lightning flashes if I if I.
Must so let's let's have a heart shape that will run our our lovely macro so if you've drawn a basic shape of course you get all the extra lovely options you can apply to it in terms of formatting I can.
Add text to the object just by simply typing into it I might need to do a little bit of formatting there to get it this uh sort of be aligned properly but when I want.
To attach a macro to it all I need to do is right click on the object and choose assign macro and you can do this to not just basic drawing object you can also do it to clip art or inserted image.
You can attach a macro to essentially anything you can draw on a spreadsheet so once I've done that again I need to click away from the object to essentially activate it and then next.
Time I hover the mouse over the tool I get the standard hyperlink mouse cursor the finger pointing and when I click on that object it now runs my macro one downside to using buttons and.
