 上传我的文档
 下载
 收藏
 下载此文档
正在努力加载中...
首发Statistics for Managers Using Microsoft Excel 习题答案 I
下载积分：488
内容提示：首发Statistics for Managers Using Microsoft Excel 习题答案 I
文档格式：PDF|
浏览次数：44|
上传日期： 16:41:57|
文档星级：
全文阅读已结束，如果下载本文需要使用
 488 积分
下载此文档
该用户还上传了这些文档
首发Statistics for Managers Using Microsoft Excel
官方公共微信苹果/安卓/wp
积分 498, 距离下一级还需 302 积分
权限: 自定义头衔, 签名中使用图片
道具: 彩虹炫, 涂鸦板, 雷达卡, 热点灯, 金钱卡, 显身卡, 匿名卡, 抢沙发下一级可获得
权限: 隐身
购买后可立即获得
权限: 隐身
道具: 金钱卡, 彩虹炫, 雷达卡, 热点灯, 涂鸦板
想求t-分布跟GED分布的分位数，已知概率跟自由度，请问该怎么求？
载入中......
找excel的书看看吧
古墓派预测分支
本帖最后由 coral033 于
11:16 编辑 marburg 发表于
想求t-分布跟GED分布的分位数，已知概率跟自由度，请问该怎么求？TDIST
Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.
TDIST(x,degrees_freedom,tails)
X& &is the numeric value at which to evaluate the distribution.
Degrees_freedom& &is an integer indicating the number of degrees of freedom.
Tails& &specifies the number of distribution tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails = 2, TDIST returns the two-tailed distribution.
If any argument is nonnumeric, TDIST returns the #VALUE! error value.If degrees_freedom & 1, TDIST returns the #NUM! error value.The degrees_freedom and tails arguments are truncated to integers.If tails is any value other than 1 or 2, TDIST returns the #NUM! error value.If x & 0, then TDIST returns the #NUM! error value.If tails = 1, TDIST is calculated as TDIST = P( X&x ), where X is a random variable that follows the t-distribution. If tails = 2, TDIST is calculated as TDIST = P(|X| & x) = P(X & x or X & -x).Since x & 0 is not allowed, to use TDIST when x & 0, note that TDIST(-x,df,1) = 1 – TDIST(x,df,1) = P(X & -x) and TDIST(-x,df,2) = TDIST(x df,2) = P(|X| & x).Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
Create a blank workbook or worksheet.Select the example in the Help topic. Note&&&&Do not select the row or column headers.
Selecting an example from HelpPress CTRL+C.In the worksheet, select cell A1, and press CTRL+V.To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
&&123ABDataDescription1.Value at which to evaluate the distribution60Degrees of freedomFormulaDescription (Result)=TDIST(A2,A3,2)Two-tailed distribution (0., or 5.46 percent)=TDIST(A2,A3,1)One-tailed distribution (0. or 2.73 percent)
Note&&&&To view the number as a percentage, select the cell, and then on the Sheet tab, in the Number group, click Percent Style .
one-tailed distribution return p-value for a data set t distribution tdist tdist function tdist worksheet function two-tailed distribution what function returns the student's t distribution? xl
Out of difficulties, makes miracles.
什么乱七八糟的
无限扩大经管职场人脉圈！每天抽选10位免费名额，现在就扫& 论坛VIP& 贵宾会员& 可免费加入
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
如有投资本站或合作意向，请联系（010-）；
邮箱：service@pinggu.org
投诉或不良信息处理：（010-）
京ICP证090565号
论坛法律顾问：王进律师VBA Tips: A Pop-up Calendar for Excel
| & &&& |&&&
|&&&&&&|&&
This tutorial is suitable for Excel versions 2007 and 2010.
The screenshots show Excel 2010 on Windows 7.
A Pop-up Calendar for Excel
Suitable for: Excel
One of the biggest problems in maintaining &good& data is the
entry of dates. People seem to get confused about entering dates.
Should they enter dd/mm/yy or mm/dd/yy? Do they enter slashes or
dashes or dots? And what was the date of the third Thursday in
September last year anyway? What you really need is a calendar!
NOTE: When I first wrote this tutorial I made use of the
Microsoft Calendar Control, an ActiveX control that was installed
along with Microsoft Office Professional (i.e. the version that
included Microsoft Access). If you didn't have that version of
Microsoft Office you could still download and install a copy of the
ActiveX control. If you want to do that you can follow the original
tutorial . Since then Microsoft have created an alternative to
the Calendar Control called the MonthView control. This new ActiveX
control is similar in function to the Calendar Control and has the
advantage that it is installed with all versions of Microsoft
Office. In Microsoft Office 2007 you have the choice of which
ActiveX control to use but in Microsoft Office 2010 you must
use the MonthView control as described in this tutorial.
The tutorial shows you how to create a pop-up calendar using the
Microsoft MonthView control that is installed with Excel. You will
use the Visual Basic Editor to create a UserForm that displays a
calendar. You will also write some VBA code to power the UserForm
and to generate an additional item on the menu that appears when the
user right-clicks a cell on an Excel worksheet. Clicking the menu
item will display the calendar. When the user selects a date it is
automatically entered into the active cell on the worksheet.
What Will the Calendar Do?
The MonthView control has a number of useful features. In its
standard format it displays a single month in calendar format (it
can be set to display more than one if required). Either side of the
month name is an arrow button which displays the previous or next
month when clicked. Clicking on the month name opens a list of
months so that you can quickly jump to a specific month. Clicking on
the year number reveals a spinner which lets you change the year.
The current date is always shown at the bottom of the calendar.
Click it to jump to today's date on the calendar.
Where Does the Code Go?
The pop-up calendar is created entirely with code and is
generated when you need it. The code that creates and operates the
calendar has to reside inside an Excel file. The question is which
one? If you want the pop-up calendar to be available whenever you
are working in Excel you should create it in Personal.xlsb,
sometimes referred to as your Personal Macro Workbook,
because this file exists to store code that you want to be available
to all your Excel files.
Personal.xlsb opens and is hidden each time Excel is started.
Any macros and functions it contains are then available for use in
any other workbook. To find out whether or not you already have a
copy of Personal.xlsb read the instructions in
further down the page.
However since the Personal Macro Workbook, as its name implies,
is specific to your copy of Excel it will reside either on your
computer's hard drive or within your personal profile on the
network. It isn't the best place to put the code if you want other
users to have access to the pop-up calendar. Instead, you could
create your pop-up calendar in a specific workbook. It would be
available whenever (and only when) that workbook was open, but also
available to any other workbook that was open at the same time. A
pop-up calendar created in an Excel template would be present in
each workbook that was generated from the template. The most
flexible option is to create an Excel Add-In. It's easy to do
and will allow you to distribute your calendar to other users. You
start by building the calendar exactly as described in this tutorial
but in a new Excel workbook (not Personal.xlsb) which you
then convert to an Excel Add-In. The
takes you through the steps of creating an Excel Add-In.
In this tutorial I will be using Personal.xlsb but if you
choose to put your pop-up calendar somewhere else you can still
follow the same instructions.
New to VBA?
If you plan to create and edit macros or work with VBA code you
will find it useful to enable the Developer tab. It offers quick
access to many of the tools you will be using during your projects.
If it isn't currently visible in your copy of Excel you can enable
it by going to Excel Options.
In Excel 2007 click the Office
Button and choose Excel Options. Click the Popular
button, check the Show Developer tab in the Ribbon option
then click OK to return to Excel.
In Excel 2010 go to the File tab and choose Options and select the
Customize Ribbon section. On the right side of the window under
Customize the Ribbon make sure that Main Tabs is
selected from the dropdown then check the Developer option
and click OK to return to Excel.
If you have never recorded a macro on your current copy of Excel
then you probably won't have a copy of Personal.xlsb yet. If
a copy exists it will be visible in the Project Explorer
window of the Visual Basic Editor. If you don't have one it takes
just a moment to create: On the Developer tab click the
Record Macro button. When the Record Macro dialog appears
choose to store the macro in the Personal Macro Workbook then
click OK. Now click the Stop Recording button on the
Developer tab. You have just recorded an empty macro but that
action was sufficient to prompt Excel to create a copy of
Personal.xlsb to store it in. You can delete the macro later but
now you have somewhere to create your pop-up calendar.
Build the Calendar
The first step is to build the calendar. When you have done that
you will go on to write the VBA code that will make it work. If you
plan to create an Excel Add-In you should open a new empty workbook.
If you want your calendar to reside in a specific workbook then make
sure that workbook is open. In Excel open the Visual Basic Editor
using the keyboard shortcut
[ALT]+[F11] or click the Visual Basic button on the
Developer tab of the Ribbon.
IMPORTANT: Remember to save your work regularly. Since a
UserForm and its associated code resides within an Excel workbook
the they are saved when you save the workbook and vice-versa. You
can save from within Excel or from within the Visual Basic Editor
window in the usual way by clicking the Save button. Excel will warn
you if there are any unsaved changes when you close the workbook. If
you are working in a regular Excel workbook (and not in
Personal.xlsb) you should save your workbook as an Excel Macro
Enabled Workbook (*.xlsm). Failure to do this will result in
Excel discarding your code.
Step 1. Create a new UserForm
You are going to place the pop-up calendar on a UserForm which is
a kind of dialog box that you can build and program with VBA. Go to
the Project Explorer window of the Visual Basic Editor. The
Project Explorer is usually located in the upper left corner of the
Visual Basic Editor window. If you can't see it, switch it on from
the View menu. Right-click on the name of the workbook
in which you want to create the pop-up calendar then choose
Insert and UserForm.
A new empty UserForm will appear in the main window of the Visual
Basic Editor together with the Toolbox containing buttons for the
most commonly used objects (called controls) that can be
placed on a form. You can switch the Toolbox on and off from a
button on the toolbar. You will also notice that the Toolbox
disappears when the UserForm is not selected. If this happens just
click on the UserForm and the Toolbox will reappear.
Step 2. Rename and caption the UserForm
When the UserForm is selected the Properties Window of the
Visual Basic Editor displays a list of all the UserForm's
properties. The Properties Window is normally located in the lower
left corner of the Visual Basic Editor window. If you can't see it
switch it on from the View menu. Excel automatically names
and captions (where appropriate) new objects. You will see that the
UserForm has been given the name and caption UserForm1. It is
good practice to give objects more meaningful names so go to the
Properties Window and change the Name property to
frmCalendar and the Caption property to Pick a
IMPORTANT: If you choose to use different names from those
suggested here remember to modify any code that refers to these
objects by name.
You can test the Userform from the Visual Basic Editor now (and
at any stage) by pressing
[F5] on your keyboard or clicking the Run button on the
toolbar. Doing this opens the UserForm in Excel so that you can
check the progress of your design. Closing the UserForm (click the
[X] in its upper-right corner) returns you to the Visual
Basic Editor.
Step 3: Place a Close button on the UserForm
You might think the calendar doesn't need a Close button since
there is already one in the upper-right corner of the UserForm, and
our code will close the form automatically after a date has been chosen. But doing
so will add a useful feature that most users take for granted, that
is to close the calendar if they press the [Esc] key on their
Click the CommandButton button on the Toolbox then click
on the center of the UserForm. The dots on the UserForm represent a
grid to help you align objects neatly. Point at the dotted border of
the new command button and drag it so that it is located two grid
points down from the top and two grid points in from the left of the
Now use the Properties Window to change the command button's
Name property to cmdClose, change its Caption
property to Close and set its Cancel property
to True. When the Cancel property of a command
button is set to True the button gets clicked automatically
when the user presses the [Esc] key on their keyboard.
Step 4: Code and test the Close button
In this step you will attach some code to the Cancel
button so that when it is clicked the UserForm will close, thus
closing the pop-up calendar. In the Visual Basic Editor
double-click the Close command button. This opens the UserForm's
code window with an empty Event Procedure for the Click
event of the command button (an Event Procedure is a collection
of commands that are executed when a particular event happens - in
this case when the cmdClose button is clicked).
Between the Sub... and End Sub lines
enter the statement:
&&&&&&Unload Me
This instructs Excel to close the UserForm. The completed code
should look like this:
Private Sub
cmdClose_Click()
&&&Unload Me
Return to the UserForm design window by double-clicking its name
in the Project Explorer or by using the keyboard shortcut [CTRL]+[TAB]
to switch windows, then test the UserForm as before by pressing
[F5] or clicking the Run button on the toolbar. You
should be able to close the UserForm from Excel by either clicking
the Close button or by pressing the [ESC] key.
HOW THE CODE WORKS
The command Unload causes a UserForm to close and drop out of
memory (the command Hide can be used to be remove it
temporarily from view yet have it remain open in memory so that it
can be reopened without losing its data). The word Me is a
quick way of referring to the current UserForm without having to use
its full name. I could have written Unload frmCalendar. Since
the form is usually referred to frequently in the code using Me
simplifies code writing and saves a lot of editing if the UserForm's
name is ever changed.
Step 5: Locate the MonthView control
The MonthView control will provide the calendar that you
will place on the UserForm. It is not normally present on the
Toolbox so you will have to ask for it. Right-click on the
Toolbox and choose Additional Controls. Scroll down the list
in the Additional Controls dialog until you find
Microsoft MonthView Control 6.0 (the version number might be
different depending on your version of Microsoft Office) and check
the box next to its name, then click OK to close the dialog.
You will see that a button for the new control has been added to the
Step 6: Place a calendar on the UserForm and set its properties
Click the MonthView button on the Toolbox then click on
the UserForm to create a calendar on the form. Drag the calendar
into the upper left corner to the UserForm. The calendar will cover
the Close button you created earlier. This is intentional because
the Close button will be operated by the user pressing their
[ESC] key and it does not need to be visible.
When the MonthView control is selected the Properties Window
displays its properties. By default it has a sunken border. I don't
like this effect so I have changed the Appearance property to
0 - cc2Flat. There are many other properties you can
modify. The MonthColumns and MonthRows properties
allow you to display more than one month at a time. The ShowToday
property lets you choose whether or not to highlight and display the
current date.
Step 7: Resize the UserForm
The final step in designing the calendar is to change the size of
the UserForm to match the dimensions of the calendar. Click on the
background of the UserForm to select the form then use the resizing
handles (white rectangles located around the dotted border) to drag
the edges of the UserForm to the required size. Finally, run the
form to check that it looks OK.
Step 8: Write the code to enter a date
In this step you will create the code that writes a date on to
the worksheet when the user clicks one of the day buttons on the
calendar. Double-click the MonthView control to open the code
window. This opens the UserForm's code window with an empty Event
Procedure for the
DateClick event of the MonthView control.
You have a couple of choices now depending upon what you want to
happen when the user chooses a date. If you want the calendar to
enter the chosen date only in the active cell, which is the
selected cell when only a single cell is selected or only the active
cell if a block of cells or multiple cells are selected, then use
this method. Between the Sub... and End Sub
lines enter the statements:
&&&&&&On Error Resume Next
&&&&&&ActiveCell.Value = DateClicked
&&&&&&Unload Me
This instructs Excel to enter the chosen date into the currently
active cell and the close the UserForm. The completed code should
look like this:
Private Sub
MonthView1_DateClick(ByVal DateClicked
&&&On Error Resume Next
&&&ActiveCell.Value = DateClicked
&&&Unload Me
If you want the calendar to enter the chosen date in all the
currently selected cells, whether this is a block of cells, a
multiple selection, or just a single cell if only one is selected,
then use this method. This code is more versatile so I would
probably do it this way unless I had a particular reason not to do
so. Between the Sub... and End Sub lines enter the
statements:
&&&&&&On Error Resume Next
&&&&&&Dim cell As Object
&&&&&&For Each cell In Selection.Cells
&&&&&&&&&&&cell.Value = DateClicked
&&&&&&Next cell
&&&&&&Unload Me
This instructs Excel to write a date into each of the selected
cells. Your code should look like this:
Private Sub
MonthView1_DateClick(ByVal DateClicked
&&&On Error Resume Next
&&&Dim cell As Object
&&&For Each cell In
Selection.Cells
&&&&&&cell.Value = DateClicked
&&&Next cell
&&&Unload Me
Test the code by running the calendar as before, with one or
several cells selected. If it fails to work properly then check
your typing!
HOW THE CODE WORKS
Although this is a very simple operation, in both cases I have added
the statement On Error Resume Next which tells Excel to
ignore any error that might occur. This will prevent the code from
crashing if, for example, the selected cell or one of the cells in a
selection is locked. The code makes use of the DateClicked
parameter which is given the date chosen when the user clicks one of
the day buttons. In the first example that value is simply written
into the active cell before the UserForm is closed. In the second
example the code first declares a variable that represents a single
cell. It then employs a For...Next code loop to visit each
cell in the current selection, entering the value into each as it
goes, before finally closing the UserForm.
Step 9: Synchronize the calendar with the worksheet
I nearly didn't include this step, which is optional anyway,
because whilst it works perfectly on the old version of my pop-up
calendar it doesn't work completely as it should in this version.
The idea is that, if the active cell already contains a date, the
calendar opens to display that same date. I have been able to make
the calendar display the correct month and year but despite my best
efforts can't get it to highlight the specific day. The MonthView
control has a DayBold property which is &supposed& to be
controllable with code - you tell the calendar which day number to
display in bold and it does so - but in the current context I just
can't make it work. I've searched for a solution without success so
if you find one please let me know! Here's how to have the calendar
open at the correct year and month...
Open the UserForm's code window by right-clicking on its
name in the Project Explorer and choosing View Code or
pressing the [F7] key. At the top of the code window are two
drop-down lists. From the left-hand list choose UserForm the
from the right-hand list choose Initialize. This creates and
empty event procedure for the UserForm_Initialize event (if a
UserForm_Click event is also created you can delete it).
Between the Sub... and End Sub statements type:
&&&&&&If IsDate(ActiveCell.Value) Then
&&&&&&&&&&&&Me.MonthView1.Value = ActiveCell.Value
&&&&&&End If
Your completed code should look like this:
Private Sub
UserForm_Initialize()
&&&If IsDate(ActiveCell.Value)
&&&&&&Me.MonthView1.Value = ActiveCell.Value
Test the code in different scenarios: by opening the calendar when the active cell in
E when it contains something that is not a date (a
number or some text); and when it contains a date different from the
current date. If the active cell is empty, or contains text or a
number, the calendar should open to show the current date. If the
active cell already contains a date the calendar should open to show
the same year and month.
HOW THE CODE WORKS
The Initialize event happens as the UserForm opens. It is
used to prepare the form for use with such tasks as filling lists
and setting starting values for textboxes. Here it is being used set
the initial value of the MonthView control. The code uses the
IsDate function to check whether or not the active cell
contains a date. This is used as the condition for an If
Statement so that, if the function returns True, the
value of the calendar is set to the same date. If the function
returns False then no date was found so nothing changes and
the calendar opens with its default date.
Step 10: Write the code to open the calendar
Until now you have been opening the pop-up calendar from from the
Visual Basic Editor but we need a way to open it from Excel. This
will take the form of a simple macro. In the Project Explorer right-click on the name of the
workbook in which you created the UserForm then choose
Insert and Module. This creates a new code module which
then opens in the main window of the Visual Basic Editor. Type:
&&&&&&Sub OpenCalendar
and press [Enter]. Excel places a pair of brackets after your
typing and after an empty line adds the line End Sub. Place
your cursor in the empty line between Sub... and
and enter the following statement:
&&&&&&frmCalendar.Show
If you gave your UserForm a different name make sure you
use it here. The completed code should look like this:
Sub OpenCalendar()
&&&frmCalendar.Show
You can test your macro by going to Excel and clicking the
Macros button on the View tab or the Developer
tab. Your macro will be listed in the Macro dialog box.
Select it then click Run to open the calendar.
HOW THE CODE WORKS
The Show command is the reverse of the Hide command
mentioned earlier. But this time you can't use Me to refer to
the UserForm because it is not the object in which this bit of code
resides. The UserForm has to be referred to by name. There is also a
Load command which opens a Userform into memory but does not
make it visible. It is unnecessary to use it here because, if a
UserForm is not already open in memory when the Show command
is given, Excel assumes that it must Load it first and does
so automatically.
Step 11: Write the code for the shortcut menu
Now that there is a macro to open the calendar we can add some
features to make it more convenient for the user to run it. One way
is to add a new command to the Cell context menu, the one
that the user sees after right-clicking on a cell. This means
writing some code to add a new item to the menu when the file
containing the calendar opens, and some more code to remove the menu
item when the file closes. This code has to run automatically when
the file opens or closes so we make use of special event procedures
that are located in the ThisWorkbook code module.
In the Visual Basic Editor go to the Project Explorer and click
the plus sign [+] next to the folder marked Microsoft
Excel Objects under the name of the file containing your
calendar. When the folder opens double-click the item marked
ThisWorkbook to open the code module in the main window. Choose
Workbook from the left-hand drop-down list at the top of the
window. This automatically creates an empty event procedure for the
Workbook_Open event. Between the Sub... and End Sub
statements type:
&&&&&&On Error Resume Next
&&&&&&Dim NewControl As CommandBarControl
&&&&&&mandBars(&Cell&).Controls(&Insert Date&).Delete
&&&&&&Set NewControl = mandBars(&Cell&).Controls.Add
&&&&&&With NewControl
&&&&&&&&&&&&.Caption = &Insert Date&
&&&&&&&&&&&&.OnAction = &Module1.OpenCalendar&
&&&&&&&&&&&&.BeginGroup = True
&&&&&&End With
Your completed code should look like this:
Private Sub Workbook_Open()
&&&On Error Resume Next
&&&Dim NewControl As
CommandBarControl
&&&mandBars(&Cell&).Controls(&Insert Date&).Delete
&&&Set NewControl =
mandBars(&Cell&).Controls.Add
&&&With NewControl
&&&&&&.Caption = &Insert Date&
&&&&&&.OnAction = &Module1.OpenCalendar&
&&&&&&.BeginGroup = True
&&&End With
IMPORTANT: If you changed any of the names within your project
you must make sure you also edit the code so that the matching names
are used. So, if you called your macro anything other than &OpenCalendar&
or the module in which it is located is called anything other than
&Module1&, you should amend the code accordingly. Make sure, for
example, that the text &Insert Date& is spelled the same way each
time it is used. Simple errors like this are the most common reason
for code not working
HOW THE CODE WORKS
The Workbook_Open event happens automatically when a workbook
opens in Excel. As before, the code starts with an instruction to
ignore any errors that might occur (you should always use this
command with caution and only when you are sure that nothing
untoward can happen if an error is ignored). Then a Dim
statement is used to declare a variable which I have called &NewControl& and which represents a menu item. Before proceeding to
create the new menu item the next statement deletes the item from
the menu. This seems illogical but it is a safety measure to make
sure that, if Excel did not close properly last time, we are not
left with multiple copies of the menu item. If, as expected, Excel
closed properly and the menu item was deleted then this statement
would cause an error to occur, hence the error handler at the start.
The next statement adds the item to the menu. A With Statement
(a way of grouping together a number of commands relating to the
same thing) is used to set the properties of the new menu item.
Now go to the right-hand drop-down list at the top of the code
window and choose BeforeClose to create an event procedure for
the Workbook_BeforeClose event. Between the Sub... and
End Sub statements type:
&&&&&&On Error Resume Next
&&&&&&mandBars(&Cell&).Controls(&Insert Date&).Delete
Your completed code should look like this:
Private Sub
Workbook_BeforeClose(Cancel As Boolean)
&&&On Error Resume Next
&&&mandBars(&Cell&).Controls(&Insert Date&).Delete
HOW THE CODE WORKS
The Workbook_BeforeClose event happens automatically as a workbook
closes. This procedure simply deletes the new item from the menu and
is protected with a simple error handler.
Step 12: Add a keyboard shortcut&to activate the calendar
If you like using keyboard shortcuts then you might like to have
one automatically assigned to your pop-up calendar. This requires an
additional command statement to be added to the Workbook_Open
event procedure. Add the following line to the
Workbook_Open event procedure. Either below the Dim...
statement or immediately above the End Sub statement:
&&&&&&Application.OnKey &+^{C}&, &Module1.OpenCalendar&
You also need to cancel the shortcut assignment when the file
containing the calendar closes so add the following line to the
Workbook_BeforeClose event procedure:
&&&&&&Application.OnKey &+^{C}&
The completed code should look like this:
Private Sub
Workbook_BeforeClose(Cancel As Boolean)
&&&On Error Resume Next
&&&Application.OnKey &+^{C}&
&&&mandBars(&Cell&).Controls(&Insert Date&).Delete
Private Sub Workbook_Open()
&&&On Error Resume Next
&&&Dim NewControl As
CommandBarControl
&&&Application.OnKey &+^{C}&, &Module1.OpenCalendar&
&&&mandBars(&Cell&).Controls(&Insert Date&).Delete
&&&Set NewControl =
mandBars(&Cell&).Controls.Add
&&&With NewControl
&&&&&&.Caption = &Insert Date&
&&&&&&.OnAction = &Module1.OpenCalendar&
&&&&&&.BeginGroup = True
&&&End With
HOW THE CODE WORKS
The OnKey command is used to instruct Excel to run a specific
macro when a particular combination of keys is pressed. The key
combination is defined by a code, in this case +^{C}
which represents the shortcut [SHIFT]+[CTRL]+[C]. The plus
sign represents the [SHIFT] key, the caret (^) represents the
[CONTROL] or [CTRL] key, and the {C} represents the letter C. If you
prefer a different shortcut then you can specify something else. If you
would like to see a complete list of the key codes that can be used
go to your code module and place you cursor within the word OnKey
in the code, then press [F1]. This opens Help at the
appropriate page. The OnKey command is used in the
Workbook_Open event procedure to assign the keyboard shortcut to
the macro that opens the calendar, and again in the
Workbook_BeforeClose event procedure, but this time without the
macro assignment, to cancel the assignment when it is no longer
Step 13: Test the code
Before testing your code you should CHECK YOUR TYPING,
especially anything you have typed in quotes, such as the caption
you used for the menu item! The Visual Basic Editor is good at
spotting coding errors but can't check your text entries. It is far
better to find and correct any errors now than after your code has
crashed and maybe caused problems in your worksheet. You can
additionally check any new code by compiling it. Open the Debug
menu and choose Compile VBA Project. If you get no
messages then the editor has not found any problems. If the
Compile... entry is disabled it means that there is nothing new
to compile.
Compile and Save your code then close Excel. This
ensures that Personal.xlsb is closed. If you are working in a
file other than your Personal.xlsb then you need only close
and save that file. Re-open Excel (or re-open the file in which you
created the calendar if it was not Personal.xlsb) and
right-click on any cell. You should see the command Insert Date
at the bottom of the context menu. Choose it to make your calendar
appear. When you pick a date from the calendar the date will be
written into the cell and the calendar will close. If you added the
keyboard shortcut to open the calendar then test that too by holding
down the [CONTROL] and [SHIFT] keys and pressing [C].
If any of this does not work then the most likely problem is a
typing error in your code. Go back and check it thoroughly!
Step 14: Password protect the code
If it is likely that other users will have access to your code
projects and particularly if you intend to distribute your project
either as a shared file, template or Add-In, it is advisable to
protect your code with a password. This has a number of advantages.
It prevents a third party from viewing and possibly interfering with
the code. It also guarantees that, in the unfortunate event that an
unforeseen error causes the code to crash, the user does not find
themselves in the Visual Basic Editor looking at a stalled code
module and not knowing what to do next.
In the Visual Basic Editor go to the Project Explorer then
right-click on the name of the file containing your pop-up calendar
project and choose VBAProject Properties. Select the
Protection tab of the Properties dialog and enter then
confirm your password. Place a tick in the Lock project for
viewing check box then click OK and Save the file.
The password protection comes into operation after the protected
file has been closed and re-opened. If you try to view the code of a
protected code project the Visual Basic Editor requires you first to
supply the password.
IMPORTANT: Don't forget the password! If you lose the password
to a VBA code project you will permanently lock yourself out of the
code. There is no easy way to recover the password of a protected
file which, after all, is the point of protecting it.
That's it! You've finished and should have a fully-functioning
pop-up calendar. If you want to continue and turn it into an Add-In
then read on...
Building an Excel Add-In
The following steps explain how to convert your project into an
Add-In which you can use yourself or distribute to other users. If
you have been working so far in Personal.xlsb you don't need
to build the calendar again. Just copy or move your UserForm and
associated code into a regular Excel workbook. It isn't difficult to
do. Open a new, empty workbook in Excel then in the Visual Basic
Editor go to the Properties Window and drag the UserForm from
Personal.xlsb to your new workbook where Excel will create a copy of
it. All the UserForm's code will automatically go with it. Do the
same for the module in which you created the macro that opens the
calendar (Module1 in this example). Then copy the
Workbook_Open and Workbook_BeforeClose code from the
ThisWorkbook module in Personal.xlsb to the
ThisWorkbook module of your new workbook.
IMPORTANT: If you plan to use the Add-In yourself you should
delete the UserForm and all the associated code from Personal.xlsb
to eliminate any risk of conflicts between them.
If you have been working in a regular Excel workbook then you
don't need to do anything else, just follow on from here.
Step 15: Save the file and define its properties
If you have been working in a regular Excel workbook you will
already have saved the file. If not, or if you have just copied
everything into a new workbook from Personal.xlsb then you
need to save it now. Save the file as an Excel Macro Enabled
Workbook (*.xlsm) and call it something like Calendar.xlsm.
It is possible to miss out this step but I recommend it because
it gives you a back-up copy of your work and also allows you to add
a meaningful name and a description to your Add-In. Then Close
the workbook.
Use Windows Explorer to find the workbook you just saved then
right-click on it and choose Properties. In the File
Properties dialog click the Summary tab and enter some
suitable information about your Add-In. Alternatively you can
you can access the file properties from within Excel when the file
In Excel 2007 click the Office Button then
choose Prepare and Properties. In the
Properties pane click Document Properties then
Advanced Properties to open the Properties dialog.
In Excel 2010 go to the File tab, click Info
then choose Properties and Advanced Properties to
open the Properties dialog.
The Title property will become the name of the Add-In (as
distinct from its filename) and the Comments property will be
used to provide the user with a brief description of its features.
After defining the properties remember to save the file!
Step 16: Create the Add-In file
To create the Add-In open your Calendar.xlsm file in Excel
then open the Save As dialog box:
In Excel 2007 click the Office Button then
choose Save As and Other Formats.
In Excel 2010 go to the File tab and choose
If you want to give your Add-In a different filename you can do
it now. Open the Save as type dropdown and choose Excel
Add-In (*.xlam) from the list then click Save. Excel
creates a copy of the file (the original remains unchanged) as an
Add-In and automatically stores it in the Add-Ins folder. The
location of the Add-Ins folder might be different depending on your
particular set-up but it is usually found at:
C:\Users\&Username&\AppData\Roaming\Microsoft\Add-Ins.
The location of an Add-In is important but not critical since, as
you will see shortly, you can tell Excel where to find an Add-In if
it is not in the usual place. This is useful if, for example, you
want to store your Add-Ins in a network location.
NOTE: You will see that there is also the option to save the
file as an Excel 97-2003 Add-In (*.xla). Don't do this unless you
know that it is going to be used in an older version of Excel (Excel
2003 or earlier). The Add-In needs to be in the right format for the
version of Excel in which it will be used.
Step 17: Activate the Add-In
Now that you have built the Add-In it must be activated in Excel
before you can use it. If you are going to distribute your Add-In
the users will need to do this on each computer in which the Add-In
is installed.
In Excel 2007 click the Office Button and
choose Excel Options.
In Excel 2010 go to the File tab and choose
Go to the Add-Ins section of the Excel Options
dialog. If you have installed the Add-In file in the default
Add-Ins folder you will see it listed here under the Inactive
Application Add-Ins heading (don't worry if you put it somewhere
else - you will be able to browse for it later). You can click on
its name here to view details of its location and the properties you
assigned earlier.
Make sure that Excel Add-Ins is selected in the Manage
list then click Go to open the Add-Ins dialog in
Excel. If you installed the Add-In in the default Add-Ins folder it
will be listed in the dialog box. Otherwise, click the Browse
button to locate and select your Add-In file which will appear in
the dialog when you have done so. You will also see any other
Add-Ins that have already been installed including those that come
pre-installed with Excel. Selecting one displays its features at the
bottom of the dialog box. Now that Excel knows about your Add-In you
can switch it on and off from here. Doing so effectively opens and
closes the Add-In file in Excel. To switch your Add-In on place a
tick in the box next to its name and click OK.
NOTE: In Excel 2010 the Developer tab of the
Ribbon has a button (marked Add-Ins) that takes you direct to
the Add-Ins dialog.
The pop-up calendar is now ready for use and will remain so each
time you use Excel until you deactivate it by un-checking the box
next to its name in the Add-Ins dialog box. If you return to the Add-Ins section of the Excel
Options dialog you will see that your Add-In now appears under
the heading Active Application Add-Ins.
Download a Sample File
Before downloading any files from this site please read the
. If you download a file from this
site it is assumed that you have read, understood and agreed to the
terms set out in the notices.
So, maybe you are too busy or just can't be bothered to build one
for yourself. Don't worry, you can download a ready-made copy of the
Pop-up Calendar Add-In or an Excel workbook containing a functioning
Pop-up Calendar right here. The files are not password
protected so you can view and modify the code if you wish. You are
strongly advised to password protect the code
if you plan to
distribute these files to other users.
The files are supplied in .zip format. To download a file
right-click on the link and choose Save target as... (in
Internet Explorer) or Save Link As... (in Firefox) and
save the .zip file to your hard disk. You should extract the Add-In
or workbook from the .zip file before using it. Right-click on the
.zip file and choose Extract All... then follow the
instructions.