OzGrid Excel and VBA Newsletter

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

All Excel Templates Normally an 80% saving at $299.00. Super Special $175.00!

Check out this months super price offer !

Microsoft Excel Add-ins Financial Add-ins and Software
Microsoft Excel Training & Tutoring Conversion Software
Microsoft Excel Templates Construction and Estimating Software
Excel, Word, Access Password Recovery Financial Calculators
Corrupt Excel, Word, Access File Recovery Business Planning
Database Software Excel on the WWW
Windows & Internet Software Downloadable Excel Training
Barcode, Fonts, ActiveX, labels and DLL's OUR CURRENT SOFTWARE SPECIALS!
Chart Tools & Add-ins The Analysis Add-ins Collection
Trading Software & Add-ins TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!

Buy ContourCube ActiveX MAIN INDEX

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

What's New at OzGrid | 50% Off Offer | Excel Tips and Tricks | Excel VBA Tips and Tricks

What's New at OzGrid

Firstly, we must apologise for the downtime the website has been experiencing over the last week or so. As the number of visitors to our site is ever increasing, we have had to make changes to accommodate this, thus resulting in being off-line. We are sure you understand.

Due to the number of requests for help with Excel we have added a link to ourFree Excel Forum where you can now also post direct to theMicrosoft Excel Puplic Newsgroup .

With both the Forum and the Newsgroup open to Ozgrid visitors, there are no excuses for not getting that problem solved. It's a GREAT way to give yourself and/or your company *TOTALLY FREE* Excel and Excel VBA support.

This month, we have also finished the last of the changes for our book - 100 Excel Hacks . The book is off to the printers on the 11th, and all that is left for us to do now is to place the workbook examples on our website. This we will do over the next few weeks to ensure it is finished by the time the book hits the shelves.

Special Offer

If you haven't checked out our new templates we are now selling you can do so here:Excel Templates Many of the new templates are fromTemplate Zone All Template Zones templates come with a *60 Day Money Back Guarantee*

With one of the more popular newtemplates being found here

In addition to templates we have also been offered some *great specials* from another leading software maker. These are: Speed Up My Pc, Windows Back-up, Windows Tasks, Windows Utility Pack. To check out these pages for great specials,Go Here

Excel Tips and Tricks Microsoft Excel Calendar Control For Any Excel Workbook

One of the big problems many users have with Excel is entering dates correctly. If you want to ensure that users enter dates correctly, the Excel Calendar Control can make things easier for both you and the users of your spreadsheet. Download Demo

ALSO SEE: Excel Date and Times | Convert Excel Date Formats

If you do not have this Control, gohttp://www.winswim.com/support.htm and scroll down to "Calendar Control in Preference..."

Unless a date is entered correctly, Excel wont recognize it as a valid date, ie a serial value or number. This sometimes means you cannot perform calculations with the 'so-called' dates. It also means any charts or pivot tables based off the dates will not be valid.

Let's look at how we can ensure dates are entered correctly and make the entering of dates easier for the user at the same time.

Open the workbook for the calendar. It is a good idea to use your Personal.xls for this, in which case you should first go to Window>Unhide-PERSONAL.XLS . If this is greyed out it means you do not, as yet, have a Personal.xls. You can easily create one by recording a dummy macro. Go to Tools>Macro>Record new macro and choose "Personal Macro Workbook" from the Store macro in box. Then click OK, select any cell then stop recording. Excel will now have automatically created your Personal.xls. So now go to Window>Unhide-PERSONAL.XLS.

Now go to Tools>Macro>Visual Basic Editor (Alt+F11). Then go to Insert>UserForm from within the VBE. This should automatically display the Control Toolbox, if not go to View>Toolbox. Right click on the Toolbox and select Additional Controls. Now scroll through the list until you see: "Calendar Control 10.0" (number will differ depending on the version of Excel you are using), check the checkbox and click OK. Now click the Calendar that is now part of the Toolbox and then click on the UserForm we inserted.

Using the size handles on both the UserForm and the Calendar Control, make them both a reasonable size (See online version for screen shot). Now make sure the UserForm is selected then go to View>Properties Window (F4). Select Caption from the Properties Window and replace: "UserForm1" with the word "Calendar". Now go to View>Code (F7) and in the white Private Module in front of you, add the following code:

Private Sub Calendar1_Click()

ActiveCell = Calendar1.Value

End Sub

Private Sub UserForm_Activate()

Me.Calendar1.Value = Date

End Sub

Now go to Insert>Module and in this Public Module place this code:

Sub ShowIt()


End Sub

Next, click the top right X (or push Alt+F11) to return back to Excel.

Go to Tools>Macro>Macros (Alt+F8) and then select "ShowIt" click Options, assign a shortcut key and you're done.

All you need to do now is push your shortcut key and the Calendar will show with today's date as the default. Click any date and it will be inserted into the active cell. This ensures that any date entered is valid and it is a lot easier for the user.

***Database Software **

Excel VBA Tips and Tricks Advanced Microsoft Excel Calendar Control For Any Excel Workbook Download Demo

Following on from the Excel tips above let's look at how we can turn our standard date entry calendar into an advanced one. When done, we will be able to tell our calendar to add/subtract days, weeks and months.

ALSO SEE: Excel Date and Times | Convert Excel Date Formats

Ok, with the calendar control we have created, as shown above, we need to increase the width of our UserForm to about 340 and the width of the Calendar itself to about 215. The height can be any reasonable height. Next you need to add (to the right in a vertical order) 3 label Controls (Label1, 2 and 3) and 3 TextBox Controls (TextBox1, 2, 3). Starting from Label1 give this a Caption of "Months to add", Label2 Caption="Weeks to add" and Label3 Caption="Days to add". Then ensure Textbox1 is immediately below Lable1, Textbox2 is immediately below Lable2 and Textbox3 is immediately below Lable3. Now set the Value Property of each TextBox to 0 (zero).

Next to each TextBox (on the right) place a SpinButton, i.e SpinButton1, SpinButton2 and SpinButton3. These will be used to increment by Days, Weeks or Months. Set the MIN Property of each SpinButton to -500 (or any negative amount) and the MAX Property to 500 (or any positive amount).

Next add a CommandButton Control to the top of the UserForm, to the right of Calendar1 and above all TextBox and Label Controls. Give this CommandButton a Caption of "Reset to Today's Date".

Now it's time for the code, so double click any Control to get to the UserForms Private Module and in here paste ALL the code shown below.

Dim dDate As DatePrivate Sub CommandButton1_Click()Calendar1 = DateSpinButton1 = 0SpinButton2 = 0SpinButton3 = 0End Sub

Private Sub SpinButton1_Change()If SpinButton1 >= -1 And SpinButton1 _<= 1 Then dDate = Calendar1TextBox1 = SpinButton1Calendar1 = DateAdd _("m", TextBox1.Value, dDate)UpdateCellEnd SubPrivate Sub SpinButton2_Change()If SpinButton2 >= -1 And SpinButton2 _<= 1 Then dDate = Calendar1TextBox2 = SpinButton2Calendar1 = DateAdd _("ww", TextBox2.Value, dDate)UpdateCellEnd SubPrivate Sub SpinButton3_Change()If SpinButton3 >= -1 And SpinButton3 _<= 1 Then dDate = Calendar1TextBox3 = SpinButton3Calendar1 = DateAdd _("d", TextBox3.Value, dDate)UpdateCellEnd SubPrivate Sub UpdateCell()ActiveCell = Calendar1ActiveCell.NumberFormat _= "dddd d mmmm yyyy"End Sub

Once the code is in place, save and then go back to Excel and show the calendar.

Until next month .................

Until next month, keep Excelling!

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :