Coordinate based query and data copy/paste/clear

[mrmattmc]

I am just getting into this idea so really my question to everyone is what type of function or combination of functions would best achieve what I am looking to do.

I have a workbook with a summary sheet. This sheet summarizes the data found on three other sheets of data.

HighlightedTargetReturnLink.jpgHighlightedTargetReturnLink2.jpg

What I would like to implement is this. If I click on a date (say date2) in the left column the function would check each non hidden cell in that row and would return the three rows of data for name 1, the 4 rows of data for name 2 and so on. I have a few other criteria that are cell targets on the summary sheet I would like to add to the date and name to make it a 4, maybe 6 criteria query and would also like to be able to specify where the data is written to.. sheet name, column and row. I can work out the "return button" to clear the data once the user wants to return the summary view.

A variant of the above idea would be if the name was clicked on then the function would query down the column returning the same unique results like above

Lastly, If the user was just to click on the 4 for name2 date2 it would return the 4 rows of data this is referring to.

I'm really new to VBA and have been making great strides in the few weeks I have been doing this but I just don't know where to start with this one.

[p45cal]

This sounds very complicated!
It's certainly possible but it would be hard work and probably very convoluted, and probably quite flaky.
My thoughts are that the data you want to summarise could either be queried directly with a pivot table, or the data could be (copied and) re-arranged first (with or without a macro) so that a suitable pivot table could be created.
The result you want looks very much like it could be produced by a pivot table.
I feel such a solution would be simpler and more robust.
A sample workbook would be useful here, anonymised if sensitive.

[mrmattmc]

Perhaps I should just focus on the last idea. User clicks on a number and the script pulls up just the data the number is referring too. ?

[p45cal]

Perhaps I should just focus on the last idea. User clicks on a number and the script pulls up just the data the number is referring too. ?

This sort of thing is built-in to pivot tables; I think it's called drill down and is initiated by a double-click on a number.

[mrmattmc]

So it took some doing but I have managed to cobble together a working sample. This is from an earlier version of the workbook where I still had to work out some of the underlying functions.

fake.xlsm

The workbook is very codependent on a lot of things.. Yeah the underside is a bit messy but it does the job. The data tab is a repository used to populate all the semi variable data. Every week the source data changes and the user brings in the new source workbooks and runs the update sheet function which takes about 30 seconds to mine all the data from the three sheets. The newest version does all this automatically, the user just need to put the new workbooks in the same directory as the dueview workbook.

The sample file should be viewed from the DueView tab. The A1 dropdown list works. The sheet currently only gets data from the PM sheet. It took so long to sanitize the data while keeping some functionality that the A2 dropdown only works for the "PM" category. I had to take the criteria out of the formulas for A3 and A4 dependent drop downs as the named ranges got nuked in the sanitizing process. This is what the individual cell formula looked like before I removed the end part referencing A3 and A4.

=IFERROR(HYPERLINK(CELL("address",INDEX(PM!$E$5:$E$3000,MATCH(1,IF(PM!$B$5:$B$3000=$G$3,IF(PM!$E$5:$E$3000=$C5,1))),0)),COUNTIFS(PM!$A$5:$A$3000,$A$1,PM!$B$5:$B$3000,$G$3,PM!$E$5:$E$3000,$C5,PM!$W$5:$W$3000,IF($A$3="ALL","*",$A$3),PM!$C$5:$C$3000,IF($A$4="ALL","*",$A$4))),0) 

The C1 dropdown works but I only put one overdue date in the sample data to show the purpose of the selection. The C2 dropdown is looking at data in row 102. White text on white background.

Click on the ShowAllCells button on the right to see everything I am hiding.

If I had to push for one function it would be If a user clicks on cell O8 the script would pass values from C8 (Dates Column) O4 Type (this determines which sheet to search, PM, T1 or T2) as well as O3 for name. This is the tricky part thanks to my design stupidity of using merged cells because any cells selected in M,N,O columns need to look to M for name data...I think. I also need to pass the constants to the query of A1,A2 and A3 with ALL=* for A2 and A3.

O4 determines which sheet to search
C8 would be compared to column E
O3 would be compared to column B
A1 to column A
A3 to column W
A4 to column C

I would like to copy the results to the DueView tab starting at CL4 , looking for the first empty row and incrementing down one for every result. That way if a user click on two cells the second set of copied data would paste below the first set and so on.. It would be icing on the cake to be able to specify which columns to copy as there is a lot of useless data on the source sheets.

Is it possible to have the Worksheet_SelectionChange event targeted to a named non contiguous range? If so, I would probably run two versions of the above function as some of the columns on the T1/T2 sheets have data in different columns then the PM sheet.

[mrmattmc]

Forging ahead I have been putting some code together to get the values from the name and date cells shown in my first post. The range is a bit different but other than that it works great. Don't see any problems so far...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:G16")) Is Nothing Then
r = Target.Row
c = Target.Column
Set FSE = Cells.Item(3, c)
Set DD = Cells.Item(r, 1)
MsgBox FSE & " " & DD
End If
End Sub

This code does a fine job of returning the value from a predetermined row and column when any cell in the range is selected by the user.

On to the next task of passing my other values to my search.

[mrmattmc]

Moving ahead to the next step.

Using the above function I can get the date data from Column C (3) and the name data from Row 3 (3)

SelectionChange.jpg

Because of the Merged cells that contain the name I cobbled together an IFElse check to see if the name value returns as blank, if so shift back one column (cl -1). If that value returns as blank shift back another column (cl-2). This takes care of the problem of when a cell is selected in the T2 or PM rows the name returns as blank due to the merge..If only I had know the evil of merged cell. haha if then.. Anyway

Now that I can reliably get the Name and Date I set names to the other values I want to use in my search. Such as "Set Dis = Cells.Item.(1,1). See code below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("DataSet")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
rw = Target.Row
cl = Target.Column
If Cells.Item(3, cl).Value <> "" Then
    Set FSE = Cells.Item(3, cl)
ElseIf Cells.Item(3, cl - 1).Value <> "" Then
    Set FSE = Cells.Item(3, cl - 1)
Else
    Set FSE = Cells.Item(3, cl - 2)
End If
    Set DD = Cells.Item(rw, 3)
    Set DIS = Cells.Item(1, 1)
    Set Pgrp = Cells.Item(3, 1)
    Set Typ = Cells.Item(4, cl)
    
MsgBox DIS & " " & Pgrp & " " & FSE & " " & DD & " " & Typ
End Sub

Sending all the data to the MsgBox seems to be a good way to verify my script is doing what I ask.

Now just to pass the above name ranges (if that's what they are called.. I don't know, only been doing this a month) to a search string. Once I have that search cobbled together I will then output the results to the right of the summary sheet as a type of dynamic data window.

Anyone got experience with setting up the blank text box idea? I read about doing that then dumping the data into it to behave like a dynamic window.

Wish me luck

[p45cal]

I have to apologise to mrmattmc for not getting back sooner. Something came up which didn't leave me the time to give this thread the attention it deserves.
I'm still going to be a little pushed for time through until christmas/new year (I will have some time in a few days, but it's only 50/50 I'll have internet access).
If you haven't solved this one to your satisfaction yet could you update me (and since you seem to make good progress, perhaps attach an updated file?).

[mrmattmc]

Its all good p45cal. Life gets in the way of my fun all the time.

I've had a good bit of VBA learning these past weeks. I think I may be an addict now. Where is the warning? VBA may be addictive!

I'm still plugging away at my project. As my code toolbox grows the ideas keep springing up. My project now dynamically pulls data from the source sheets when a user clicks on any of the numbered cells within the defined area. This data is dumped to a hidden area of helper rows that a group of formulas extract from to populate some index card like boxes that I unhide when data is available in the helper rows. This is a bit laborious as I am transposing my data with brute force via formula. Been thinking of adapting a method I saw on the c pearson site for formatting with vba.

Attachment 12587

I then hide all columns except the column for the selected user. I even worked up a couple functions to make the notes box save any data input into the note cell back to the original source data to a note column. This works out to be two separate functions. The write note function and the get note function. Works great.

I am currently working on adding the ability to click on a date and pull all values for the selected date for all names, and outputting that to a set of rows to the right of the cards. All the dynamic data spaces hide and unhide as needed.

I could use some help on my intersect functions. I have noticed a strange glitch where if I click on any cell outside the two named ranges it will trigger the event targeting the "dates" range. Here is my code. I have been reading up on if not intersect vs if intersect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'On Error Resume Next
    dobo1 Target
    dobo2 Target
Application.EnableEvents = True
    End Sub

Sub dobo1(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Dataset")) Is Nothing Then
rw = Target.Row
cl = Target.Column
Idetails (rw), (cl)
End If
Call aenable
End Sub

Sub dobo2(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Dates")) Is Nothing Then
rw = Target.Row
cl = Target.Column
visibletest (rw), (cl)
Else
'rw = Target.Row
'cl = Target.Column
'visibletest (rw), (cl)
End If
End Sub

[SamT]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 Then 'Someday you might modify this sub to handle counts > 1 
       If Not  Intersect(Target, Range("Dataset")) Is Nothing Then dobo1 Target
      If Not Intersect(Target, Range("Dates")) Is Nothing Then dobo2 Target
    End If

    If Target.Count > 1 then
       '
    End If

    Application.EnableEvents = True
End Sub



Sub dobo1(ByVal Target As Range)
    Idetails Target.Row, Target.Column
    aenable
End Sub


Sub dobo2(ByVal Target As Range)
Call visibletest (Target.Row, Target.Column)
End Sub

[mrmattmc]

Thanks Sam

I was toying around with the code earlier and swapped

This

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Dataset")) Is Nothing Then

To This

If Not Intersect(Target, Range("Dataset")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub

and the odd behavior went away.

I do prefer your method of passing the row and column data to the sub. Saves some space. Is it necessary to bracket the row and column reference when using the CALL in the second sub? Seems the method in the first sub is more efficient?

[SamT]

I cannot remember the last time I used "Call" in real life VBA.

If you use "Call" you must bracket the parameters. "Calling" a Function will prevent the Function result being passed back to the Caller, or so it says in the Help files.

I do prefer your method of passing the row and column data to the sub. Saves some space.

Whenever you see a dot (".") in the code, it means the program must access the actual object (Target is a Range Object,) which takes more time that referring to a variable already set to the Propperty, (rw and cl.) A variable takes a bit of memory.

Variables let you make your code self-descriptive.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim EmpNameCell As Range
Dim ActionDateCell As Range
  
  If Target.Count <> 1 Then Exit Sub
  If Not Application.Intersect(Target, Range("A2:G16")) Is Nothing Then
     With Target
        Set EmpNameCel = Cells(3, .Column)
        Set ActionDateCell = Cells(.Row, 1)
     End With
  
     MsgBox EmpNameCell.Value & " " & ActionDateCell.Value
  End If

End Sub

Now that code with tutorial comments

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim EmpNameCell As Range 'FSE 'Emp as mnemonic for Employee
Dim ActionDateCell As Range 'DD 'Cell to indicate a Range.Count of 1
  'If refering to Column("A"), I would use ActionDateRng
  
  If Not Application.Intersect(Target, Range("A2:G16")) Is Nothing Then
     With Target
        Set EmpNameCel = Cells(3, .Column) 'Item is the default property of Cells, ergo is not needed
        Set ActionDateCell = Cells(.Row, 1)
     End With
  
     MsgBox EmpNameCell.Value & " " & ActionDateCell.Value
      'Value is the default property of Range, but I used it to emphasize _
       that "Set" is only used with Objects. Note that "Let" is deprecated, but
      'still allowed on other variables. Note structure of this multi-line comment. (|:>)
  End If
  
End Sub

Let me STRONGLY recommend that you manually add "Option Explicit" at the top of all code pages you intend to keep, and that you use the VBA Tools Menu >> Options >> Editor Tab >>Auto Syntax Check to automatically Place "Option Explicit" there-at. the use of Option Explicit catches many errors for you. It also requires you to Explicitly declare variables.

I personally check all boxes on that tab, except Drag-and-Drop Text Editing. I also change the colors of Comments and Keywords on the Editor Format tab.

[SamT]

The code in my Post #10 has fatal errors. It should read

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 Then 'Someday you might modify this sub to handle counts > 1
       If Not Intersect(Target, Range("Dataset")) Is Nothing Then dobo1 Target
       If Not Intersect(Target, Range("Dates")) Is Nothing Then dobo2 Target
    End If

    If Target.Count > 1 Then
       '
    End If

    Application.EnableEvents = True
End Sub

Intersect returns a Range. The previous bad code treated the return like a Boolean. My bad and I apologise

[mrmattmc]

Sam I really appreciate you taking the time to help me out here. None of my subs are looking to get a result back from the subs they are calling so in that respect I am ok so far.

It sounds like Option Explicit along with error handling is the best way to button up a VBA solution for robustness.

Turns out I was wrong about the previous code change fixing the odd behavior. Further investigation showed an event that was triggering a selection change event. Insertion of a Application.EnableEvents = False in the appropriate line cleared up the problem. I guess that's the price I pay for jumping in and building all these interdependent functions without a good grasp of the fundamentals.

Learning is fun right.

I really should mark this thread as solved if I haven't already.

Using a combination of Target and row/column references as outlined in Post 7 has achieved my stated goal of triggering events based on a cell selection.

I suppose I could include the subsequent autofilter, copy and paste of the retrieved data to the "dynamic" area in case anyone else would like to implement this using my clumsy solution.

pmdetail:
Application.ScreenUpdating = False
Set ws2 = Sheets("PM")
ws2.Activate
With ActiveSheet
    Sheets("PM").Select
    Set rng = Sheets("PM").Range("a:ad")
    Set rng1 = Sheets("PM").Range("b:b,c:c,d:d,e:e,g:g,j:j,l:l,n:n,o:o")
    .AutoFilterMode = False
    .Rows("4:4").Select
    .Range("A4").Activate
    Selection.AutoFilter
        .Cells(1).AutoFilter Field:=5, Criteria1:=">=" & DD, Operator:=xlAnd, Criteria2:="<=" & DD
        .Cells(1).AutoFilter Field:=1, Criteria1:=DIS
         iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
                 Sheets("PM").Columns.Hidden = True
                 .Range("ae:af").EntireColumn.Hidden = False
                .Range("AE4:AF" & iLastRow).SpecialCells(xlCellTypeVisible).Copy
                 Worksheets("DueView").Range("CV" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                 Sheets("PM").Columns.Hidden = True
                 rng1.EntireColumn.Hidden = False
                 .Range("B4:AD" & iLastRow).SpecialCells(xlCellTypeVisible).Copy
                 Worksheets("DueView").Range("CX" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                 Sheets("PM").Columns.Hidden = False
                 Sheets("PM").AutoFilterMode = False  
                 End With
                 Call Hideit
                 Sheets("Dueview").Select
                 Range("cv3").Select
                 Application.ScreenUpdating = True

So there it is. I get the criteria from post 7. An if statement directs the code to go to this routine, if the type is PM the do this. I autofilter the source data for DD(DueDate), DIS(City). Do a quick count to find the total number of rows then hide it all. Next I unhide just the data I want and do a copy using xlCellTypeVisible. Paste the data to the desired destination. As I am doing some columns rearranging here I do this twice. To finish up I call a sub Hideit that does a count of the newly pasted data and hides all the empty stuff.

I have read repeatedly that selecting cells is bad form but I haven't been able to make an autofilter work without first selecting the header row.