VBA - Trigger a macro when ANY cell in column A is selected

[tlc_in_OK]

I haven't found exactly what I need on the boards, so hope someone can help.

I have a macro that refreshes a dataset from SQL, then creates a new sheet with some of that data placed and formatted as desired. My issue is that after that macro runs, I need to run another macro when any cell in column A is selected. I could do this normally by using Worksheet_SelectionChange(ByVal Target As Range), however, since the data gets inserted and changed in my worksheet when refreshing it, this is triggering the macro without actually selecting an individual cell.

So my questions are. . .how can I make a macro run by selecting a single cell, but ONLY make that possible after the data is refreshed and formatted with my first macro? And how do I make any cell in column A the trigger for the macro, instead of a specific cell?

Thanks in advance for any assistance.

 

[Rick Rothstein]

One way to do what you have asked for is to use a global variable as a "signaling device" between the two macros. The declaration for the global variable would be done at the beginning of the Module containing your two macros on a line all by itself... it is NOT placed inside any Sub or Function procedures. Then you have the first macro set it to True and have the second macro test it and reset it to False as the end. The generalize structure of what I am talking about would look like this...

Dim OkayToRun As Boolean

Sub FirstMacro()
  '
  '  Your existing code
  '
  OkayToRun = True
End Sub

Sub SecondMacro()
  If Not OkayToRun Then Exit Sub
  '
  '  Your existing code
  '
  OkayToRun = False
End Sub

Just make sure each macro executes that last line of code before exiting (in case you have Exit Sub statements located internally within your code).

 

[tlc_in_OK]

Thanks Rick- I see where you're going, but I don't understand how that would allow me to "activate" all of the cells in column A of my sheet so that, on selection of any one cell, it would run the second macro.

 

[Rick Rothstein]

Thanks Rick- I see where you're going, but I don't understand how that would allow me to "activate" all of the cells in column A of my sheet so that, on selection of any one cell, it would run the second macro.

I thought you would use the Worksheet_SelectionChange event to control the selection, maybe using EnableEvents=False at the beginning of it and EnableEvents=True at the end of it and, I'm guessing, call you first macro from within it. Notice I said "thought", "maybe" and "guessing"... that is because you have not posted anywhere near enough information for us to be able to give you specifics. The best we can do is try to guide you to a solution.

 

[tlc_in_OK]

Ok, I guess I need to give additional information.

My workbook contains a hidden worksheet ("Hidden") and a visibile worksheet ("CurrentProjects"). Data is refreshed into the hidden worksheet, then my first macro takes some of the data, formats it and arranges it into the visible worksheet, shown below:
Excel 2010

	A	B	C	D	E
August
data1	city1	GA	project1
data2	city2	project2
data3	city3	MA	project3
data4	city4	project4
data5	city5	AL	project5
data6	city6	project6
September
data9	city9	NM	project9
data10	city10	project10
data11	city11	AR	project11

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #4F81BD, align: center"]DetailNumber[/TD]
[TD="bgcolor: #4F81BD"]StreetAddress[/TD]
[TD="bgcolor: #4F81BD"]City[/TD]
[TD="bgcolor: #4F81BD"]StateProvince[/TD]
[TD="bgcolor: #4F81BD"]ProjectName[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]332530[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #DCE6F1, align: center"]332121[/TD]

[TD="bgcolor: #DCE6F1"]NJ[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]332103[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #DCE6F1, align: center"]332341[/TD]

[TD="bgcolor: #DCE6F1"]OK[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]332278[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DCE6F1, align: center"]331940[/TD]

[TD="bgcolor: #DCE6F1"]TX[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]332344[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #DCE6F1, align: center"]332343[/TD]

[TD="bgcolor: #DCE6F1"]AZ[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]332300[/TD]

</tbody>

CurrentProjects​

Now I need to select any of the data cells in column A and have that trigger a second macro that will create another sheet with the value of the selected cell as the worksheet name, then do some other calculations and such (which I can handle). I originally used the Worksheet_SelectionChange event, but it fired when my first macro put data into the sheet and messed everything up. I attempted to use Rick's solution, but it seemed to throw it into an endless loop and the second macro would never fire. I also need to run the second macro, then go back to the original sheet and run it again if desired by selecting a different cell.