Macro recognising a a change in a cell

[Janine]

Hi

I have the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("B3").Address Then
Range("Vendor").Value = ""
End If
End Sub

What I am trying to do is clear a row of cells that are drop down boxes, when B3 changes. B3 is the output cell from a ActiveX combo box and displays text

All help appreciated

 

[Tiago MF]

Hi Janine

I'm not VBA savvy so I can't help you with that code.

But regarding drop down boxes, I guess you are using data validation, in which case you have to assure that the List that feeds the drop down box contains a blank value or else I don't think your macro will work.

Regards

 

[Janine]

Hi Tiago, the macro works fine, if I click into the cell its evaluating for the change and type something, it just doesn't recognize the change when the combo box output changes. frustrating !

 

[Janine]

can anyone else help please?

 

[Stevie]

Hiya Janine, please upload the file, as it could be a few things.

 

[Janine]

Thanks Stevie

 

[Stevie]

Hi Janine,
I see your problem now, Worksheet_Change only captures manual user changes. You can either capture the manual change on the 'Header Information' sheet, or you can capture the Worksheet_Calculate event.
Both approaches are outlined here:
http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula

Just change the ranges and sheet names to match your document and you should be ready to go.

I hope this helps!

 

[NoSparks]

Perhaps you are using the wrong event for what you want or perhaps you need both.
Worksheet_SelectionChange applies to the cell you are going into.
Worksheet_Change applies to the cell you are coming out of.

 

[NARAYANK991]

Hi Janine ,

You do not need a Worksheet_Change event procedure.

The existing ComboBox21_Change event procedure should work.

I moved your Header Information tab to a new workbook , and introduced the ComboBox21_Change event procedure in that new workbook , and the event of changing the combobox selection is recognized.

Unfortunately in your workbook it is not being recognized.

I suggest you create a new workbook , transfer all of these worksheet tabs to the new workbook , and try once more.

Narayan

 

[Janine]

Ah interesting Narayan, what is the code you used please?

 

[Janine]

Stevie, I just cant seem to get those to work either

 

[Janine]

On another note on this workbook, on the front tab I have a controls tab order, which works fine by tab down through the boxes. However other users when testing it cannot tab through them? The macros don't seem to work for anyone else but me..... I'm seriously thinking of starting again!

 

[NARAYANK991]

Hi Janine ,

See this example.

Narayan