Excel List Box Capabilities 2

[jfussell]

I have created a timesheet in Excel, and I would like for the spreadsheet to pull project numbers from an Access Databse. At first, I tried using the data validation function in Excel, but then I had to export the Access Data onto another spreadsheet for excel to query the project #s, and if the project number increases, I would have to redefine the names everytime. Is there a way to do so directly from Access without exporting access into excel first? I'm not familiar with Visual Basic so I don't know how to use the list box form. Please help!

 

[SkipVought]

j,

Here's what I am doing in my current project. I have several Comboboxes for the user to select a value contained in a table.

For instance the first combobox list Products. As a result of the Product selection, I query the table for a list of contracts associated with the selected product, and display that list in the second combobox.

Here are the elements of the process, which, BTW, uses VBA.
1. The combobox clck event assigns the selected value to a worksheet cell...

sub combobox1_click()
  with combobox1
    .topleftcell.value = .value
  end with
end sub

2. Use a separate sheet for each query using Data/Get External Data. Set the query up with your Access database to return a SINGLE column with the appropriate criteria. Return the data to Excel. THEN turn on the macro recorder and EDIT the query and return to Excel (you don't actually do anything except get in and out). Turn off the recorder. Then edit the recorded code to substitute the previous selection value for the har-coded value in the SQL array string. In this way the sheet-cell selection will be substituted in the query.

3. Either use a button click event to trigger the query or you can use the Worksheet_change event, but this is alot trickier since there are some pitfalls that come with recursive worksheet change events.

Maybe if you can get this far, (or not) you can post back and get some further guidance.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[jfussell]

Skip,

Thanks for the reply. Now that I read back on my message, I don't think I was very clear on my question. So, let me try once again.

I'm trying to create individual timesheets for 7 employees (all the same info though). I started out with a separate hidden worksheet (same file) that queries my project numbers from my access database. Then define name and data validation, so that the timesheets' drowndown listbox would only pull the active projects upon each file opening. I was able to get this working, however, I noticed that when projects become inactive, the listbox would still reflect a blank space even though I had checked the "ignore blank" in data validation; or if I have added on more active projects, I would have to change all 7 hidden worksheets to redefine the name area. That's why I was trying to bypass the query worksheet all together, and to see if the listbox function could query that automatically for me.

I hope this time I was able to describe my problem/question a little better. And perhaps your suggestion above would solve my dilemma, and if so, please let me know and I will try the combobox format. Thanks again.

 

[SkipVought]

Use the OFFSET function in Insert/Name/Define to define the named range. Then the named range will be dynamic

faq68-1331 How can I rename a table as it changes size

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Bowers74]

Play around with the SQL.REQUEST() function in Excel. You just might find it helpful!

Here is a FAQ that will lead you to a good learning example for the SQL.REQUEST() function:

faq68-4089 - SQL.REQUEST function for Excel

I hope this helps!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[jfussell]

The OFFSET function worked like wonders! Thank you so much Skip. :-D

Also, thanks for the SQL.Request() suggestion Mike, however I think I had read on your FAQ that everyone would need to have Access in order for this request to work, and unfortunately that isn't the case, so that's why I'm sticking with using Excel as the timesheet. Thanks though! I'll try it out if I run into other problems in the future.