Posts by Foxxy1

Re: Advanced filter criteria

Sub Filter()
    ActiveSheet.Activate
    ActiveSheet.Range("E:E").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="" & ActiveSheet.Cells("B4").Value
End Sub

I tried this code and it didn't work, it kept catching on the last line. I changed it from the original code a little because I really only need to sort by the 1 column. :0ops: my mistake. I'm not sure what else would be wrong.

Re: Create pull down lists dependent on eachother

Thanks Steve, and welcome to Ozgrid. The problem with the autofilter is (and this maybe a problem with me) that I don't know if I can set it up as code and make it recognize data within a certain cell. If I can, I think that it would work, theoretically.

Re: Worksheet_change updates on any cell, I require a range

You know I got to messing around with this a bit more thought about having a different bit of code run when a different range was modified. Is this possible, what would the code look like. I'm also thinking I'd run into a nasty loop if I said everytime "G1" changes paste "A1" into "B4", is there a way around that also?

I need to filter 3 columns (c:e) on my sheet called "List". I want the value in cell B4 on my sheet "Combos" to be the criteria for the filtering. So my result will be all the rows that have the same value as B4 copied to columns g:i. Let me know if this doesn't make sense, and thanks for the help.

Re: Worksheet_change updates on any cell, I require a range

Gee, thanks!

For some strange reason the following code is not working as I hoped. I want the code to run when only certain cells are changed (B4:B6). It runs though, when any cell is changed. What am I missing?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    Set rng = Combos.Range("B4:B6")
    With rng
        'This is just a simple code to use as a test
        Range("A1").Copy
        Range("G1").Select
        ActiveSheet.Paste
    End With
End Sub

Thanks for the help.

Re: Create pull down lists dependent on eachother

You know it's funny, I tried the dynamic named range formula and it didn't work with a combo box but it did with a data validation cell. Is there a reason why? To be honest I don't totally follow having two rows, each one setup for double dependency. I'm not sure where you're going with it.

Re: Create pull down lists dependent on eachother

No worries, I appreaciate all the help. I wasn't sure, but I thought that what I (I say I, but I mean my boss, I think what we have is fine!!!) was looking for might require macro and combo boxes, etc.

Re: Create pull down lists dependent on eachother

I've seen a few different formulas for the dynamic lists. I was just going to worry about that when I got further along due to the fact that it would just complicate things for me for now.

You're kind'a close with your guess. Think about my example with the cars though. If I knew I wanted to get a Honda I would choose that first, and then could decide what kind of car I would want, like a 4 door Mid-size, it would then leave me with only so many models, in this instance , the Accord. However, if I knew right away that wanted the F-150, there would be only one manufacterer and one description. That's why I want those to automattically show up, just like the vlookup in the second row held in the ExampleA file. I'm not sure if doing this is an actual option, but it would be nice, otherwise, if it limited the choices to the only choice it could be, would need to be in place though. Thanks again for your time and help.

Re: Create pull down lists dependent on eachother

It's cool, right now I'm just more concerned with getting a better understanding how this stuff works, that's why I didn't really bother too much, but maybe that's part of the problem.

Re: Create pull down lists dependent on eachother

I looked at the link and one of the other related threads up at the top and found this to be quite useful(yes, I swear I did a search first). However, I tried to do the same in a third column, dependent on the second list, and it did not work. It said I was evaluating to an error. This was only part of what I need to do anyway. The other part was that I still need to reference what was also in the first column. After I get all of that taken care, I still would like to figure out a way to get the first two columns to recognize when I enter data in the third column first, that they need to update automattically to the proper value. I've attached something again to show where I'm at with it now, and sort of where I want to go with it.

Re: Create pull down lists dependent on eachother

I created this generic example sheet of what I kinda have now to hopefully help explain it better.

I have to admit, I don't really know what I'm doing on this. I showed my boss something I have been working on and he liked it, but he wants something different. Right now I'm using data validation in several cells in a column. Through the vlookup comand, depending on what is chosen, the coresponding data is shown.

So let's say my current setup is model#, description, and last manufacturer. The model# cells contain the data validation and once a choice is made the description and manufacterer is listed also. He wants to have a list in the other areas where he could choose which manufacturer he wants to use or what the decription would be. This would obviously limit the choices for the other fields. If I chose Honda as a manufacturer, I would want to see things like civic, accord, s2000, not viper, focus, or camaro. Maybe I'm not thinking at this hard enough, or am over thinking the issue, but I don't even know where to begin on this. is data validation still the best choice, what about ActiveX controls like combo boxes. On top of that, the lists get be dynamic! Any suggestions or help would be appreaciated greatly on my part.

Re: auto activate macro

Ah, much better! Thanks for the tip on the right hand drop down, looks pretty darn easy compared to having them all memorized. Thanks for all the help, this one's done.

Re: auto activate macro

Private Sub Workbook_Open()


    Application.ScreenUpdating = False
    ChDir "Y:\Database\Pricing"
    Workbooks.Open Filename:="Y:\Database\Pricing\Pricing.xls"
    ActiveWindow.Visible = False
    ChDir "Y:\Database\Contacts"
    Workbooks.Open Filename:="Y:\Database\Contacts\Elecrtical_Contractors.xls"
    ActiveWindow.Visible = False


End Sub
Private Sub Workbook_Close()


    Application.ScreenUpdating = False
    Windows("Pricing.xls").Visible = True
    Workbooks("Pricing.xls").Close False
    Windows("Elecrtical_Contractors.xls").Visible = True
    Workbooks("Elecrtical_Contractors.xls").Close False


End Sub

This is what I currently have, but it doesn't close the files out when I close the file that the code is in. I'm not really familiar with this area and obviously it's not right or else it would work. What's wrong? It is in the "ThisWorkbook".

Re: auto activate macro

maybe I don't uderstand so well, I got what was said about naming the macro "auto_open", and that worked great, but I don't really know anything about events and how to set them up or where they go. If you can help explain a it a little better I'd greatly appreciate it. Thanks again for all the help.

Re: auto activate macro

will this make it close when my workbook that contains the link is closed?

Re: auto activate macro

K, that works great and this stuff is making a whole lot more sense now, but is there a way to close my initial document and close the secondary file(s) automatically without being asked if I wish to save the secondary files

Re: auto activate macro

That sounds like a good idea but I'm still not sure how to make the macro work autimatically.

Re: auto activate macro

The problem is that i'm trying to create this mater file without actually having to open it each and every time I open the other file that needs the list. Thanks.