MS Excel - Trap delete range and underlying query warning

[Jean9]

How can one trap the event when the user attempts to delete a range and thereby the underlying query on a worksheet? Currently, the default warning message (The range you deleted is associated with a query that retrieves data from an external source. Do you want to delete the query in addition to the range? If you click No, the query will retrieve new data to the worksheet the next time the query is refreshed) appears that is all too easy for a user to click "Yes" on thereby deleting the query. I want to, via code, capture when the user would get this message and delete the data that they are trying to without deleting the underlying query (In effect, answering "No" to the warning FOR them)....anybody know first how to trap this warning and second how to clear the contents of the selected range without deleting the query?
Thanks in advance!

 

[combo]

I doubt that without some protection and operating via code you can lock this message.

Having in mind that the query name can be set (here "TestQuery") and it's a local (worksheet) level range name, you can consider (both code snippets in sheet's code module):
1. trapping whole range selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Me.Names("TestQuery").RefersToRange.Address Then
    MsgBox "Whole external data table range selected"
    Target.Cells(1).Select
End If
End Sub

2. undo action if the name does not exist:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Name
On Error Resume Next
Set n = Me.Names("TestQuery")
If Err.Number <> 0 Then
    Err.Clear
    MsgBox "Anyway, the underlying query will be restored"
    With Application
        .EnableEvents = False
        .Undo
        Me.Names("TestQuery").RefersToRange.ClearContents
        .EnableEvents = True
    End With
End If
End Sub

combo

 

[SkipVought]

hi,

I use querytables a lot. What a great feature in Excel!

Is it possible for you to HIDE the sheet containing the QT and reference the data in some way for the user to "play" with?

I would also capture the VBA that creates and refreshes the QT. Use you macro recorder. Paste back your recorded code for help customizing, if need be. If the user deletes the QT, it can very easily be re-added, via your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Jean9]

@Combo - you're probably right, I wouldn't be able to trap that warning. Maybe I moreso would rather just catch whenever the user chooses a delete action and then only delete the selection and not the queries...

@Skip - Your "hide the query sheet and reference it for the users to play with", is an interesting idea...
So keep some hidden worksheets in the workbook (one for each query)
Refresh the queries on the hidden worksheets
Delete all previous data on the visible worksheets?
Then copy the data from the hidden worksheet to the visible worksheet?

Something like that or what is the best way to reference the data on the hidden worksheets (keeping in mind that the number of rows returned by the queries will change as they change the data being queried against)?

 

[SkipVought]

What is it that the users need from any query?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Why do you have multiple queries?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Jean9]

The users have a worksheet in the workbook that contains thousands of rows of data. Based on particular criteria, if the row of data meets that crtieria they want it "moved" to a sheet containing all the rows meeting that criteria so they can do some further manipulations. They have 9 worksheets that contain the query results meeting differing criteria so, the macro loops through "changes" 9 different data connections each time it runs (thank you for that code, btw) and if the user had deleted the query from the sheet since the last time the macro was run, the code is written to add a data connection; but formatting gets flaky when the macro runs straight through on the add connection so I REALLY would prefer to keep the user from EVER deleting the underlying queries to begin with. (Breathe...Longest sentence EVER...)

So, your idea sounds like that would work, what they don't see, they won't mess with. Once the queries have been refreshed, so to speak, should the data be copied out to the visible worksheets or as you said earlier, referenced in some way?

The code you gave me for modifying a connection will work for the refreshing of the connections on the hidden worksheets, I just need to know what the best method of referencing the data on the hidden worksheets would be and how to do so.

 

[SkipVought]

Hmmmmmm? The info given reminds me of the group of blind scientists describing and elephant.

So do all these worksheets contain data from the same source but each has data that has been filtered by a different set of criteria? If so, how many fields/criteria variables?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You can use parameters in your database query. Add filter in the msquery window, [parameter_name] instead of value. Next, in excel, configure parameters so that they appear in the worksheet. You will get directly filters and a filtered recordset in the workbook.
Depending on the data, you can also use pivot table to extract some information, page fields are handy filters.

combo

 

[Jean9]

BTW, the users are on MS Excel 2003 for an indeterminate amount of time...
@Combo, not sure if that will work for the scenario...but if it seems like it might, then I might need some clarification as to how this can be accomplished

@Skip, All worksheets contain data from the same source but each has data that has been filtered by a different set of criteria, true, BUT see below.

I just thought of this, tell me what you think...One hidden worksheet with one data connection that is re-used. The query runs, the data is refreshed, the results copied from the hidden worksheet to the visible one and then loop back through those steps another eight times. Because once the query is run and the data has been captured and copied to a worksheet that the users can manipulate, that query definition is no longer needed until they run the macro again (the sql for the query definition is already in the code soooo...).

To your "If so, how many fields/criteria variables?", don't even get me started...short of the long, I hope....Each set of criteria has to contain its own criteria and the opposite of the previous criteria in order to avoid selecting records that were previous selected...I'm only going to paste the last query's criteria so you get the gist...

    "WHERE (AllData.Fund_Status<>'I') and (AllData.COA_Dist_Status<>'I') " & _
    "AND (AllData.COA_Dist_Status<>'') and (AllData.Exception_Codes = '') " & _
    "AND (AllData.Payout_Frequency<>'U') " & _
    "AND (AllData.FDN_Fund_Code LIKE 'W%') AND (AllData.Priority_Num = 0) " & _
    "and (AllData.Regents_Fund <>'A2001') " & _
    "AND (AllData.Regents_Fund <>'A2002') AND (LEFT(AllData.Regents_Fund,3)<>'CAA') " & _
    "AND (LEFT(AllData.Regents_Fund,2)<>'IH') " & _
    "ORDER BY AllData.FDN_Fund_Code"

AllData is the named range on a worksheet that contains...you guessed it, all the data on the worksheet

 

[SkipVought]

Is it possible to present 3 comboboxes, each one containing the value list for one of the three criteria.

When the user makes the selections, then the query can be executed, using those selected values.

If this would work, then would ALL returned rows go into that user's worksheet, or would this QT be the final product?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Jean9]

The user doesn't want to have to do anything other than run a macro and the (9) worksheets be populated.

 

[SkipVought]

...and then LOOK for a bunch of stuff. Lots of manual effort, seems to me.

What I am proposing may possibly make the process more efficient for the user and return all the necessary data in ONE SHEET.

I dunno, it's the part of the elephant I seem to observe.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

First of all, I agree with Skip that the first step should be to protect the data in a hidden (or veryhidden) worksheet.
If the next step is to run macro to process data it's simple, you have a named range that can be analysed.

In case of more manual work I meant usage of parameters in the process of extracting data. For an external data query in the visual query designer (in ms query) one can use parameters in filters. After importing such data to the worksheet the "parameters" button becomes available, it is possible to choose the way they are picked. If you point to aspecific cell, its value will be used when refreshing data.


combo