Solved

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

Posted on 2012-04-05
6
472 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 42

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 42

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 42

Expert Comment

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

Give me a few...

Dave
0
 
LVL 42

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

710 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