Microsoft Excel and Automation-2

 We have seen in the earlier Article Microsoft Excel and Automation that with a simple Macro Statement we could transfer control to a particular area of Worksheet very quickly, when several Worksheets are there in a Workbook. We have designed a Control Sheet and a Control Data Sheet to store the user’s choices and use those values for running the Program.

Excel Macros can make our work easier by automating several tasks like Print Previewing or Printing of Worksheets, hiding all worksheets except the one that we are working on, transfer data from other worksheets to a specific range linked with the Graph Chart so that the Chart changes dynamically and no need for creating several Charts across sheets and so on.

We can bring in lot of improvement in that example Workbook that we have created earlier and in this Article we will concentrate on a simple issue, specific to what we did with the earlier macro. We have seen that we can transfer control to a particular worksheet among several worksheets in order to avoid scrolling sheets to the right to find the specific sheet location that we want to work with. If you have tried that example, then a Question is already on your mind that how do we pass control back to the Control Sheet, without clicking on the Tab Scrolling Buttons?

Since, we have transferred control with a macro we need another macro to bring the control back to Main worksheet where we have designed the List boxes and Command Button for automation. But this macro we cannot run from the Main Control Sheet. We will run it conveniently from a Toolbar Button above. We need to create a custom Toolbar Button and attach the new Macro to the Toolbar Button. But, the new Toolbar Button will remain in the machine where you will create it. If the same workbook is open in a different machine the Button will not be available there, but we can make the Custom Toolbar Button to travel with the Workbook so that it can be used in any machine.

First we will write a Macro and then go for the Custom Toolbar Button creation.

1. Select Tools - -> Macros - -> Visual Basic Editor.

2. Click on the Project Explorer Toolbar Button (or press Ctrl+R) to split the Visual Basic Editor window into two. Left side panel will list the Worksheet names and the right-side window is for VBA Code editing. Check the left side panel under the heading This Workbook shows a Folder with the name Modules. If you are using the same workbook that you have created for the earlier example then this will be there. If the Module Folder is there then double click on the VBA Module below that to open up the VBA Code Editing window. If the Module Folder is not there then select Insert - -> Module to insert a Global VBA Module.

3. Click on the Code window at the right side and select Procedure from Insert Menu.

4. Type the Name GotoControl in the Name control and select Sub in the Type Category.

5. Select Public in the Scope choices.

6. Click OK to insert the empty Procedure skeleton in the VBA Window.

7. Type the following Macro Statement without making mistakes in the middle of the two lines. See that you are keying in a period (.) between the right parenthesis and the word select.

Worksheets ("Control") .select

1. Select View - -> Toolbars - -> Customize.

2. Select the Toolbars Tab (if it is not the current one)

3. Click New to create a new Empty Toolbar and type a Name (say myToolbar) in the Toolbar Name control and click OK. An Empty Toolbar will show up, look for it if you could not spot it. It may appear anywhere within the Excel Application Area. You may drag and place it among the existing Toolbars above.

4. Click on the Commands Tab on the Customize dialog control.

5. Click on File in the Categories List and Click New...

6. Right-Click on the Button and type &Go2Ctrl in the Name Control.

7. Select Text Only option in the displayed menu. The Name Go2Ctrl appears in the Button face.

8. Click on the Assign Macro to display all the existing Macros available and among them you will find the GotoControl that we have created, click on it and Click OK and Close the Customize Dialog Control.

9. Now we are ready to test our creation. Click on the Go2Ctrl Toolbar Button and you will be placed on the Control Worksheet.

10. Click on the Goto Command Button after selecting a Worksheet and Month from the List to transfer control to that sheet.

11. Try clicking again on the Go2Ctrl Toolbar Button to jump the control back to the Main Worksheet.

Now, we need to make one more change for the Toolbar to travel along with the Workbook to other machines.

1. Select View - -> Toolbar - -> Customize.

2. Click on the Attach... Button.

3. You will find Custom Toolbars List at the left-side panel. Select myToolbar from the list and Click Copy Button to make a copy to the Toolbars in Workbook List.

4. Click OK to Close the Customize dialog box.

If your Excel File can be put on a Network Drive then you can try opening it from a different Machine. The Custom Toolbar with the Go2Ctrl Button will come along with the Workbook on that machine also.

a.p.r. pillai

http://www.msaccesstips.com