Posts by gijsmo

Re: Non-activex Datepicker Calendar Control

Well, a bunch of things happened since the last post that gave me the opportunity to test the code on Windows 7 32 bit with Office 2007 and Windows 7 64 bit with Office 2010.

In addition, the users using my implementation of the date picker decided they wanted more functionality that was previously available in DTPicker from MSCOMCT2 namely the ability to increment or decrement the date using the up or down key whilst in the "date" field (which is a textbox in my implementation) ie, without actually using the drop-down calendar at all!

Getting things working for Office 2007 and Office 2010 meant contending with the delightful "ribbon" and in addition (for Office 2010) using compiler directives for VBA7. This means that Stephen Bullen's clsFormChanger module also got some rework as it uses quite a lot of APIs. Oh, and whilst there are both VBA6 and VBA7 compiler directives in the code, the VBA6 directive will also work successfully in VBA7 but not vice-versa.

Chip Pearson's form positioner code is too old to contend with Office 2007/2010 so I kludged together some changes that seem to work although I cannot guarantee this in all cases - I have had to make some assumptions on dealing with the height of the "ribbon" to get the proper vertical compensation factor. I do not have a lot of experience with the "ribbon" and I have assumed it can be moved to the left although I am pretty sure this is not so. If it can be moved, the code may not work because I could not figure out a way to move it!

Anyway, with limited testing on Excel 2007 and 2010, the pop-up calendar does seem to be placed reasonably close to the active cell in most cases, with the ribbon maximised or minimised and when the form is zoomed. This of course only applies to using the pop-up calendar directly from the worksheet, there is no impact to using the pop-up calendar on a userform as userforms don't need to use the FormPositioner module.

The additional functionality of incrementing or decrementing the date within the textbox meant that I used a "hard-coded" date format instead of vbShortDate. This was because I had to know what the format of the date field was in order to determine where the cursor was in the "date" field ie, my textbox that holds the selected date. There are APIs that can tell me the current format of vbShortDate but it started to get messy because the typical format in my case was "d/mm/yyyy" which meant I was contending with either a 1 or 2 digit day field. Instead I have coded within the Calendar module itself a CommonDateFmt variable which is initialised to "dd/mm/yyyy" for my region. This can be changed to anything of course but I recommend "dd" or "mm" rather "d" or "m" in the respective part of the field. Using this means you have to load the Calendar module first in order to initialise the value of CommonDateFmt (see the DatePicker test module). You also need to call the FormatDate function in the Calendar module to ensure all dates are in the "common format".

With this change, there is some additional code available in the Calendar module that can deal with the user pressing the up/down key in a textbox holding the date value. The textbox is enabled but locked at design time which is important. I needed it enabled to allow a key to be pressed but locked to disallow a direct change. The code determines where the cursor has been placed in the "date" field and will increment or decrement either the day, month or year portion of the date up to the min/max values you specify. You can use the mouse to move the cursor to the part of date field you want to increment or the left/right arrow keys once you are in the field.

I have now tested the code on Excel 2003 on both Windows XP and Windows 7 32 bit, Excel 2007 on Windows 7 32 bit and Excel 2010 on Windows 7 64 bit. It should work on some earlier versions of Excel possibly as far back as Excel 2000 but I certainly cannot guarantee Excel 97.

Re: Non-activex Datepicker Calendar Control

Thanks for the Kudos, they are always appreciated!

In return, I am attaching a slightly modified version of the code based on my final round of testing. The users I tested my code on wanted a "beep" to tell them when they had selected a date that was outside the min/max date range rather than just returning the min/max date.

In addition, further testing with Chip Pearson's excellent FormPositioner code revealed a number of instances where the pop-up calendar could be partially outside the visible portion of the screen. So there's been some more tweaking here as well.

The new version yet again demonstrates how to use the pop-up calendar on a UserForm or directly from the worksheet. If you only need the pop-up calendar in a UserForm, you won't need the FormPositioner module.

All the other modules have had some code tweaking except for Stephen Bullen's clsFormChanger class module.

Thanks again to Baodad for starting this most excellent thread and to OzGrid which has given me many great ideas over the years as I learnt about Excel VBA.

Re: Deleting Rows from a single column based on multiple criteria

AAE is right in that the looping is inefficient when compared to using the AutoFilter. My only concern is that the criteria will end up on the worksheet somewhere which may or may not be an issue depending on whether this workbook is meant to be used by an end-user or not. If it is not an issue, then this might be better done with a named range so there is no hard coding for Sheet 2 (in the AAE example).

Also, if having the criteria data visible and/or possibly available to an end-user is an issue, then changing the hard coded values in the code is no more of a problem than changing the criteria values on a sheet - but it is less visible to an end user.

One other question I have (for my own education) is what if an AutoFilter was already in place - the code sample would turn that filter off wouldn't it?

Re: Deleting Rows from a single column based on multiple criteria

You will need to find the last row of data in the column and then work backwards to delete the rows containing that matching data.
A few assumptions of course:
1. The number of rows after column A is no greater than the number of rows after column A - this affects how you locate the last row in a column
2. The sheet containing the data is the first sheet in the current active workbook

Here's a possible solution:

Function FindLastRow() As Long
  If WorksheetFunction.CountA(Cells) > 0 Then
    FindLastRow = Cells.Find(What:="*", After:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row
  Else
    FindLastRow = 0
  End If
End Function 'FindLastRow


Sub DeleteUnwanted()
  Dim lLast As Long, lNum As Long
  
  lLast = FindLastRow
  
  With ActiveWorkbook.Sheets(1)
    For lNum = lLast To 1 Step -1
      Select Case .Cells(lNum, 1).Value
        Case 100, 250, 1200, 1515, 2649
          .Rows(lNum).Delete Shift:=xlUp
      End Select
    Next
  End With
End Sub 'DeleteUnwanted

There are other ways to find the last row if assumption A is not true.

Also, of course, you can delete just the cell in column A without the deleting the whole row if desired (and just move the rest of the data up).

Re: Non-activex Datepicker Calendar Control

Of course, as always happens when you think you've posted your final code...someone else testing it finds a bug!

I had allowed the pop-up calendar to close without selecting a date if the user (left)clicked on the first or second row (while still allowing the Esc key to do likewise).
Oh, and double (left)clicking on the last row will simply return the current date (as long as the current date is within Min Date/Max Date range of the calendar). From memory, this is what DTPicker in MSCOMCT2 does as well.

Anyhow, a simple bit of initialisation code tripped me up (the TextBoxDate field was not set to "blank") which causes the potential for a date to be returned the next time the calendar is popped up and the user presses the Esc key to cancel the pop-up.

So, a tidied up version is attached....this time for sure! There's been a bit of housekeeping as well.

Re: Non-activex Datepicker Calendar Control

After more testing in the field (and a little more debugging) I have made some further modifications to the code that should simplify usage. Mainly this was to make coding simpler when dealing with multiple date fields on a user form as I discovered this could quickly get messy. This was fixed by putting some pop-up handling code into the Calendar module itself.

The other thing I discovered was the need for the "Checkbox" feature that DTPicker from MSCOMCT2 had - this allowed you to enter a date only if the checkbox was ticked (ie, for non-mandatory date fields).

Putting this altogether again, I have attached the latest demo of the code. There's been a lot of streamlining in various places to make the pop-up easier to use either directly or in a userform. There are also further modifications to the original clsCalendar class module (which gave me all the great ideas in the first place!).

Re: Non-activex Datepicker Calendar Control

Of course the right click event can cause the selection change event to fire (if you right click on a different cell). So the pop-up calendar will pop-up twice in a row in some instances in my previous example.

After much investigating and hunting around to see what others are doing, I discovered it wasn't that easy to stop both events from triggering - not even with EnableEvents.

In the end I used an API call to determine if the right button on the mouse had been clicked and tested for this in the selection change event. Of course, you can simplify things by not allowing the right click to do anything with a small modification to Worksheet_BeforeRightClick.

The updated demo code is attached. This should address everything I currently need this for.

Re: Non-activex Datepicker Calendar Control

As Baodad indicated, 'single-clicking' on a worksheet is essentially changing the selection and this can be captured by the Worksheet_SelectionChange event.

However, the exception is that if you single left-click on the currently active cell, the Worksheet_SelectionChange event won't fire. There is no Worksheet_BeforeLeftClick event and depending on whether the sheet is protected or not, the user may end up editing the cell value directly.

The nearest I can come to a simple solution is to protect the sheet and also capture both the Worksheet_BeforeDoubleClick and the Worksheet_BeforeRightClick events. With the sheet protected, a double click will allow you to pop-up the calendar (in the already selected cell) and prevent the cell value from being edited. The right click capture prevents other actions that you might normally do with a right click.

Putting this altogether and finding a method to pop-up the calendar in a "logical" place is no mean feat in Excel. I borrowed the Form Positioner code from Chip Pearson (and modified it to allow for zoomed windows) in order to pop-up my calendar somewhere near the active cell. I make no guarantees you won't trip up using this but it does appear to work well in all my testing. If you cannot see the calendar on the window where you expect, pressing the Esc key should unload the pop-up.

The sample code I have attached puts this altogether - the difference is that the calendar will pop-up on all valid date fields, you would need to modify this further if you wanted to limit the pop-up to fields with a "d-mmm-yy" format. Oh, and obviously the calendar will only pop-up when a single cell with a valid date value is selected. The attached file also still includes a demo of using the pop-up calendar in a userform.

Re: Non-activex Datepicker Calendar Control

Just for completeness, I have removed some redundant code from the last date picker demo (the duplicated code for the original command button has been deleted) and I've made a minor change to the Calendar module (moved a bit of code from Initialize to Activate).

Other than that, the attached file has the same code as before - except for changing the less familiar 'calendar' icon to the more familiar 'drop-down' arrow using a transparent gif file. I found in testing that people understood what this meant better than the calendar icon.

Enjoy!

Re: Non-activex Datepicker Calendar Control

Well obviously this thread is still alive and well. In response to Ger, I don't need to place the selected date into an active cell for the project I am working on but what you have suggested should work.

In my case I am collecting dates on a UserForm to process in other macros. I have now tested this on a Win 7 PC running Excel 2007 and I have had no issues. Actually, it was the PC at work with Excel 2007 that started my quest to find a solution.

Also attached is my latest version of the code which now allows for the calendar to start either on Monday or Sunday (I need to start on a Monday as my project mainly revolves around working days). Also, I have changed the command button that kicks off the drop-down calendar with an Image object as this is more or less what I'll be using in my finished product - this makes it look similar to the familiar DatePicker in MSCOMCT2. However, a command button next to a text box will also work.

Of course, the original functionality that was in the CalendarControl5.xls demo is still there - you can press Esc to close the pop-up calendar without selecting a date (no need to click on a "cancel" label) and you can click anywhere on the last row to return to the month that has today's date (unless this is outside of the min date / max date range - I put a small bug fix in clsCalendar related to this).

Thanks for the encouraging responses - I think my work is now done with all your help!

Re: Non-activex Datepicker Calendar Control

Hi guys, I've had the same problem with the DatePicker OCX as the rest of you seem to have had and this thread is the closest I came to finding a complete replacement to the DatePicker in MSCOMCT2 however, in my case, I was trying to replicate the actual "pop-up" action that it provided.

So I took what I learned from this thread and combined it with some other fantastic routines I found and built what I consider to be a complete replacement for DatePicker which should work across all platforms. It also includes the ability to specify a min and max date (which is something I needed for my project).

I have attached a demo of my date picker which can be easily ported to other projects - without having to worry about pasting into forms that may already have the same label names as it runs as a separate UserForm.