Solved

MS Excel Date Picker Issue is automatically changing cells other than the designated Date Cells

Posted on 2012-04-05
6
464 Views
Last Modified: 2012-04-05
Issue:  The date picker added is not working properly, you have to hit the down arrow on the user form created, in addition the overall look is awful.

Is there a simple, clean looking date picker for the Status Date and Due Date columns.

Also, anytime you right click anywhere in the Sheet1 Worksheet in, the date picker pops up, when it should only be available in the Status Date and Due date columns (which are in green on Sheet1 tab).

This thread is directed towards fixes for issues 3-5a - the description of the issues are also located on the Desc tab of the workbook. Issues 5b-11 issues will be following in subsequent posts.

Thank you in advance for your assistance.
Test-Report-v2.xlsm
0
Comment
Question by:ckwillGWU
  • 4
  • 2
6 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37814751
I don't see the use of date picker control that you're talking about in the attached worksheet so hard to comment on that.  Can you advise or upload the workbook that has the control and the problems you are mentioning?

Dave
0
 

Author Comment

by:ckwillGWU
ID: 37814800
If you right click in the Status Date column on the Sheet 1 tab, you have to hit the drop down arrow, then the date picker will appear.

I used the instructions from this post to create.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22041214.html
0
 
LVL 41

Accepted Solution

by:
dlmille earned 400 total points
ID: 37814810
I see that now.  The right click is working properly for me - it only functions to popup the date picker userform in column G.

I would rewrite as follows:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Not Intersect(Target, [G:G]) Is Nothing Or Not Intersect(Target, [H:H]) Is Nothing Then
        Cancel = True
        UserForm1.Show
    End If
End Sub

Open in new window


This checks for right clicking in G or H.

What is it that you don't like about this?  The other alternative would be to put the control directly on the sheet, when the user right clicks there for selection. And then, you'd still get the month popup.  Thoughts?

Dave
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ckwillGWU
ID: 37814841
I sort of imagined the date picker as seen in the MS Access forms, but I'll work with this one and mayve change the Form name and lessen the whitespace on the form. I'm being overly picky with the date picker. This will work and thanks for the updated code.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37814842
Hang on - I'm positioning it right over the cell.

Give me a few...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37814875
I tried to reduce the size of your form, etc., and it worked great, but didn't appear on the cell.  I tried Chip Pearson's tip on that http://cpearson.com/excel/FormPosition.htm but it didn't work properly - I probably did something wrong as Chip's solutions have never failed me (need to go back to it for other uses, lol)  However, it would still have the ugly userform wrapper, though smaller, etc.

--------------------

I think this is nicer, but a bit of code to manage - it picks up from the learnings using drop down combo boxes on data validation lists - article with DynamicDV! addin utility - please vote YES if you think that helpful:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html

The modification drops the data picker right atop the cell and you can pick dates that way as opposed to using the UserForm.  While some/lots of MPV's no-no using ActiveX controls on a sheet, this is well developed, and Contextures uses this approach that I learned to build DynamicDV!  I used the same approach to drop it where it needs to be and use it on the worksheet.  Its only one control used over and over again, so the quirks (I talk about this in another article) should be non-existent (re: controls resizing etc., when there are static controls being used on the sheet).

here's the Sheet1 code that addresses the change:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
    
    Call datePickerManager(Target, False)

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Not Intersect(Target, [G:G]) Is Nothing Or Not Intersect(Target, [H:H]) Is Nothing Then
        Cancel = True
        Call datePickerManager(Target, True)
    End If
End Sub

Open in new window


And this code in Module1:
Sub datePickerManager(Target As Range, bDisplay As Boolean)
Dim dpkrTemp As OLEObject
Dim WS As Worksheet
Dim vType As Variant
Dim chkDVList As Variant
Dim proceedSetup As Boolean

    Set WS = ActiveSheet
    
    On Error Resume Next 'connect to temporary Date Picker "TempCombo", testing along the way using Err.Number

    Set dpkrTemp = ActiveSheet.OLEObjects("TempDpkr")
    If Err.Number <> 0 Then 'the Date Picker object must have been inadvertently deleted, so let's create it
        Set dpkrTemp = ActiveSheet.OLEObjects.Add(ClassType:="MSComCtl2.DTPicker.2")
        dpkrTemp.Name = "TempDpkr"
    End If
    
    On Error GoTo errHandler
    If bDisplay Then
        With dpkrTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .LinkedCell = Target.Address
            .Activate
        End With
    Else
        With dpkrTemp
            'hide the Date Picker, and get it out of the way from inadvertent deletion
            If .Visible = True Then
                .Visible = False
                .Left = Range("BB5000").Left
                .Top = Range("BB5000").Top
            End If
        End With
    End If
        
errHandler:
    'do Nothing for the moment
End Sub

Open in new window


See attached finished project.

Enjoy!

Dave
Test-Report-v5.xlsm
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now