Excel/Use VBA to Add Same Worksheet Event to Multiple Sheets
Use VBA to Add same Worksheet Event to Multiple Sheets.
Using Excel 2003
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)
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 = ""
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.
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.