You are here:

Excel/Use VBA to Add Same Worksheet Event to Multiple Sheets


Use VBA to Add same Worksheet Event to Multiple Sheets.
Using Excel 2003

Hi Tom,
I found this Worksheet Selection Change Event Code (written by Hans Vogelaar) that will display a combo box in the active cell if it is in a specified Range. The range in this case is (J2:J65535) and It works fine.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   With Me.cboType
       If Not Intersect(Range("J2:J65535"), ActiveCell) Is Nothing Then
         .Top = ActiveCell.Top
         .Left = ActiveCell.Left
         .Width = ActiveCell.Width
         .LinkedCell = ActiveCell.Address
         .Visible = True
         .Visible = False
         .LinkedCell = ""
       End If
   End With
End Sub

Now I’m trying to add this same code to every sheet in my Workbook except two sheets one named Actions and another named Daily_Rejections.

The reason I need to have the "Worksheet Selection Change Event Code" in each sheet is because after the individual sheets are created they will be saved as separate workbooks for different users.

Is there a way to use VBA code to add the “Worksheet Selection Change Event Code” to each sheet of my workbook except the Actions and Daily_Rejections sheets?

I started to play around with some code like this but I have no idea if it is the right way to go or how to actually finish it to get it into the worksheets.

.insertLines 1, "Option Explicit"
.insertLines 2, "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
.insertLines 3, "With Me.cboType"
.insertLines 4, "If Not Intersect(Range(""J2:J65535""),ActiveCell) Is Nothing Then"
.insertLines 5, ".Top = ActiveCell.Top"
.insertLines 6, ".Left = ActiveCell.Left"
.insertLines 7, ".Width = ActiveCell.Width"
.insertLines 8, ".LinkedCell = ActiveCell.Address"
.insertLines 9, ".Visible = True"
.insertLines 10, "Else"
.insertLines 11, ".Visible = False"
.insertLines 12, ".LinkedCell = """
.insertLines 13, "End If"
.insertLines 14, "End With"
.insertLines 15, "End Sub"

Appreciate any assistance you can provide.
Thanks, Anthony


This is not really a good interface for a detailed discussion - however, Chip Pearson has documented all you need to know on this topic.  I will provide you with his site/page on this topic to so you can build a strong foundation of what you need to know - plenty of good additional information at well.  Make sure you read from the top so you know what references you need to create and so forth.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.