All Excel Templates Normally an 80% saving at $299.00. Super Special $175.00!
Check out this months super price offer !
MAIN INDEXWhat's New at OzGrid | 50% Off Offer | Excel Tips and Tricks | Excel VBA Tips and Tricks
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.
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
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()
UserForm1.Show
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 **
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 :