need help with VBA code

[Mark Lowe]

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.

[Mudface]

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.

[robfo0]

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

[Juan Pablo González]

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

[Tom Schreiner]

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 ]

[Mark Lowe]

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.

[Mark Lowe]

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.

[Mudface]

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

[Tom Schreiner]

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

Good Luck

[Mark Lowe]

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.