Run Macro and fill out cells , when country is selected.

[abjac]

Hi I have a dropdownmanu in sheet1 with different countries taken from Column A in Sheet3. I need a macro to run when i select a country example Denmark. It will fill out transmittal code and also country code in named cells for it in sheet1. Info taken from Sheet3
Transmittal code is in below testsheet in Sheet1 cell E12.
Country code is in Sheet1 cell.
But this have to be possible to change. Also the range for the country have to be possible to change. Can be many countries.

This vba code i need to run as soon as i select a country in the dropdown manu.

This is my primary question.
But one thing i would like to solve also is. When i select a country it will create a dropdownlist in I13 taken from the info in column, i have in Sheet3 column F. So if i select example Denmark, it will show a dropdown menu in sheet1 I13, with the ledger codes 10 and 6x.
I have tried to make this work but cant make it work good.

I use excel 2003. Please have a look and upload the testsheet back.

Thanks in advance.

Sincerely.
Abjac

[oeldere]

e12

Please Login or Register  to view this content.

g14=

Please Login or Register  to view this content.

[abjac]

Thanks Oeldere. Tried the formula but it gives and error. Maybe because i use excel 2003. If it can be done with a vba code it would be great.
Please have a look if time.

Thanks

Abjac

[PushCar]

Hi, I hope this help you.

[abjac]

HI Pushcar. Thanks allot for your codes. I tried it but it come up with an error in this line.

Say compile error.
Cant find project or library.

If time please have a look .

Thanks in advance.
SIncerely

Abjac

[PushCar]

Hi abjac.
automateaccessado_1.gif
To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version (mostly, you should choose the highest version number) of Microsoft ActiveX Data Objects x.x Library from the list. The ADO version depends on your Windows operating system, viz. Microsoft ActiveX Data Objects 6.1 Library for Windows 7 or later, Microsoft ActiveX Data Objects 6.0 Library for Windows Vista, and Microsoft ActiveX Data Objects 2.8 Library for Windows XP. In Windows Vista or in later versions of Windows only one of the ADO version 6.0 or version 6.1 will be available in the Reference list at one time. Refer Image 1 for available libraries, wherein 'Microsoft ActiveX Data Objects 6.1 Library' has been selected.

You must have these references (the number on the end can be lower or higher):
references.JPG

[oeldere]

Please Login or Register  to view this content.

[abjac]

HI Pushcar. I figured that out also. I have windows xp and selected the version 2.8. Now it gives another error. ScreenHunter_01 Jun. 09 07.42.gif

Maybe i dont understand but i guess i shall just press the button Show form. Have a look if time.

Oeldere. I think you only pasted my answer to you in here. Could not make your formula work in excel 2003

Sincerely

Abjac

[PushCar]

yes just press the button.
But you must have activated the first cell for example "A2" or A3.... Try in the code change like this and then show me the error when are you pressing the button.

Please Login or Register  to view this content.

ok i think it is because of the excel version so in the code:

Please Login or Register  to view this content.

on the page

http://www.connectionstrings.com/excel/

is the correct examples if you use the excel 2003

[oeldere]

Please Login or Register  to view this content.

VLookup will work in excel 2003.

That is why i'm asking you wich error you get with the formula.

[abjac]

Hi i am on d way work now but will try as soon as i can. Oeldera is it possible for u to send it back in d test sheet i uploaded. Got a code error when i tried put d code in as a formula. Thanks Abjac

[oeldere]

You can use the formula in the excel sheet. (No VBA necessary).

[nilem]

Hi Abjac,
maybe so (see attached file)

[PushCar]

Hi, now (I think) it works fine in Excel 2003.

[abjac]

Hi Nilem. Yours seems to work just perfect. So thanks allot for that.

Pushcar yours are nearly working now the only error is that in Country code it put this in 678_1. It should just be 678. And I guess you did not make anything for the ledger codes. Need to be able to choose there, but the error with the number 678_1 I need to solve if possible. Thanks both for your effort. Will leave it open to see if Pushcar can make his work also.

Thanks its a big help for both of you.

Sincerely

Abjac

[PushCar]

Hi abjac,
If you fill each cell so the values will be in the correct form. Just tri push Next when the Ledger codes is checked. This UserForm works even if some data are longer than 255 characters.

[abjac]

Ok Now I got it for sure. Its quite perfect, thanks allot to you PushCar for you effort and also you Nilem and Oeldere.

Both solutions works brilliant.

Have a nice day. And thanks
Sincerely Abjac