Office Assistant And Msgbox Menus-2

Continued from Last Week. - Access 2003.

This article is a continuation of last week’s post, “Office Assistant and MsgBox Menus.” If you haven’t read that yet, I recommend going through it first before proceeding with this one.

Last week, we learned how to use the Office Assistant for MsgBox simply 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 Properties in three different ways by setting the Values of the 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 visual styles, but they are not interactive—users cannot select them. These types are therefore best suited for situations where you only need to present information, rather than receive user input.

Let’s take a closer look at the overall behavior of the Balloon Object when displaying a message box through the Office Assistant, so we can better understand how it works.

If you revisit the earlier examples, you’ll notice that when a message box created with the Office Assistant’s Balloon Object is displayed, you cannot interact with any other objects in the database. You must dismiss the Balloon first by responding to one of its options—such as clicking OK or Cancel—before you can continue.

This behavior is identical to that of the standard MsgBox() function in MS Access. However, the Office Assistant provides ways to overcome this restriction.

When a Balloon is displayed, you cannot, for instance, open a Form or Report to verify some information before clicking Yes or No, because the Mode property of the Balloon Object is set to its default value, msoModeModal.

The Mode Property of Message Balloon

I have intentionally not introduced the Mode property in the earlier examples to avoid overwhelming you with too many details at once. Now that you’re familiar with the basics, it will be easier to understand the additional features associated with it.

As you’ve already seen, you can create stylish Message Boxes using the Balloon Object of the Office Assistant with just a few lines of VBA code—without even using this property.

The Mode property can be assigned one of three values, each controlling how the Balloon Object behaves when displayed.

Mode = msoModeModal (default).

This setting forces the user to respond to the Balloon before performing any other action—just like a standard MsgBox in MS Access. The user must click one of the available buttons (such as OK or Cancel) to dismiss the Balloon before continuing with any other task.

Mode = msoModeModeless.

This setting allows the user to access other database objects while the Balloon remains active. However, it also requires the use of another property—Callback—to trigger a separate subroutine that can handle user interactions, evaluate selections or choices, perform the necessary actions, and finally close the Balloon object.

Mode = msoModeAutoDown.

This setting automatically dismisses the Balloon (MsgBox) if you click anywhere else, effectively ignoring it.

The purpose and behavior of the first and last Mode property values are straightforward. However, the second value: msoModeModeless, requires special handling. When this mode is used, the Callback property must be set to the name of a valid subroutine; otherwise, the Balloon will fail to function properly. Leaving the Callback property empty (an empty string) will also cause an error.

Let’s now modify our earlier program to demonstrate how to use the Mode property set to msoModeModeless, along with the Callback property, to handle user selections from a menu displayed in the Balloon.

The Mode and CallBack Property.

The Modified Code with Mode and CallBack Property Settings and the sample code for the required Subroutine 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 Subroutine name myProcess. When the user clicks on one of the Options from the displayed MsgBox, the MyProcess() Subroutine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Subroutine 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 a 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.

 lbtn  The 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 Subroutine, 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.

  bln.close  The 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 a 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 explored several techniques to use the Office Assistant for displaying message boxes with formatted text. In this article, we will move beyond formatting. I believe many readers would now like to learn how this simple feature can be used to gather responses from users — allowing them to choose from multiple options and trigger different actions based on their selections.

I have already discussed this concept earlier by creating MsgOK(), MsgYN(), and MsgOKCL() Functions, and others were created using the Office Assistant. These functions can be called from anywhere within your application — just like the built-in MsgBox() function — using only the message text, or both the message text and title as parameters.

My intention in designing these functions was to simplify the use of the Office Assistant’s Balloon object, which otherwise requires several property values to be manually set for each display.

However, during this simplification process, I’ve found that many readers may not fully understand the underlying method used within these functions.

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 article, Color and Picture in Message Box, we explored several property values of the Balloon object of the Office Assistant that can be assigned before displaying a message box.

In this session, we will work directly with these properties so that you can clearly understand how each one functions. This approach will make it easier to grasp their behavior and usage, rather than passing values for them indirectly through the parameter list of a function definition.

Message Balloon Properties

The following are some of these properties:

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

Microsoft Office Object Library

Note:
If you have not yet attached the Microsoft Office Object Library to your database, please do so before trying out the examples given here. Follow the steps below:

1. Press Alt + F11 to open the VBA Code Window (or go to Tools → Macro → Visual Basic Editor).

2. From the Tools menu, select References.

3. In the Available References list, locate Microsoft Office Object Library and place a check mark beside it.

4. Click OK to close the dialog box.

Message Balloon Animation Constants

The Animation property alone offers about thirty-five different choices, all defined as constants in the Microsoft Office Object Library. For reference, the constant values for the Balloon properties — Animation, Button, Icon, and Balloon Type — are listed below.

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

The Animation and Icon properties are always set using one of the values listed above, depending on the message you want to convey to the User. By default, the OK button appears. If you need a different button or a group of buttons, the Button property must be set to one of the available values. The Balloon Type property is used in conjunction with the Labels property, which will be covered later in this article.

By going through these simple and straightforward examples, you will gain a better understanding of the Office Assistant, its methods, and how to incorporate them into your Applications.

We will now reproduce the code from last week’s article with minor adjustments and review it before applying changes 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 the 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 all the other property values have been set. The Text property defines the main body text of the Message Box, while the Heading property sets the title text in bold. The Animation property can be assigned one of the 35 options listed above, and we have set the Icon property to the Information type.

To obtain the User’s response and take different actions accordingly, we need to use the Button property in the code. For example, if the User must proceed with the report preparation process, they would click the OK button; otherwise, they might click Cancel. We can then evaluate the response returned by the Balloon Object and write further code in the routine to handle each scenario appropriately.

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 version to identify the differences.

If the user clicks the OK button (which returns -1 in the variable R; Cancel returns -2, when the Report Process Macro is executed. Otherwise, the program ends without performing any action.

We can also present different choices to the user as a menu using the Labels property and execute actions based on the user’s selection. An example of such code is provided 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 Labels() property can hold a maximum of 5 items. Using the Balloon Type value: msoBalloonTypeButtons The user can click on one of the options to make a selection. The index of the clicked item is returned in the variable R.

In this example, we set Button = msoButtonSetNone to remove the default OK button, ensuring that the user must select one of the options displayed.

Balloon Type Bullets and Numbers.

Two additional Balloon Type property values are available: msoBalloonTypeBullets and msoBalloonTypeNumbers. Unlike msoBalloonTypeButtons These are not selectable by the user and are intended 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 explore 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 - Access 2003

The image below shows a Microsoft Access Message Box displayed using the Office Assistant. This Message Box appears after the user changes the Appointment Date field to a new value. The alert prompts the user to reconfirm whether they really want to replace the previous date, 20/07/2009, with the date 25/07/2009.

The field name is underlined, and the old and new values are displayed in different colors. Additionally, the company logo appears 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 the 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 the 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 is underlined and colored. It catches the user's eye 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 a 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 is 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 the 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 can copy the text string part of the above examples into the first program 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 main challenge when formatting the message text is typing all the codes within curly brackets correctly, as it’s easy to make mistakes in balancing the opening and closing brackets. If this happens, part of the message text or the inserted field values may not appear in the Message Box.

A simple solution is to define all the color codes as Global Constants in a standard VBA module and use these constants wherever needed in the message text. You can declare the color values as constants in a standard module like this:

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 rewrite 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 the Company Logo above the Message Text (or below if you like), you can use two types of Images, a Bitmap Image (bmp) or a 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 the wmf Image without the sizing_factor value.

The complete Global Constant declarations for the above color values are provided below. You can copy them into a standard module in your Common Library Database to use across multiple applications on your network, or into a single database to test them before implementing them in 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 can modify the image path on the first line to point to a bitmap image stored in a network’s common folder accessible to all your application users. This allows all your MS-Access applications on the network to use the same 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,... " It 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, such as Christmas or Valentine's Day, you can replace the company logo with themed images to delight and surprise your application users.

Check the sample Message Boxes with Christmas Images:

Or the Image of the King of Pop Music:

Note: These features work only in Access 2003 or earlier versions.

Image Source: Britannica 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.

Sometimes, Microsoft Access users need report data exported to Excel for further analysis and custom work. In such cases, you can create a macro using the OutputTo command or the VBA DoCmd.OutputTo method to export data from a Table or Query and automatically open it in Excel.

The sample Macro command and parameter settings are illustrated in the image below:

Right-click to open Large Image in New Tab/Window.

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.

Imagine if users could perform all the tasks they normally do in Microsoft Excel—such as creating formulas, formatting cells, preparing charts, printing reports, and more—directly from within Microsoft Access. That would provide a completely different and seamless experience. They could even save a copy of their work as an independent Excel workbook outside Access whenever 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. Leave about a quarter of an inch space on all four sides of the Form. A sample image is given below for reference:

   

   Right-click to open Large Image in New Tab/Window.

   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 have been 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. 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 then 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 were drawn initially. The Unbound Object Frame will be in a Disabled and Locked state in the normal view of the Form. We must modify these properties 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, click on it again to select it), showing the sizing controls on all four sides and corners. Now the Menus and Toolbars have 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 the 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 saw 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 are now changed to Excel, and the Formula-Bar is showing up above the Form.

    A Few Issues with the Worksheet need correction.

    I understand that working with this worksheet may feel uncomfortable because only a limited area is visible, the scrollbars may not appear correctly, or the worksheet tabs are hidden. Don’t worry—these are all temporary issues that can be fixed quickly.

    Before addressing that, you might wonder how to bring data from an Access table or query into this worksheet. You cannot link a table from the same database directly, nor can you use Excel’s Import Data option via ODBC for this purpose—it’s either ineffective or not recommended for users.

    The simplest solution is to open the table or query in Datasheet View, select the rows, columns, or the entire dataset you need, copy it to the clipboard, and paste it directly 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 the Edit Menu.

19. Minimize the Datasheet View and Maximize the xlWorkBook Form.

20. Click on the top left corner of the Worksheet area where you would like to paste the data.

21. Select Paste from the Edit Menu. The copied contents will be pasted onto the Worksheet.

    You can close and reopen the Form, and all the changes you made earlier will be preserved and displayed.

    NB: The normal Excel Paste Special... Menu Options are not available here, but you can get them 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 had selected Edit) from the Worksheet Object Menu.

The Worksheet will open in a standard Microsoft Excel window, allowing you to perform any special analysis. You can add additional worksheets, write formulas, create charts, and more.

You can select specific areas of the worksheet as a Print Area, adjust Page Setup, use Print Preview, or print the worksheet. All standard Paste Special options are available for use.

Any changes you make can be updated directly on the MS-Access Form by selecting the Update option from the File menu. There’s no need to locate the Excel file on your disk. If desired, you can also save a copy of the workbook as a regular Excel file using Save Copy As from the File menu.

Once your work is complete, select Close & Return to xlWorkBook: Form to return to the MS-Access Form, ensuring that all your changes are saved.

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 Access 2007
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 an Excel Database Directly
12. Create Excel, Word Files from Access

Read More