Why wont it do?

[thorpyuk]

Dim bfr As Long
bfr = ComboBox1.Value
ComboBox1.Value = bfr: Exit Sub

how come this gives me a "cant change property" error!?

The code is inside my combobox change event...

 

[thorpyuk]

To be honest, this particular spreadsheet needs re-engineering.

Ok, i'll explain in words what it's mean to do.

Basically, i have a 'project' sheet which contains a list of projects, and a load of dates.

The projects can be sorted by season (AW08, SS09, AW09 etc)

The scripting dictionary lets me populate combobox2 with the seasons that appear in my project sheet.... currently AW08, SS09 and AW09. I dunno how these things work exactly, but essentially it then lets me 'filter' my projects in season order depending on the season picked in combobox2. (rory - i know, i know, i really should name my objects!!)

I am then left with a list of relevant projects in combobox1... (the one with the supposed 'erronious' values).

What that horrid code above does under the combobox1 change event is to search for the unique project ID (combobox1.value) within another sheet - 'Overview' and pull back a list of sub-project ID's

I wanted to ohmit any selection of a project which has NO sub-projects..... hence the msgbox!

These sub-project id's are then looked up using a vast array of very inefficient VLOOKUP formulas - Norie - you'd be horrified!!! Hope that explains what i'm trying to do!

 

[RoryA]

Sadly, I've seen worse...
Bad enough to use GoTo (IMO) but to use it for that...

Anyway, why are you happy with a car that has its wing mirrors falling off all the time? Surely better to fix that (the data) rather than wait for them to fall off when you're reversing (i.e. your boss is using your app!)

 

[thorpyuk]

Sadly, I've seen worse...
Bad enough to use GoTo (IMO) but to use it for that...

Anyway, why are you happy with a car that has its wing mirrors falling off all the time? Surely better to fix that (the data) rather than wait for them to fall off when you're reversing (i.e. your boss is using your app!)

ROFL!! I like you're analagy's mate!

 

[Norie]

Re-engineer - more like re-birth from the beginning upwards.

And it really, really would help to see some sample data, especially how it's structured.

3 codes which mean nothing to us isn't much to go on.

PS And it sounds to me like you already have some way to identify the values you don't want.


Have you considered not using Dictionary?

There are plenty of other methods to populate comboboxes.

Anyways I'm off to fight Tiscali, they keep on denying my password for the Billing section but allow me into all others.

 

[thorpyuk]

Norie

If i ever go to Yaren, i shall buy you a pint. P.S - where is Yaren anyway!!!

I can't really re-engineer because this particular spreadsheet has taken me over a year of development :> Maybe i need to re-work the one sheet. (there's about 5 usable tabs)

If you like, i can send you a cut-down version on email (less all company insignia and actual data), pm me :>

 

[Norie]

Thorpy

I've lost count how many times I've had to re-engineer projects/whatever.

The best way to go about it is to keep the original on the go and develop the new improved version when you have time.

If you don't have that time you might want to speak to somebody about what you want to do.

Explain to them that though the current system is adequate it could do with a revamp and that could bring advantages to the 'business'.

PS Remember to use 'business' and other such jargon - the sort of people you are dealing might like that kind of thing.

I'll PM you later -I'm not promising anything but hopefully I can give you some pointers.

And knowing me you might find one of those might mention Access.

 

[thorpyuk]

Sent over, you'll see what i mean when you look at it!

On a side note, i've got some reference problems too.

One of my users has just had a new machine with office 2007 installed. Problem is that as soon as she opens my workbook, excel automatically migrates all version 11 libraries to version 12

Meaning - i get errors with all my code when trying to run anything, unless i copy over Microsoft Office Outlook 12 Object library into the relevant folder:

C:\programfiles\microsoft office\office12\msoutl.olb :<


There's no way of 'freezing' all these references in my workbook is there? (or does that mean my code wont run!?)

 

[Norie]

Are you actually using Outlook anywhere in the project?

Have you at any point experimented with it?

PS Got the file - can't help you. I'm just getting errors regarding the reference and I can't access the VBProject - it's asking for a password.

 

[thorpyuk]

Hiya,

have pm'd you the VB project password....

I do use outlook on one of the userforms - userform4 :>

 

[RoryA]

If referencing is an issue, use late binding instead.