Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: need help with VBA code

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The down & dirty....

    I have a register for inventory (in this case, animals), & I'm trying to create a form that will ease the input of new items.

    First, the database....

    Sheet1 has three columns, A, B & C
    Column A is the type of animal (ie, cat, dog, horse)
    Column B is the Breed
    Column C is the Name of the pet.

    On the Userform I'm trying to make, I have two comboboxes (one for animal & one for breed) & one text box that & will type in the name.

    Here is my question. Once I've selected the "animal" (let's say dog, for this instance) from the combobox, what code do I need to only display the "breeds" associated with dogs.

    In more Excel terms, I need for breedcombobox to show only the values of sheet1's column B if column A's value equals animalcombobox's value.

    I'm guessing it's a countif function, but I'm stumbling with no sign of recovery.

    I'm greatful for any replies.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    At the risk of tearing up your project and starting again from scratch, the following might help you out in a non-VB stylee.

    Insert a new worksheet into your workbook. In column A type in the types of your animals (dog, cat, horse). Select the types you've just entered and click on Insert-Name-Define and type in (say) Animal_Types in the top box.

    In column B type in all the breeds of Dog, select the cells, click on Insert-Name-Define and type in Dog. Repeat this in the next couple of columns for Cat and Horse. You can then click on Format-Sheets-Hide, if you don't want people to see your entries.

    Flip back to your entry worksheet, select the whole of column A, click on Data-Validation and select List and type in

    =Animal_Type

    Then select the whole of column B, click on Data-Validation, select List again, but this time type in

    =Indirect(A1)

    Ignore any error messages.

    Now, you should have a drop-down list in A consisting of your animal types, related to a drop-down list in B based on what animal type was entered in the first column. HTH.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I KNOW this isnt the correct way to do this, but maybe someone will let us know. In anycase, this is the first idea that came to mind when you posted this.

    You need to somehow change the values of the "Breed" combo box when "Animal" is changed. The first thing i thought of is use the "Animal" change event (if there is one, im pretty sure there is) and use this to close the form, and reopen it, initializing it with the correct "Breed" options. You can do this using a select Case method one for each Animal.
    Hope that helps, maybe someone will suggest a better way

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd recommend sticking to Mudface's idea, altough the same "effect" can be achieved in VBA
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I'm not very experienced with data bases, but it seems that your structure might be improved upon.

    With the current structure, column A may have multiple instances of the same type of animal which may very well be the same breed.
    In other words you might be able to reduce redundancy by using a different structure

    For example on sheet 1

    Row1:ColA = Dog
    List all breeds of Dogs from A:2 to A:??

    Row1:ColB = Horse
    List all breeds of Horses from B:2 to B:??

    Using this structure the breed would only be listed once.

    I do not know what your purpose is but a good database structure should include a primary key. A unique identifier for each record.

    The name of the animal is the best bet with what you have so far, though you could very well have two German Shepherds named Fido.


    Anyway, when your form activates, place this code in your activate event for your combo boxes

    Your first box appears to be animal type

    Loop across the Column headers to fill in the combobox with all animal types...

    Form has two combo boxes named:
    cmboAnimalTypes
    cmboBreeds


    Place this code in your form module

    When you select an animal type in the first cmboAnimalTypes then cmboBreeds box will
    become populated with every breed you have listed under the column heading of that anumal type.

    I'm assuming you are familiar with VBA at least a little. There is no room for error as this code stands. You will have to spice it up yourself. If you need more help, just repost and someone will help you..

    Have a nice day!

    Option Explicit
    Dim TrackAnimalTypeColumnNumbers()

    Private Sub cmboAnimalTypes_Change()
    Dim AddBreedTypes As Long
    Dim FindColumnOfType As Integer
    Dim FoundBreed As String

    cmboBreeds.Clear
    If cmboAnimalTypes.Text = "Animal Types" Then Exit Sub
    cmboBreeds.Text = cmboAnimalTypes.Text
    FindColumnOfType = 1

    Do Until Sheet1.Cells(1, FindColumnOfType).Value = _
    cmboAnimalTypes.Text
    FindColumnOfType = FindColumnOfType + 1
    Loop
    AddBreedTypes = 2

    Do Until Sheet1.Cells(AddBreedTypes, FindColumnOfType).Value = ""
    FoundBreed = Sheet1.Cells(AddBreedTypes, FindColumnOfType).Value
    cmboBreeds.AddItem FoundBreed
    AddBreedTypes = AddBreedTypes + 1
    Loop


    End Sub


    Private Sub UserForm_Activate()
    Dim CountAnimalTypes As Integer
    Dim AnimalType As String


    cmboAnimalTypes.Text = "Animal Types"
    cmboBreeds.Text = "Select Type"
    CountAnimalTypes = 1

    Do Until Sheet1.Cells(1, CountAnimalTypes).Value = ""
    AnimalType = Sheet1.Cells(1, CountAnimalTypes).Value
    cmboAnimalTypes.AddItem AnimalType
    CountAnimalTypes = CountAnimalTypes + 1
    Loop

    End Sub






    [ This Message was edited by: TsTom on 2002-03-23 14:51 ]

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface,
    Thanks for the input, but I'm afraid that I made it a little too simple.

    Column A is actually a customer number
    Column B is an inventory number

    The customer number (column A) is unique to the customer.

    The order number is a product number found in the company inventory. This way we can easily identify who is getting what.


    So, let me try ot explain this. When John Doe calls in his first order, he is assigned a customer number (let's say it's 123). The first record for him would be 123-esd (Customer 123 - product esd).

    If he calls in another order, the order number is something like 123-qaz.

    Now when Ronald Reagan calls in his first order, since he's assigned as customer number of 456, his order is something like 456-drg.

    ~~~~~~~~~~~~~~``

    As you can see, the order ledger will be pretty dynamic.

    Ultimately, When a customer number is selected from the "custno" combobox, I need for the "orderno" combobox to be populated with only the orders placed by that customer. Shed more light?? Sorry for not being more indepth earlier.


  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    SNIP

    Mudface,
    Thanks for the input, but I'm afraid that I made it a little too simple.

    Column A is actually a customer number
    Column B is an inventory number

    The customer number (column A) is unique to the customer.

    The inventory number is a product number found in the company inventory. the customer number & the inventoery number combined create the order number This way we can easily identify who is getting what.


    So, let me try ot explain this. When John Doe calls in his first order, he is assigned a customer number (let's say it's 123). The first record for him would be 123-esd (Customer 123 - product esd).

    If he calls in another order, the order number is something like 123-qaz.

    Now when Ronald Reagan calls in his first order, since he's assigned as customer number of 456, his order is something like 456-drg.

    ~~~~~~~~~~~~~~``

    As you can see, the order ledger will be pretty dynamic.

    Ultimately, When a customer number is selected from the "custno" combobox, I need for the "orderno" combobox to be populated with only the orders placed by that customer. Shed more light?? Sorry for not being more indepth earlier.


  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I might be censured for this, but that's just ridiculous. Your original post bears absolutely no relation to your actual requirement, and several people have spent time trying to code for your original question.

    I can sort of see what you want and it might be simply achieved by an Autofilter but I'm reluctant to answer in case your criteria change again. Again, I might be hit over the head with this, but you should repost and give as many details as you can (is the customer and order combined in column C, what you want your userform to do for example).

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I agree with Mudface
    Heck, I thought you were a veterinarian or something and now I feel quite sheepish. Will go elsewhere.

    Good Luck

  10. #10
    New Member
    Join Date
    Feb 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry for pissing in everyone's wheaties, but I have asked the question a couple of times before using the customer-product format only to get replies that are insulting instead of helpful.

    Maybe when I understand everything there is to know about VBA I can come back to ask questions, but that seems redundant.

    Thanks for your help.



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
  •