Collins, Darryl
Darryl.Collins at anz.com
Sun Mar 21 18:45:46 CDT 2010
Hi John, Sorry for the sluggish reply... We are a day ahead in here Oz so it was Friday afternoon real quick. Now of course it is Monday morning real quick as well .... Hmmmmm. There numerous ways to fire code (or call a form) from the worksheet. '------------------------------------------------------- 1: Form Button This method is the most simple and will work great in most Excel applications. There are a few drawbacks and gotchas, but these depend on what you want to do and how you use the workbook. To insert a form button goto the Excel Application and right mouse click anywhere in the command bar space (or even on a command bar) usually sitting between the top "File > Open Menu" and the actual worksheet. You will get a whole list of available toolbars, one of them will be called "Forms" - choose this one. The Forms toolbar will usually 'float' in the middle of the worksheet. Anyway, the 4th option will be "Insert button". Click on this and then 'draw' the button with the mouse on the worksheet. You can make it as big or small as you like (and resize it later if you get it wrong). When you finish the draw Excel immediately pops open an "assign macro" button. If you already have your code written (and it is not in a "option private" module you can see it here and assign it to the button. As all my code module are usually hidden from the users I just press cancel here to get rid of the pop up. These buttons are very simple. To rename the button you merely highlight the text and type what you want. You can change the font properties merely by using the font options on the toolbar - just like you would any font properties in the workbook or MS Word or whatever. To assign your code to it, right mouse click on the actual button - choose "assign macro" and copy or type in the name of your procedure you want to call when the user presses the button. Pros: - Very quick and easy to set up - Fairly reliable as long as users leave them alone and you are not doing lots of copy / paste worksheet stuff - Native to Excel so they rarely cause application issues or crashes. - No internal security issues Cons - It is easy for a user to foul these buttons up - rename, resize, delete etc. Worksheet protection and some button properties options can limit the potential damage but just be aware this maybe be an issue - If you are doing Excel trickery like copying and pasting sheets you can run into problems such as multiple buttons all sitting on top of each other, they look like one, but there maybe 100's of them there, this can cause Excel to become unstable. - If you copy the sheet to another workbook, Excel tries to be clever and will automatically link the button to the original workbook - this is usually a right PITA. '------------------------------------------------------- 2: Active X Control Button To insert an Active X Control button goto the Excel Application and right mouse click anywhere in the command bar space (or even on a command bar) usually sitting between the top "File > Open Menu" and the actual worksheet. You will get a whole list of available toolbars, one of them will be called "Control Toolbox" - choose this one. The 6th Option will be an Active X Button. These are a bit more fiddley to setup and use. For the novice they are downright confusing. Firstly I recommend you also make the "Visual Basic" Toolbar visible. You will need this toolbar to toggle the 'design mode' on and off. Without design mode turned on you cannot change any of the properties of the Active X button. To assign properties or changes the Active X command button. First goto the "visual basic" toolbar and ensure you are in design mode. You can toggle this on and off by press the design mode button on the toolbar. Once in design mode you can move and resize the command button. Right Mouse Click on the command button and the properties popup will appear. This is very similar to MS Access and is exactly the same as Excel VBE, only it floats in the actual application (weird but true). To make something happen when you press the button, you can Right Mouse Click on the command button (again when in design mode, otherwise this wont work) and choose "View code". I suggest you rename your command button first using the properties window and then choose view code, otherwise you can going to have to manually reword the code later. Pros: - You have a lot more control over the properties on an Active X control then over a form button Cons: - Depending on your settings, Active X controls in Excel often trigger a second security warning to users on startup (over and above the 'do you want to enable macros?' warning). A lot of folks get annoyed at all these darn warnings. - Active X controls are known to be unstable in Excel and can cause the application to crash from time to time. - Active X controls in Excel will sometimes change shape (usually the keep growing bigger) when the application opens. Sometimes a restart of the app will put everything back correctly, but this is a well known problem. Doesn't happen everytime but usually it will happen occasionally - Normally when you are showing your application to a prospective client I find!! I rarely use Active X control in Excel. They are usually more trouble (or potenital risk) then they are worth in my experience, but there are times they are useful to have around and there are some nice helpful controls (such as the calendar date picker). '------------------------------------------------------- 3: Custom Toolbars You can create custom commandbars via code quickly and easily in Excel. If you want to take this path, let me know and I will email you some code showing you how to do this. Pros: - Rock solid! custom commandbars are virtually unbreakable and pretty much never fail. They are fantastically reliable and useful as the users can dock them when it suits them. (top, bottom, right left or floating). - They are very fast and easy to create using code. - You can turn them off and on when required (such as when the users move to a particular sheet, or a user click on a particular cell). - The work in any version of XL including XL2007* (see caveat below) - Users are familiar with Commandbars are very comfortable using them. Cons: - That damn Ribbon in XL2007 has pretty much fouled up the functionality of custom command bars. The command bars will still work fine and can still be called on and off on demand, but rather than the command bar magically appearing for the user (say floating in the middle of the application where they can see it) it will be stuck under the addin tab on the ribbon. This means that unless the user knows or is told to activate the addin tab and look for the toolbar, well, nothing happens as far the the user is concerned. Sure you can write custom Tabs into Excel's ribbon, but then it is not backwardly compatible with older versions of XL. Maybe XL2010 will improve on this foul up but I am not hopeful. - You need to use a "worksheet/workbook" activate / deactive event to turn off and on the commandbars (such as when the user leaves your workbook and goes to another, you would turn off your custom commandbars so they are not visible in the other workbook. The event bit works great and is very reliable, problem is it will empty Excel's clipboard. This means if your uses are trying to copy from one workbook to another in the same application of Excel it will fail. If you have two workbook open in two separate applications then you don't have this issue. I usually have code that will force the application to open in it's own version of Excel and if the user trys to open another workbook it will push it automatically into another Excel app. '------------------------------------------------------- 4: Events You can call code from specific events such as worksheet_change - which can be limited to a specified range. There are numerous events that can be used to fire code. If you want to know more about this approach then I will try and fill you in on some methods. Hope that helps Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, 19 March 2010 10:53 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Excel - HOW TO SEE MY MODULES COLLECTION Very good, thanks! Can I insert a button into a cell so that they have something to click on to open a specific form? John W. Colby www.ColbyConsulting.com Collins, Darryl wrote: > "A form would be a good thing but I don't know how to create one, how > to open it, push text to controls on it etc." > > > Hi John, > > To create a form, goto the VBE and Choose "Insert" > "User Form" from > the menu. This will create a form in the code window that can be > viewed (and toggled) between an Object View (which looks like the > form) and a code view (which will show you the underlying code for the form). > > Adding controls etc is pretty much like in Access, although in Access > you create your forms in the app rather than the VBE. > > To make the form visible to the user in the app you need some > additional code in a module like this > > '===================================================================== > Sub ShowMyForm() > Application.ScreenUpdating = True > frmMyTestForm.Show > End Sub > '===================================================================== > > > > "This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential, may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."