Macro to run a macro when a particular cell is selected/clicked

[Brijesh]

Hello every one

I have a sheet "sheet1". In this sheet in cell A5 there is a drop down box with two options: Option A and Option B. What i have to do is this when option A is selected a particular process is to be done and when option B is selected another process is to be done. I have built a macro "MacroOption" for this purpose. Now what i want is that wheneve a person opens this excel file and when he click on cell A5 of sheet1 (cell in which option drop down box is there) this macro "MacroOption" is should run automatically. Also if he changes the option in cell A5 then this macro should run again automatically. Please suggest code for this.

Thanks in advance

Regards

Brijesh

 

[NARAYANK991]

Hi Brijesh ,

You can make use of the Worksheet_Change event procedure. See this file.

Narayan

 

[Brijesh]

Thanks a lot Narayan

It's working but i have to do a little thing more.

Actually I have already written a macro which take care of the option selected and does the procedure as per option selected. The name of the macro is "Check". I have modified your code as below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Call Check
End Sub


Now I have to do one thing. Whenever this excel file is opened, a macro should run which should place optionA in drop down box and call macro Check to do the procedure. This I have to do because I want the OptionA to be default option. Please suggest the code and also where this code should be placed.

One more thing I would like to ask. What "If Target.Count > 1 Then Exit Sub" does in the code provided by you.

Thanks again

Regard

Brijesh

 

[NARAYANK991]

Hi Brijesh ,

See this file , where the Workbook_Open event procedure has been used.

Target.Count is the number of cells which are affected when the Worksheet_Change event procedure is triggered ; if this is more than 1 , it means that the user is carrying out some operation such as a range clear or a copy + paste ; normal data entry can be done only one cell at a time.

So if it is not normal data entry , we exit.

Narayan