create a button event

I have a spreadsheet having a command button. Once I click on this button , This need to create a separate workbook and need to insert ‘following code in the sheet1 under Worksheet_Change event. Would it possible ?

Set Rng1 = WS2.Range("$B$1:$B$3")
    Rng1.Name = "AData"

    With WS1.Range("B" & a).Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Adata"
              .IgnoreBlank = False
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = "Wrong Data"
              .InputMessage = ""
              .ErrorMessage = "Data is Invalid !!!"
              .ShowInput = True
              .ShowError = True
               

         End With

Any help is appreciated…

Originally Posted by royUK

The simplest way is to copy the sheet to a new workbook. The code will copy with it.

Thanks for quick reply, The main requirement is to create around 15 similar sheets for each employee in everyweek. I thought this can be automated by doing this.

It would be great if you could throw some light on this.

Jophy

Hello Jophy,

Copy the code below into a Standard VBA module. This will automatically insert the Worksheet_Change event procedure, and your macro into Sheet1 of each Open Workbook. You will need to add a Reference to the Microsoft Visual Basic for Applications Extensibility library before this macro will run.

1. In the Visual Basic Editor go to Tools > References...
2. Scroll through the dialog box until you find Microsoft Visual Basic for Applications Extensibility
3. Click the Check Box to the left of the entry and click OK
4. Save your project

'This your macro code below this line...
'|Set Rng1 = Worksheets("Sheet2").Range("$B$1:$B$3")
'|   Rng1.Name = "AData"
'|
'|   With Worksheets("Sheet1").Range("B" & a).Validation
'|     .Delete
'|     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
'|     xlBetween, Formula1:="=Adata"
'|     .IgnoreBlank = False
'|     .InCellDropdown = True
'|     .InputTitle = ""
'|     .ErrorTitle = "Wrong Data"
'|     .InputMessage = ""
'|     .ErrorMessage = "Data is Invalid !!!"
'|     .ShowInput = True
'|     .ShowError = True
'|   End With

Sub InsertEventCode()

  Dim ModuleCode As String
  Dim StartLine As Long
  Dim Wkb As Workbook
  
    With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule
        ModuleCode = Replace(.Lines(1, 17), "'|", " ")
    End With
  
    For Each Wkb In Workbooks
      If Wkb.Name <> ThisWorkbook.Name Then
        With Wkb.VBProject.VBComponents("Sheet1").CodeModule
          If .Find("Worksheet_Change", 1, 1, .CountOfLines, -1) Then Exit Sub
          StartLine = .CreateEventProc("Change", "Worksheet")
          .InsertLines StartLine + 1, ModuleCode
        End With
      End If
   Next Wkb
   
End Sub

Sincerely,
Leith Ross