Office Assistant And Msgbox Menus-2

Continued from Last Week. - Access 2003.

This is the continuation of last Week's Article: Office Assistant and MsgBox Menus. Readers may go through that Page first, if you have not already done so, before continuing.

Last week we learned, how to use the Office Assistant for MsgBox in a simple way and saw how to create a Menu and present it to the User, with the Labels Property of the Balloon Object.

Labels().Text Property

We have seen that we can display the Menu from Labels().Text Property in three different ways by setting the Values of Balloon Type Property:

1. Balloon Type = msoBalloonTypeButtons

   The User can click on any of these buttons to select one of the options presented.

2. Balloon Type = msoBalloonTypeBullets
3. Balloon Type = msoBalloonTypeNumbers

The second and third Balloon Type Values display the Labels().Text in different styles, but they cannot be selected by the User. So, we can use these options for different situations in Programs.

Let us look a little closer at the overall behavior of the Balloon Object when you display MsgBox with Office Assistant, to understand it better.

If you go back and try those earlier examples, you can see that while a MsgBox with the Office Assistant's Balloon Object is displayed you cannot click or work with any other object in the database unless you dismiss the Balloon by responding to the actions you are suggested to do, like clicking on the OK or Cancel Button and so on. This is true in the case of the normal MsgBox() Function of MS-Access too. But, Office Assistant has some, solutions to this rigid behavior of Message Boxes.

When the Balloon is displayed, you cannot open a Form or Report to check something on it, before clicking the Yes or No Button on the MsgBox because the Mode Property of the Balloon Object is set with the default value msoModeModal.

The Mode Property of Message Balloon

I have not introduced the Mode Property to you in the earlier examples to avoid overcrowding of usage rules. Once you are through with the basics it will be easier to understand other things associated with it better. You have already seen that you can create Message Boxes using Balloon Object of Office Assistant with a few lines of Code and display them in style without using these properties.

The Mode property can be set with three different values to control the behavior of the Balloon Object.

Mode = msoModeModal (default).

This value setting forces the user to dismiss the Balloon by responding to the suggested action before doing anything else, like normal MS-Access MsgBox. You have to click on one of the Buttons (if more than one is showing like OK and Cancel) on the MsgBox to dismiss the Balloon before you are allowed to do anything else.

Mode = msoModeModeless.

This value setting allows the User to access other database objects, while the Balloon is active. But, this forces the use of another Property CallBack, to run a separate Sub-Routine to do the testing of a selection of choices or do different things based on the choice and finally to Close the Balloon Object.

Mode = msoModeAutoDown.

This value setting dismisses the Balloon (MsgBox) automatically if you click somewhere else ignoring the Balloon.

The first and last Mode Property value setting and their usages are very clear. But, the second one (msoModeModeless) needs the CallBack Property Value set to the name of a valid Sub-Routine otherwise the Balloon will not work. You cannot load the CallBack Property with an empty string either because that will trigger an Error.

So let us see how we can re-write the earlier Program to understand the usage of Mode Property sets to the value msoModeModeless and Callback Property with the name of a Sub-Routine to handle the selection of options displayed in the Menu.

The Mode and CallBack Property.

The Modified Code with Mode and CallBack Property Settings and the sample code for the required Sub-Routine MyProcess() is given below:

Public Sub Choices()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print. "
    .labels(3).text = "Pivot Chart. "
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of  " & .labels.Count & " Choices? " 
    .mode = msoModeModeless
    .Callback = "myProcess"
    .Show
End With

End Sub

Sub MyProcess(bln As Balloon, lbtn As Long, lPriv As Long)
Assistant.Animation = msoAnimationPrinting
Select Case lbtn
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select
 bln.Close
End Sub

The Call Back Property is set with the Sub-Routine name myProcess. When the user clicks on one of the Options from the displayed MsgBox the MyProcess() Sub-Routine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Sub-Routine when called:

1. The Balloon Object bln.
2. A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
3. A Long Integer type value lPriv (Private) uniquely identifies the Balloon that is called the Sub-Routine, if there is more than one Balloon active in memory at the same time.

NB: There is no such thing as a collection of Balloon Objects. But, you can create an Array of Variables with Balloon Object, define different Property Settings for each of them, and the Show() method is run with their respective index numbers in Programs when you need them to appear.

The lbtn Variable will have the value of the User's choice. This is tested in the Sub-Routine and runs the Docmd.OpenReport action within the Select Case End Select Statements.

The Assistant.Animation = msoAnimationPrinting line is placed in the Sub-Routine, rather than in the main Program as part of the Balloon Object Property setting, to animate the printing action only after the User makes a selection from the displayed Menu otherwise, the printing animation will run before the selection of choices.

The bln.close statement dismisses the MsgBox.

Next week we will learn how to use the CheckBoxes().Text Property Values of the Balloon Object to display a Menu with CheckBoxes in MsgBox.

• Network and Report Page Setup-2
• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color

Read More

Office Assistant and MsgBox Menus

Introduction - Access 2003.

In last Week's Article: Color and Picture in Message Box we have seen some methods to use the Office Assistant quickly, to display a Message Box with formatted text. We are not going to discuss further the formatting part, but I have a general feeling that Readers would like to know how this simple method can be used to obtain responses from Users, out of several options presented to them, and do different things based on their selection of choices.

I have already covered this topic by creating Functions like MsgOK(), MsgYN(), MsgOKCL(), etc., with the use of Office Assistant. These can be called with only the Message Text Value alone or Message Text and Title Values as Parameters, from anywhere within the Application like the built-in Function MsgBox(). I made an attempt to simplify the usage of Office Assistant through the above-mentioned Functions and others, which otherwise need several property values to be passed to the Office Assistant's Balloon Object for displaying.

But, in the simplification process, the method used in those Functions is not fully understood by several readers.

The Links to those earlier posts are given below for reference:

1. Message Box with Office Assistant -Access 2003
2. Message Box with Options Menu - Access 2003
3. Office Assistant with CheckBox Menu - Access 2003.

In the example code presented in last week's Post: Color and Picture in Message Box, we came across several Properties of the Balloon Object of Office Assistant that can be set with values before the Message Box is displayed.

We are going to work with these properties directly so that it is easy to understand their usage, rather than passing values for them through the Parameter List in the function definition.

Message Balloon Properties

Following are some of these properties:

• Animation
• Icon
• Heading
• Text
• Balloon Type
• Button

Microsoft Office Object Library

NB: If you have not already attached the Microsoft Office Object Library to your Database, try out the examples given here, then do that by following the procedure given below.

• Press Alt+F11 to display the VBA Code Window (or Tools - - >Macro- ->Visual Basic Editor).
• Select References from Tools Menu.
• Find Microsoft Office Object Library in the Available List and put a checkmark to select it.
• Click OK to close the Dialog Box.

Message Balloon Animation Constants

The value for Animation alone has about thirty-five different choices which are defined as Constants in the Microsoft Office Object Library. The constant values for Balloon Properties Animation, Button, Icon, and Balloon Type are given below for reference:

Animation	Icon
msoAnimationAppear msoAnimationBeginSpeaking msoAnimationCharacterSuccessMajor msoAnimationCheckingSomething msoAnimationDisappear msoAnimationEmptyTrash msoAnimationGestureDown msoAnimationGestureLeft msoAnimationGestureRight msoAnimationGestureUp msoAnimationGetArtsy msoAnimationGetAttentionMajor msoAnimationGetAttentionMinor msoAnimationGetTechy msoAnimationGetWizardy msoAnimationGoodbye msoAnimationGreeting msoAnimationIdle msoAnimationListensToComputer msoAnimationLookDown msoAnimationLookDownLeft msoAnimationLookDownRight msoAnimationLookLeft msoAnimationLookRight msoAnimationLookUp msoAnimationLookUpLeft msoAnimationLookUpRight msoAnimationPrinting msoAnimationRestPose msoAnimationSaving msoAnimationSearching msoAnimationSendingMail msoAnimationThinking msoAnimationWorkingAtSomething msoAnimationWritingNotingSomething	msoIconAlert msoIconAlertCritical msoIconAlertInfo msoIconAlertQuery msoIconAlertWarning msoIconNone msoIconTip
Button	BalloonType
msoButtonSetAbortRetryIgnore msoButtonSetBackClose msoButtonSetBackNextClose msoButtonSetBackNextSnooze msoButtonSetCancel msoButtonSetNextClose msoButtonSetNone msoButtonSetOK msoButtonSetOkCancel msoButtonSetRetryCancel msoButtonSetSearchClose msoButtonSetTipsOptionsClose msoButtonSetYesAllNoCancel msoButtonSetYesNo msoButtonSetYesNoCancel	msoBalloonTypeButtons msoBalloonTypeBullets msoBalloonTypeNumbers

Animation and Icon properties are always set with one of the above values based on what we are trying to convey to the User. By default, the OK button will appear. If any other Button or Buttons Group is required, then the Button Property must be set to one of the above values. The Balloon Type is used with Labels Property only. We will look into the Labels property in the latter part of this Article.

I am sure when you go through these simple, straightforward examples you will be better informed about the usage of Office Assistant, and its methods and will start using them in your Applications.

I will reproduce the Code here with simple changes, which we have seen in last week's article, and go through it before we make changes in them for our new examples.

Public Sub MyMsgBox()
Dim strMsg As String
Dim strTitle As String

strTitle = "Assistant Test"
strMsg = "Wecome to MS-Access Tips and Tricks"

With Assistant.NewBalloon
    .Icon = msoIconAlertInfo
    .Animation = msoAnimationGetAttentionMajor
    .Heading = strTitle
    .text = strMsg
    .Show
End With

End Sub

Use the Above Code for a Demo Run

Press Alt+F11 to display the VBA Editing Window. Select the Module option from Insert Menu to create a new Standard VBA Module. Copy and Paste the above Code into the Module. Click somewhere in the middle of the Code and press F5 to run it.

In the above example, the Show() method displays the Message Box after setting the other Property Values. The Text property value shows the body text of the Message Box and Heading is the Title Text in Bold letters. The Animation property can be set with one of the 35 different options given above. The Icon Property value we have changed to Information type here.

If we want to obtain the responses of the User to do different things then we need to bring the Button Property into the Code. Let us say if the User needs to proceed with the report preparation process then she must click the OK Button otherwise Cancel Button. To evaluate the response received from the User and to take action accordingly we must write code for that further down in the Routine.

Modified VBA Code

Let us see how we can do this with changes to the above Code. The modified program is given below:

Public Sub MyMsgBox ()
Dim strMsg As String
Dim strTitle As String
Dim R As Long

Title = "Assistant Test"
msgTxt = "Proceess Weekly Reports...?"

With Assistant.NewBalloon
.Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMajor 
 .Button = msoButtonSetOkCancel
    .Heading = strTitle
    .text = strMsg 
 R = .Show
End With

If R = -1 Then
'User Clicked OK Button
    DoCmd.RunMacro "ReportProcess"
End If
End Sub

Compare the changes made in the new Code with the earlier one to find the difference.

If the user clicked the OK Button (-1 is returned in Variable R, Cancel=-2) then the Report Process Macro is run otherwise the Program ends doing nothing.

We can give different choices to the User in the form of a Menu with the use of Labels Property and perform the actions based on the User's responsibility. See the example code given below:

MsgBox Based Menu Choices.

Public Sub Choices()
Dim R As Long
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print."
    .labels(3).text = "Pivot Chart."
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of " & .labels.Count & " Choices?"
    R = .Show
End With

Select Case R
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select

End Sub 

 Note: You may copy and paste the code into the VBA Module and modify it with the appropriate changes before attempting to Run it.

The dimension of the Labels() Property Value can be up to a maximum of 5 only. The Balloon Type Value msoBalloon Type Buttons allow the User to click on one of the Options to select it and the Index value of the item clicked is returned in Variable R. We have set the Value Button = msoButtonSetNone to remove the OK Button from appearing so that the User will click only on one of the Options displayed.

Balloon Type Bullets and Numbers.

There are two more Balloon Type Property values available: msoBalloonTypeBullets and msoBalloonTypeNumbers. These are not selectable, like msoBalloon Type Buttons, and are used only for displaying information.

Compare the following Code with the earlier one to see the difference in changed Property Values.

Public Sub InfoDisplay()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Reminder"
    .Icon = msoIconAlertInfo
    .labels(1).text = "MIS Reports."
    .labels(2).text = "Trial Balance."
    .labels(3).text = "Balance Sheet."
    .BalloonType = msoBalloonTypeBullets
    .text = "Monthly Reports for User Departments"
    .Button = msoButtonSetOK
    .Show
End With

Sample Images with Balloon Type Property Changes are given below:

We will continue this discussion next week to see more ways to use the Office Assistant with Labels and CheckBoxes.

• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color
• MS-Access and Reference Library

Read More

Color and Picture in Message Box

Introduction - Access2003

Image of a Microsoft Access Message Box, with the use of Office Assistant, is shown below. The Message Box pops up after the User changes the Appointment Date field value with a new date and the Message Alert asks the User to reconfirm whether she really needs to replace the earlier Date 20/07/2009 with the new value 25/07/2009 or not.

The Field Name text is underlined and field values (old and new) are displayed in different colors. Company-Logo is displayed above the message text.

A Quick Demo

Want to find out quickly how this works?

1. Copy the following VBA Code into a Standard Module in your Database:
   

   Public Function MyAssistant()
   Dim msg As String
   Dim AppointmentDt As Date
   Dim AppointmentDt2 As Date
   Dim logo As String
   
   AppointmentDt = #7/20/2009#
   AppointmentDt2 = #7/25/2009#
   logo = "{bmp D:\Images\CoLogo.bmp}"
   
   msg = logo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & AppointmentDt & "{cf 0}" & vbCr & vbCr
   msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " & AppointmentDt2 & "{cf 0}...?"
   
   With Assistant.NewBalloon
       .Icon = msoIconAlertQuery
       .Animation = msoAnimationGetAttentionMinor
       .Heading = "Appointment Date"
       .text = msg
       .Show
   End With
   
   End Function

2. If you have a small Bitmap File (bmp) somewhere on your machine, then change the path in this line logo = "{bmp D:\Images\CoLogo.bmp}" of the Code to your .bmp image location.
3. See that you have linked the Microsoft Office Object Library File to your Database. If you are not sure how to do it, then do the following:
   • Press Alt+F11 to display the VBA Editing Window (or Tools - - >Macro - - >Visual Basic Editor), if it is not already visible.
   • Select References from Tools Menu.
   • Find Microsoft Office 11.0 Object Library (or whatever Version available) in the Available List and put a check-mark to select it.
   • Click OK to close the Dialog Box.
4. Click somewhere in the middle of the above VBA Code you have pasted into your Database.
5. Press F5 Key to Run the Code. You will find the above Message Box, with Office Assistant displaying your Bitmap Image above the message text.

The Enhancement Makes the Difference.

One important point to note here is that the visibility of the actual data displayed within the message text with underline and Color. It catches the eye of the User quickly and looks a lot better than showing everything in one Color.

Note: Readers who have not yet learned how to use the Office Assistant with MS-Access Message Boxes may read the following Post and copy the Programs presented there in your Library Database or Project. You can download a sample Database with the Code from there too.

Message Box using the Office Assistant.

You may also go through the following Posts to learn more interesting ways to use the Office Assistant with Message Boxes:

1. Message Box with Options Menu
2. Office Assistant with Check-Box Menu
3. Selection of Office Assistant

The AppointmentDt_LostFocus() Event Procedure that displays the above Message Box is given below:

Private Sub AppointmentDt_LostFocus()
Dim msg As String

If Me![AppointmentDt].OldValue <> Me![AppointmentDt].Value Then
    msg = AsstLogo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & Me![AppointmentDt].OldValue & "{cf 0}" & vbCr & vbCr
    msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " &  Me![AppointmentDt].Value & "{cf 0}...?"

    If MsgYN(msg, "AppointmentDt_LostFocus()") = vbNo Then
        Me![AppointmentDt].Value = Me![AppointmentDt].OldValue
    End If
    Me.Refresh
End If

End Sub

Text Formatting Codes

If you check the above message text formatted and stored in the String Variable msg you can see that it uses certain code values within {} (curly brackets) embedded in the Message String to format the Underline and Color of the text. Old and New Values for the AppointmentDt field are joined with the message text to show them in color.

At the beginning of the message text, I have used the word AsstLogo to add the Company Logo above the Message Text. I will explain this after we check the other codes and usage.

The MsgYN() User Defined Function we have created for displaying Message Box with Yes and No Option Buttons with the Office Assistant and learned about other options in the earlier Articles mentioned above.

There are only two types of Codes used within curly brackets to format the message text:

{ul } ' stands for Underline
{cf } ' stands for Color Format and used with 16 different Color Numbers.

{ul 1} turns On the underline and {ul 0} turns it Off.

{cf 0} (Black Color) is used to change the color of the text to normal message color.

Example-1: MsgOK "Hi {ul 1}Michael{ul 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

Example-2: MsgOK "Hi {cf 250}{ul 1}Michael{ul 0}{cf 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

You may copy the text string part of the above examples into the first program given above and try them out to find out.

You can use any of the following 16 Color Numbers in the {cf } format string:

Color Codes:

• Black: 0
• Dark Red: 1
• Dark Green: 2
• Dark Yellow: 3
• Dark Blue: 4
• Dark Magenta: 5
• Dark Cyan: 6
• Light Gray: 7
• Medium Gray: 248
• Red: 249
• Green: 250
• Yellow: 251
• Blue: 252
• Magenta: 253
• Cyan: 254
• White: 255

The only difficulty you may experience while formatting the message text is to type all the codes in curly brackets and it is likely that you may make errors in balancing the opening or closing brackets. In such cases, part of the message text or the field values added to it will not appear in the Message Box when displayed.

There is an easy way out of this problem. Define all the above Color Codes as Global Constants in VBA Standard Module and use the Constants wherever we need them in the message text. You can declare the Color Values as Constants in a Standard Module like the following:

Declaring Global Constants.

Constant declaration Examples:

Public Const AsstUlOn as String = "{ul 1}"

Public Const AsstUlOff as String = "{ul 0}"

Public Const AsstGreen as String = "{cf 250}"

Public Const AsstBlack as String = "{cf 0}"

Then we can re-write the example-2 Code above.

Example-2:

MsgOK "Hi " & AsstGreen & AsstUlOn & "Michael" & AsstUlOff & AsstBlack & ", Welcome to Tips and Tricks. "

No curly brackets or color codes to memorize, if you know the color name, then you can format the message quickly the way you want it.

Valid Image Formats

To display Company Logo above the Message Text (or below if you like) you can use two types of Images, Bitmap Image (bmp) or Windows Meta File (wmf) Image.

Bitmap Image usage: {bmp ImagePath}

Windows Meta File usage: {WMF ImagePath sizing_factor}

The sizing_factor determines the width of the wmf file displayed and is omitted for Bitmap Files. You can use wmf Image without the sizing_factor Value as well.

The complete Global Constant declarations for the above Color values are given below. You may copy them into the Standard Module of your Common Library Database to use across your Applications on the Network or into one of your Databases to try them out before implementing it in your other Applications.

Text Formatting Constant Declarations

Public Const AsstLogo As String = "{bmp D:\Images\CoLogo}"
Public Const AsstUlon As String = "{ul 1}"
Public Const AsstUloff As String = "{ul 0}"
Public Const AsstBlack As String = "{cf 0}"
Public Const AsstDarkRed As String = "{cf 1}"
Public Const AsstDarkGreen As String = "{cf 2}"
Public Const AsstDarkYellow As String = "{cf 3}"
Public Const AsstDarkBlue As String = "{cf 4}"
Public Const AsstDarkMagenta As String = "{cf 5}"
Public Const AsstDarkCyan As String = "{cf 6}"
Public Const AsstLightGray As String = "{cf 7}"
Public Const AsstMediumGray As String = "{cf 248}"
Public Const AsstRed As String = "{cf 249}"
Public Const AsstGreen As String = "{cf 250}"
Public Const AsstYellow As String = "{cf 251}"
Public Const AsstBlue As String = "{cf 252}"
Public Const AsstMagenta As String = "{cf 253}"
Public Const AsstCyan As String = "{cf 254}"
Public Const AsstWhite As String = "{cf 255}"

You may modify the Image Path on the first line to point it to the Bitmap Image on the Network's common folder accessible to your Application Users so that all your MS-Access Applications on the Network can use this image.

Network Server Location Mapping

Use the Image Path Name in UNC format (like \\ServerName\FolderName\ImageName.bmp) so that if the Disk Drive mapping is changed to a different letter like K: or J: etc., the programs will not lose contact with the image on the Network Path.

Instead of adding the AsstLogo Constant along with the message text every time like:

Msg = AsstLogo & vbCr & "Hi Michael,. . . " Is better if you add it to the main program code so that it is used automatically for all the messages before they are displayed. See the modified Code segment on the main program given above:

Public Function MyAssistant()
With Assistant.NewBalloon
    .Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMinor
    .Heading = "Appointment Date"
 .text = AsstLogo & vbCr & msg
    .Show
End With
End Function

Modify the above Code segment of the Main Program you have copied from the earlier Article: Message Box uses Office Assistant to use the Company Logo in all your Message Boxes.

Displaying Greetings on Special Occasions.

During special occasions like Christmas or Valentine's Day, you can replace the Company Logo Image with other images suitable to these occasions to give surprises to your Application Users.

Check the sample Message Boxes with Christmas Images:

Or the Image of King of Pop Music:

Note: This feature works in Access 2003 or earlier versions of Access only.

Image Source: Brittanica Encyclopedia.

• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color
• MS-Access and Reference Library
• InputBox and Simple Menus

Read More

Microsoft Excel Power in MS-Access

Introduction.

There are times that Microsoft Access Application Users ask for Report Data exported into Excel so that they can work on it and do their own customized analysis. In this case, we can create a Macro with the Macro Command OutputTo or Visual Basic Docmd to export the Data from a Table or Query and open the Data in Excel automatically.

The sample Macro Command and Parameter setting will look like the image given below:

The same action in Visual Basic Code is given below:

Function xPort2XL()
    DoCmd.OutputTo acTable, "Products", "MicrosoftExcelBiff8(*.xls)", "C:\My Documents\Products.xls", True, "", 0
End Function

Both can be run from a Command Button Click on the Main Switch Board.

But, if the User is able to do whatever he/she does in Microsoft Excel, like writing expressions for Calculations, Formatting, Chart Preparations, Printing, and so on from within Microsoft Access itself that will be a different experience altogether, right? They can even save a copy of whatever they do from within Access as an independent Excel Workbook outside MS-Access if needed.

The Design Task.

Only a few things are involved to implement it in your Projects.

1. Open a new Form in Design View in your Database.
2. Expand the Detail Section of the Form large enough for the size of the Worksheet you would like to display on the Form.
3. Display the ToolBox (View - - > Toolbars - - > Control ToolBox), if it is not visible.
4. Select the Control Wizards Tool Button (the button with a magic wand icon) to activate it.
5. Select the Unbound Object Frame Tool from the ToolBox.
6. Draw a rectangle large enough to accommodate the size of the Worksheet you need to leave about a quarter of an inch space on all four sides of the Form. A sample image is given below for reference:
   

   This action will display a list of Objects that can be inserted into the Unbound Object Frame.

7. See that the Create New Radio Button is selected and Select Microsoft Excel Worksheet from the displayed Object Type List and click the OK Command Button.

   An Excel Worksheet will be inserted into the Unbound Object Frame area on the Form. 

   If you look at the Menus and Toolbars above, you can see that all of them are changed into Microsoft Excel Menus and Toolbars now. You can see the Formula Bar and the active Cell Address to the left of the Formula Bar. You can type some expression in the Formula Bar to save it in the active cell if you want to try now.

   But, we are now in the Design View of the Form. First, we must save the Form with an appropriate name and open the Form in Normal View for use.

   Unlocking Worksheet for Normal Use

   Before saving the Form we must change a few Property Values of the Unbound Object Frame (the housing frame of the Worksheet Object) that we have drawn initially. The Unbound Object Frame will be in a Disabled and Locked state in a normal view of the Form. These properties we must modify to work with the Worksheet in the normal view of the Form.

8. Click outside the Worksheet on the Detail Section of the Form to deactivate the Worksheet.

   The Unbound Object Frame with the Worksheet will be in a selected state (if it is not, then click on it again to select it) showing the sizing controls on all four sides and corners. Now the Menus and Toolbars changed back to MS-Access.

9. Select View - - >Properties to display the Property Sheet of the Unbound Object Frame and change the following Property Values as shown below:
   • Enabled = Yes
   • Locked = No
10. Close the Property Sheet.

    NB: You may modify the look of your Form with a Title on the Header Section of the Form and change the Property settings of the Form to remove the Record Selector, Navigation Buttons,  Dividing Lines, etc. Add a Command Button at the Form Footer and attach a Macro to Close the Form.

11. Select Close from File Menu and save the Form with the name XlWorkBook.
    
    

    Preparing Worksheet for Normal Operations.

12. Open the XlWorkBook Form in Normal View.

    When you open the Form the Unbound Object Frame will be in the selected state showing the Excel Worksheet Grid and the sizing controls, as we have seen them earlier in Design View.

13. Right-Click somewhere on the Control to display the Shortcut Menu.
14. Select Edit from the Worksheet Object Option displayed. You can Double-Click on the Unbound Object Frame to get the same result.

    The Worksheet becomes active now, Menus and Toolbars now changed to Excel and the Formula-Bar is showing up above the Form.

    A Few Issues with the Worksheet which need correction.

    I know you don't feel comfortable working with this Worksheet because you have only a limited area of the worksheet available, or the Scrollbars are not showing up properly to move to a wider area of the Worksheet or the Worksheet Tab is not visible, etc. But, these are all temporary issues and can be solved in no time.

    Before going on to that how do we bring the data from Access Table or Query into this Worksheet. You cannot link a Table of this Database to the Worksheet and you cannot import the contents of a Table or Query into this worksheet either. There is an option Import Data in Data Menu for Excel to get external data through ODBC Connection, but this will not work for bringing data from the same database or cannot be recommended for Users.

    There is only one easy way that I could find, that is open the Table or Query in Datasheet View, Copy the required portion (Rows or Columns) or whole contents to the Clipboard, and Paste it into the Worksheet.

15. Minimize the xlWorkBook Form and the Access Menu is back.
16. Open a Table or Query in Datasheet View (this can be done through a macro for Users).
17. Click on the left top corner of Rows and Columns to highlight the entire Table or Query contents (or select a few rows or Columns).
18. Select Copy from Edit Menu.
19. Minimize the Datasheet View and Maximize the xlWorkBook Form.
20. Click on the left top corner of the Worksheet area where you would like to paste the data into.
21. Select Paste from Edit Menu. The copied contents will be pasted on to the Worksheet.

    You can close and open the Form again and the data on the Form will have all the changes that you have made before closing it.

    NB: The normal Excel Paste Special. . . Menu Options are not available here, but you can get it when the Excel Workbook is open in a different mode.

    You can work with the data as you will do in a worksheet, to add another Worksheet, write a formula, do calculations, create Charts, and so on. There are certain things that you cannot do here (in this state of the Form) like mark an area of the worksheet as Print Area or do Page Setup changes or Print Preview of the Worksheet. That doesn't mean that we cannot do these things at all.

    Worksheet EDIT or OPEN Mode

    These drawbacks can be solved by opening the Worksheet in a different Mode.

22. Click on the Form's detail Section outside the Unbound Object Frame to deactivate the Worksheet.
23. Right-Click on the Unbound Object Frame and select Open (earlier we have selected Edit) from the Worksheet Object Menu.

The Worksheet will be opened in a normal Microsoft Excel Application Window and you can do whatever analysis you want to do here. You may add more Worksheets in the workbook, write the formula, create Charts, and so on.

Here, you can mark a selected area of the Worksheet as Print Area, change Page Setup, Print Preview, or Print the Worksheet. The normal Paste Special menu options are available here.

You can update these changes on the MS-Access Form by selecting the Update option on the File Menu. You don't have to search for the Excel File on disk later. You can even save a copy of this Workbook as a regular Workbook by selecting the Options Save Copy As from File Menu if needed.

When your work is complete in Excel, select the option Close & Return to xlWorkBook: Form to come back to MS-Access Form, saving whatever work you have done with it.

Isn't it something different to work with Excel from within MS-Access?

1. Roundup Function
   of Excel in MS-Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy Paste Data From Excel to Access2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening Excel Database Directly
12. Create Excel, Word Files from Access

Read More