Help me apply an online excel as a database macro to my spreadsheet

powerranger

New Member
Joined
May 20, 2013
Messages
16
I have been looking for a way to make a friendly interface pulling data from a 'database' spreadsheet via a filtered userform query and found a tutorial on a website that offers pretty much what I need (except Im gonna add code so Id be able to filter by more than just 3 fields). Problem is I am completely ignorant when it comes to VBA so can someone point me at the right direction. Here's the tutorial Using Excel As Your Database | Chandoo.org - Learn Microsoft Excel Online .

What I need to apply it to my spreadsheet is a way to not only select from the dynamic lists, but code I can use for columns that are filled with numerical data so say you can filter to show all rows with <0.20 in a certain column.

Also a very stupid question but where are the actual buttons placed in the code? I sort of get the hierarchy and logic within the code and with some trial and error might be able to suit it to my needs but dont see how and where are the actual buttons placed? E.g. if I added buttons in a new macro there would be a userform subsheet in the VBA tree and there's no such thing in the given example?

Sorry if it sounds confusing, as I said I am embarrassingly ignorant. Any help is MUCH appreciated.

EDIT: How do I change the title as I made a mistake?!
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Well once you've set up the connection to your Excel 'database', then you're going to need to learn SQL which is used to query said dataset. This will enable you to filter by your numerical columns, for example.

SQL Tutorial
 
Upvote 0
Could you possibly give me more guidance if I share an example of my spreadsheet fields with you? I dont expect you to do all the work, just a direction.
 
Upvote 0
Forgive me for saying this, but I think you should perhaps learn to walk before you can run.

I think that's the best direction I can give - it sounds like you are embarking on what is a fairly advanced project in Excel, without an apparent understanding of how to code basic VBA.

I'd recommend against this.

I might be wrong but I'm not sure you're going to get the step by step walkthrough that you want from this site, given the gap between what you know and what you need to know to achieve this.
 
Upvote 0
That's a fair remark really...

I'll try and leave that approach altogether and try something different with only a bit of VBA. Was thinking about perhaps linking auto-filter to textboxes/lists and hiding all rows until filtered so when people use the boxes to filter it is going to look similar to what I'm trying to achieve.
 
Upvote 0
If you're just after things to expand your understanding of VBA then fine, but if this is actually a problem that needs solving, it sounds like this can be achieved using Pivot Tables or even array formulas. The latter, if you're just after the aggregated figures; the former if you need to be able to drill down into the figures to see the records behind.
 
Upvote 0
Ive got a pivot table which is useful when analysing the data but I do need an easy way for other people to just get the data. Ive tried some array formulas but it was too complicated and didnt manage to get it to work.

I found a formula that works quite well.

Private Sub CommandButton1_Click() ActiveSheet.Range("$A$2:$P$611").AutoFilter Field:=2, Criteria1:="=" & "*" & UserForm1.TextBox1.Value & "*" _
, Operator:=xlAnd
End Sub

The way I envisage it is adding similar formulas to buttons with text boxes/lists above the columns I need filtering so its gonna make for a more 'flashy' filtering to get the desired data.

Now question is how do I get boxes and buttons within the cells themselves or can I only do it as a userform (separate pop-up window)?

And also more importantly what would the above formula look like in the case with the numerical data where I want two boxes for people to type in a range, e.g. between 0.10 and 0.70, or perhaps below/above a certain number?

Thanks, really appreciate your patience.

EDIT: I had the developer bar deactivated doh... I can add buttons now.
 
Last edited:
Upvote 0
I suppose you could use Data Validation to create in-cell drop downs to contain the 0.2 to 0.7 values. A button with similar codes to yours can be placed next to them.


Code:
ActiveSheet.Range("$A$2:$P$611").AutoFilter Field:=2, Criteria1:=">" Range("A1").value , Criteria2:="<" Range("A2").value

Etc
 
Upvote 0
Ok but now I have a problem when trying to use that formula on the in-cell text box and button.

That's the amended code
Private Sub CommandButton1_Click() ActiveSheet.Range("$A$2:$P$611").AutoFilter Field:=2, Criteria1:="=" & "*" & TextBox1.Value & "*" _, Operator:=xlAnd
End Sub

and I'm getting a ''Compile error: Expected: End of statement'' with ActiveSheet getting underlined...
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top