Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Your own interface to SQL*XL functionality (Read 5892 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Your own interface to SQL*XL functionality
27.11.05 at 12:55:22
 
A frequently asked question by people who use SQL*XL and macro recording or macro (VBA) coding to make utilities is how to present an interface to their end-users. In this topic I want to summarise the options.
 
When building your own solutions you will create VBA code. The VBA code in Excel is stored in an Excel workbook (.xls file) or you can compile it into an addin (.xla file). The xla file can simply be created within Excel (not the VBE) by choosing Save As and choosing the xla file format (last option in the list).
 
You can present your system in various ways to your users. I have compiled a list below with possibilities with instructions how to create such an interface:
 
  • Manually running the macro:
    This is the easiest way you can let your users run the macros. You need to make your .xla or .xls file available to the end users and they can open the file and run the macros inside of them.  
    After opening your code file they need to use Excel's menu: Tools | Macro | Macros. Excel will display a list of macros the user can run. They need to pick the macro you have told them to run. Please note that all public created subroutines that you have created in modules will be in the list. Code in class module or in a user form are not available. If you want your users to run code in these modules you need to provide a subroutine in a code module.
  • Make a button in a worksheet.
    A button in a worksheet can be used to start your macro. You can make a button to execute code that is stored in another workbook. In other words, it can provide a way to separate your code from the front-end (the button). They may be desirable so you keep a central addin that everyone is using.
    There are two ways you can create a button:
    • Using a forms button
      • In Excel use the menu items View | Toolbars to view the Forms toolbar
      • Select the button control by clicking on the button icon on the toolbar
      • Now draw a button (rectangle) somewhere on your worksheet.
      • A dialog pops up that asks you to assign a macro. You can always open this dialog yourself by right clicking on the button an choosing Assign a macro.
      • Note that you need to have your workbook or addin with the code open at this point. Select your macro. It will probably be in the following syntax: book1.xls!Module1.Macro1.

    • Using a Control Toolbox button
      • In Excel use the menu items View | Toolbars to view the Control Toolbox toolbar
      • Select the button control (commandbutton) by clicking on the button icon on the toolbar
      • Now draw a button (rectangle) somewhere on your worksheet.
      • Double click the butoon and Excel will open the Visual Basic Editor and code a new sub for you:
        Code:
        Private Sub CommandButton2_Click()
        
        End Sub
         
        
        

      • When you are making a button to execute a macro in the same file you can just code a call to the macro. If the macro is called macro1 you just type macro1 in the Sub above.
      • When you are making a button to execute a macro in another file - and you probably want to run code in your addin - you need to program a call to the Application.Run method that you give the name of the macro to execute. In my case I have saved my macro in book1.xls. The code to call it is:
        Code:
        Private Sub CommandButton2_Click()
            Application.Run "Book1.xls"!Macro1
        End Sub
         
        
        

      • To test your button you need to exit design mode. On the Control Toolbox toolbar click the triangle icon Exit the design mode.


  • Create a button on a (new) toolbar.
    • Use Excel's menu: View | Toolbars | Customize.
    • If you want to create a new toolbar (is optional) select the Toolbars tab and press the New button, type a name for the toolbar and press ok. Note that you can make your new toolbar dock with the other toolbars by dragging it into the other toolbars at the top of the Excel window.
    • To create a new toolbar button select the Commands tab.
    • From the categories list select the macros option
    • Select Custom Button
    • Now drag the button onto a toolbar of your choice. It can be the new toolbar you created above or on any other visible toolbar.
    • Right click on the new button and choose properties
    • Use the assign macro option to assign the macro that runs when you click the button. You can also specify in this section which icon you want to use and other properties of the button. You are probably specifying to run a macro in your code addin or workbook. Excel will automatically load the addin or workbook when it is not already loaded in Excel.

     
Back to top
 
« Last Edit: 27.11.05 at 13:46:41 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1