Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

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

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by powerranger; Jun 3rd, 2013 at 10:07 AM. Reason: how do I change title?

  2. #2
    Board Regular nuked's Avatar
    Join Date
    Mar 2013
    Location
    London, UK
    Posts
    883
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    ****************************
    I'm good with Excel. I'm not great at:-

    (1) Guessing what solution you want to a problem
    (2) Guessing what your code might be
    (3) Math(s)
    (4) Physics
    (5) Creating systems that attempt to find patterns in lotteries
    (6) Hardware problems
    (7) Responding to questions marked "URGENT!!!!"
    (8) Predicting, at the start, what your future requirements might be - please include your whole requirements at the start

    Thanks. HTH
    ****************************

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    Board Regular nuked's Avatar
    Join Date
    Mar 2013
    Location
    London, UK
    Posts
    883
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    ****************************
    I'm good with Excel. I'm not great at:-

    (1) Guessing what solution you want to a problem
    (2) Guessing what your code might be
    (3) Math(s)
    (4) Physics
    (5) Creating systems that attempt to find patterns in lotteries
    (6) Hardware problems
    (7) Responding to questions marked "URGENT!!!!"
    (8) Predicting, at the start, what your future requirements might be - please include your whole requirements at the start

    Thanks. HTH
    ****************************

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    Board Regular nuked's Avatar
    Join Date
    Mar 2013
    Location
    London, UK
    Posts
    883
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    ****************************
    I'm good with Excel. I'm not great at:-

    (1) Guessing what solution you want to a problem
    (2) Guessing what your code might be
    (3) Math(s)
    (4) Physics
    (5) Creating systems that attempt to find patterns in lotteries
    (6) Hardware problems
    (7) Responding to questions marked "URGENT!!!!"
    (8) Predicting, at the start, what your future requirements might be - please include your whole requirements at the start

    Thanks. HTH
    ****************************

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by powerranger; Jun 3rd, 2013 at 11:50 AM.

  8. #8
    Board Regular nuked's Avatar
    Join Date
    Mar 2013
    Location
    London, UK
    Posts
    883
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    ****************************
    I'm good with Excel. I'm not great at:-

    (1) Guessing what solution you want to a problem
    (2) Guessing what your code might be
    (3) Math(s)
    (4) Physics
    (5) Creating systems that attempt to find patterns in lotteries
    (6) Hardware problems
    (7) Responding to questions marked "URGENT!!!!"
    (8) Predicting, at the start, what your future requirements might be - please include your whole requirements at the start

    Thanks. HTH
    ****************************

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •