excel vba - upon match entry, load macro?

  • Thread starter Thread starter chief
  • Start date Start date
C

chief

I am setting up code for a macro which I want to test out, and i
successful would like to apply to certain old excel spreadsheets.
Here is some background info.

For my Macro1, It is set up as
If Range("D10").Value = "Autumn Brown" Then
Range("D11, D12") = "Brown"
etc.........
End If

The macro will work when I run it manually, IF autumn brown has bee
typed into cell D10 beforehand. However, I would like to have it se
up so that when someone begins to type in cell D10, it will perform
match entry or lookup which will allow them to select the rest of th
word. Once selected, the word will match the coinciding word I hav
set up in the macro and will automatically run. Is this possible t
do?

Also, if this is possible, how would I go about applying this macro t
old excel file spreadsheets? Does it have something to do with savin
it as an AddIn?

Thank
 
There's an option under Tools|Edit|EnableAutocomplete for Cell values

But that's used with values right above or right below your cell where you're
typing.

I think the solution that you might need is a small userform where the user can
type their values into one combobox. You could use that value as the basis for
another combobox (if required) to show only those "valid" values for that first
value.
 
Thanks

I have decided to go without the match entry, but I have anothe
question though. Here is what I want to happen:

If Range("D10").Value = "Autumn Brown" Then
'Run the macro'
Range("D12, etc").Value = "Brown"
Else If Range("D10").Value = "Charcoal" Then
'Run the macro'
Range......
End If

Now I have tried different codes such as Application.Run, and so on bu
nothing will work such that as soon as text is entered in Cell D10 an
the person moves onto the next cell it will run the macro and filte
information into the appropriate subsequent cells...without having t
manually run the macro.

I am somewhat of an amateur vba man. Does anyone know if it i
possible to run the macro like this.

Thanks for any hel
 
Not sure i completely understand what you want to do but your option
are:

Use worksheet functions eg. vlookup to complete the blank cell
immediately especially if there will be multiple entries down col
that need to be calcualted.

Create a custom function in VBA if you can't ge the worksheet function
to do what you need

Trigger the macro using the worksheet_Selectionchange o
worksheet_calculate events.

Dunca
 
Thanks again for the help

Now that that has been figured out, is it possible to now take tha
module 1 macro and re-write it in a "personal.xls" project and have i
apply to all previous folders when a sheet is opened
 
Back
Top