Doing a query of data with a dialog box in Excel

Topic

New Reply

Well, Im impressed with the disposition of the people here and their mastery of Excel. Thanks! 2nd question here and it’s kind of vague and exploratory.

I’m exploring using Excel as a database but would like to know if you can create a dialog box in VBA which might permit me to do a query in Excel and dump the results in a particular range or even another worksheet.

As an example, say, I have a table with employees and their rank in sales for every month.

Column A would have the employee names. On Column B would have employee location. Columns C onward would have the months of the year, Jan, Feb, Mar, etc…

If I have 100 employees, then every cell within the table would show their rank in sales for that month, going from No. 1 to No. 100.

On another worksheet I would list the locations on Column A, and the next columns would list product types, say computers, desks and pens, for simplicity’s sake, which would occupy Columns B, C, D. Each location would have the amount of items sold of each type of product in the corresponding cell.

What I’m wondering is if there’s code, or a dialog box I could search in that I could say something like:

1. Go to the locations table
2. Find all locations where – at least 50 computers were sold and 25 desks were sold
3. Go to employee table and get me the employees whose rankings were in the Top 25 at those locations which met that criteria.
4. Print, or dump, the results into a specified range or worksheet

So, for example, I might end up with the following output:

John Dow 4 17 23 21 13
Bob Smith 11 16 22 6
Mark Jones 3 8 4

Because all the people named worked at locations which met the criteria and the numbers beside their names indicate their rank each time they were in the Top 25 of sales for the months where they were (!) in the Top 25. John was in the Top 25 5 times in the year, Bob was 4 times and Mark was 3 times.

I know this is probably pretty complicated. Right now the easiest thing would probably be to have a dialog box which would let me choose the criteria, but if VBA code works, I’d be so (!) happy to try it!

For whatever it’s worth, I’ve attached a 2003 file with some dummy data to illustrate what I’m thinking. It shows a section called ‘Report’ where I’ve slotted in the desired output. I know it’s all kind of unpolished, but I’m just trying to flesh out an idea.

I probably should be using a database for this but I’m just wondering if this can be done in Excel.

I don’t know anythng about Excel dashboards, or how to build them, but maybe that’s what I’m describing. I just don’t know and wanted some ideas.

Thanks, folks!

Reply | Quote

Replies

Hi

Yes, this can be done.
Will post something back here when I’ve thought about it a bit more.

zeddy

Reply | Quote

Just wanted to say thanks to everyone who is participating in this thread. In my Intro to Computers class today, we are talking about Excel Macros and VBA, and I used this as an example of a real life macro that was designed to save someone time. We didn’t get into the specifics of the workbook, but just reading the initial plea for help was enough to get them to understand the enormity of the emailing task, should they have been trying to accomplish it manually. It’s a great scenario, so thank you for asking the initial question!

Reply | Quote

Just wanted to say thanks to everyone who is participating in this thread…

Nice note, thanks…

Reply | Quote

Hi olives

See attached version 2.

I have added a routine to ‘erase all numbers greater than 25’. Instead of reading all cells in the range one-at-a-time, it is more efficient to read the data into VBA in one go, make the required adjustments, and then write the data back to the worksheet in a single operation (rather than cell-by-cell).

After these values have been removed, the existing conditional formats would now make all these empty cells ‘green’ (since an empty cell is ‘less-than-26’), so I added a line to remove the conditional formats as no longer required.

Populating the database basically involved sorting a column of numbers 1 to 100 into a random order and then ‘pasting’ these into the month columns. I ‘preserved’ your initial values for the first 4 records. I used my Sample Excel Data file (posted in a previous thread) to find some random names and Locations.

I have added a routine which extracts non-blank values from the month columns, into adjacent cells as requested. It uses a devious transpose trick (because Excel VBA doesn’t like shifting cells to the left after deleting blank cells, but will happily do a shift-cells-up).

I noticed in your example file that you are familiar with the use of double-click events on your sheet [Location Sales]. So I added my own favourite double-click event for the header row on sheet [Employee Sales By Month]. If you double click any cell in the header row, it will sort on that particular column. Double-clicking the chosen header cell again, will reverse the sort. Double-clicking in cell [A1] will put the records back into original order. This sorting is useful to see who had the most-times-in-top-25 (by double-clicking in cell [S1]) Or you could sort by Employee name, or by Location etc etc. with just a double-click.

I documented each line of code to help others following this.

zeddy

Reply | Quote

Hi olives

..so just to be clear, in the version2 file, click the button [1. Click here to..], and then click the button [2. Click here to..]

zeddy

Reply | Quote

..so just to be clear…

zeddy I’ve been playing with the sheet a little, I’m making the columns on the [Location] tab sortable, too, for example, which I love being able to do and I have some questions about the buttons and the sequence…

1. If I wanted to, I can move the 1st button, the one that sets the criteria, or a copy of it, to the first tab [Employee Sales], right?

2. I just realized something stupid in my workflow… If I clear all the vales ‘>25’, I can’t save the worksheet without losing my original data, with the ‘<25' values, right?…

3. The first time I used it, I

– clicked to extract the locations on the [Locations] tab
– clicked on the 'filter by location' on the [Employee Sales] tab
– clicked on the 'clear top 25' button
– then clicked on the 'extract no-blank values' button

When I hit the last button in the sequence, which is pure eye candy for me! :), the one that 'extracts non-blank values', the results revert to the full employee list. I did it in the other sequence, and I got what I wanted. That's really not a problem, but I just have to make sure I do the steps in the right order, right?

4. I added a MIN function on each row, at the end of the 'Extracted values' column (Column AF), but when I try to sort the extarcated 'non-blank' table by that column, by double-clicking on it (Column AF), I get an error in the code. I've highlighted (well, formatted it in red 'cause I don't know how to highlight it) the display I get as a bug below. I have no idea what to do to fix it…

'The following routine sorts the data records when any column header
'in row 1 is double-clicked.
'Double-clicking again will reverse the sort.
'Double-click in column [A] to sort the records back into original numeric order

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("1:1")) Is Nothing Then Exit Sub
Cancel = True 'cancel normal double-click-to-edit-cell mode

If [u1] = 0 Then 'fetch value from this cell
Target.Sort key1:=Target.Offset(1), order1:=xlAscending, Header:=xlYes
[u1] = 1 'change value to allow reverse sort on next double-click
Else 'sort descending..
Target.Sort key1:=Target.Offset(1), order1:=xlDescending, Header:=xlYes
[u1] = 0 'change value to allow reverse sort on next double-click
End If

End Sub

Reply | Quote

Hi olives

1. If I wanted to, I can move the 1st button, the one that sets the criteria, or a copy of it, to the first tab [Employee Sales], right?

..you can make a copy of that button and put it on the first sheet, but it will still take you to the [Location Sales] sheet where the actual product criteria values are entered.

2. In the existing version, once you clear values > 25 , the original data is gone. But you could make a copy of the sheet first, before clearing the > 25 values. Or the VBA could be amended to make a copy of the original data to a ‘hidden’ sheet if required.

3. Yes, you cannot run the [2. ..extract blanks ..] when the list is currently filtered. But you could then just click the [Click here to filter..] button again to apply the filter again. I made sure the ‘extract blanks’ routine worked even if you pressed that button first.
NOTE: Once the ‘extract blanks has been run, you don’t need to click it again. You can change your product criteria on sheet [Location Sales], return to sheet [Employee Sales By Month] and then just click the ‘filter Employees..’ button.

4. I added a MIN function on each row, at the end of the ‘Extracted values’ column (Column AF),

..there is a min function already in column [Q]. Double-click on cell [Q1] for showing best rankings.

The reason you get a debug error is probably because there is an empty cell immediately under the double-clicked cell. Sorting on a column won’t work on any double-clicked column header if the cell under it is blank.
(In VBA, in the top panel toolbar, select Run>Reset to clear the error.)
Update that code with this:

Code:

'The following routine sorts the data records when any column header
'in row 1 is double-clicked.
'Double-clicking again will reverse the sort.
'Double-click in column [A] to sort the records back into original numeric order

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("1:1")) Is Nothing Then Exit Sub
Cancel = True           'cancel normal double-click-to-edit-cell mode

On Error Resume Next    'skip if error encountered
If [u1] = 0 Then        'fetch value from this cell
Target.Sort key1:=Target.Offset(1), order1:=xlAscending, Header:=xlYes
[u1] = 1                'change value to allow reverse sort on next double-click
Else                    'sort descending..
Target.Sort key1:=Target.Offset(1), order1:=xlDescending, Header:=xlYes
[u1] = 0                'change value to allow reverse sort on next double-click
End If
On Error GoTo 0         'reset error trap

End Sub

..now double-clicking on an invalid heading will have no effect.

zeddy

Reply | Quote

..you can make a copy of that button and put it on the first sheet…

Crystal clear zeddy, you’ve made me so happy! I would buy you a beer, or two, after work if I could!

Reply | Quote

Hi olives

That is very kind of you!

Unfortunately, I accidentally swallowed some Scrabble tiles.
My next cr4p could spell disaster.

zeddy

Reply | Quote

… I added my own favourite double-click event.. sort on that particular column….

zeddy, can I hug you?… It’s just beautiful… I’m just reading this after having posted the other thread, I can’t wait to show version 2 off to the people at work tomorrow… You make me look good! Thanks…

Reply | Quote

Zeddy, Might I suggest this because it will allow clicking on any cell. It also doesn’t rely on u1 but on the min/max in the column.
‘===========
option explicit
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim tc As Long
tc = Target.Column
If Cells(2, tc) > Cells(Rows.Count, tc).End(xlUp) Then
Target.Sort key1:=Target.Offset(1), order1:= _
xlAscending, Header:=xlYes
Else
Target.Sort key1:=Target.Offset(1), order1:= _
xlDescending, Header:=xlYes
End If
Cells(1, tc).Select
End Sub
‘========

Reply | Quote

.. it will allow clicking on any cell…

shoe, what does this do… let me do the sort by clicking on any cell in a particular column?…

Reply | Quote

Your sample file is lacking info needed. Pls provide a file with before/after worksheets along with the logic of where to where.

Reply | Quote

Your sample file is lacking info…. Pls provide… before/after worksheets along with the logic of where to where.

I don’t know if this is what you need, but the file has two worksheets 1) Employee Sales By Month and 2) Location Sales. Both of those are raw data. That is the ‘Before’. The 1st tab – Employee Sales By Month – has a section off to the right with a heading called ‘Report’ (Column W). The names in that section are followed by each employee’s ranking in the Top 25 they had throughout the year. That section (the ‘Report) is the ‘After’. That ‘output’ matches the criteria I set out in my OP.

zeddy did a beautiful job of populating the database (how did you do that zeddy?) so it may be clearer from his example worksheet. I’ve asked him (above) for a macro to delete all values greater than 25, so I’m a little closer to my ‘dream’ output.

The next step might be doing another macro that ‘joins’ the displayed Top 25 rankings so there’s no empty cells between them, as it appears on my orginal DEMO file. Let me know if you understood or if you’d like directions in the spreadsheet itself… Thanks, shoe!

Reply | Quote

Hi olives

See attached file.

NOTES:
1. On sheet [Location Sales],
enter your required products criteria in cells [i2:k2]

2. Click button [Click here to ..]
matching locations will be output to column [O],
the range name “critLocation” will be updated for matching Locations that meet your products criteria.

3. Switch to [Employee Sales By Month]
Click button [Click here to filter Employees..]

The data range has conditional formats which show rankings 1 to 25 as ‘green’
(you can delete columns [W:AD], these shown for reference to your first sample)

I added more sample data and locations, for testing.

zeddy

Reply | Quote

… See attached file… I added more sample data and locations, for testing….

zeddy… it looks beautiful… you’re an artist! I like it… I’m in awe! Beautiful! One more question, zeddy, I’d like a macro to ‘erase’ or delete all the values ‘greater than 25’ in the sales rankings… how do I write one?… I’ve recorded macros to subsitute numbers with “”, a null character, but wouldn’t know how to say ‘erase all numbers greater than 25 for the selected table… I know I can ‘hide’ them with conditional formatting but I’d want to ‘erase’ those values so I can cut or paste to manipulate further… Thank you, zeddy!

Reply | Quote

Hi Don

That is brilliant!
I shall be using that better method from now on.

However, I still prefer to have my double-click-sort based only on the header row, because I use the double-click below the header row for other purposes. Basically, when the ‘computer’ makes ‘suggested’ changes to certain cell values, I have the cell automatically colour-coded to indicate it is a ‘computer-generated’ value. They can acknowledge ‘acceptance’ of the suggested value by double-clicking the cell, (which then changes the cell colour), or, by ‘manually’ entering their ‘own’ value, the cell colour is also changed etc etc.

zeddy

Reply | Quote

Why is that OP’s always think they need to change it??? Why add the MIN in each column at the bottom? My code allows a double click in any cell in the column. It will, BY ITSELF, see if the 1st row of data is larger than the last row of data.
Zeddy can respond to the rest.

Reply | Quote

Don

nobody is adding a MIN in each column.
olives added a MIN for each row.
The MIN was to get the ‘best ranking position’ for each Employee.
It has nothing to do with the Sort by double-clicking!
Your sort method is still the best.

zeddy

Reply | Quote

http://windowssecrets.com/forums/images/smilies/eek.png

http://windowssecrets.com/forums/images/smilies/redface.png

Reply | Quote