Hello
I have a code to create a new workbook. I would like to add a Worksheet_SelectionChange code to one of the created worksheets.
Is it possible to automate this during the vba create process?
Thanks
Hello
I have a code to create a new workbook. I would like to add a Worksheet_SelectionChange code to one of the created worksheets.
Is it possible to automate this during the vba create process?
Thanks
You could write code to create code but it might be easier to have a template worksheet with the SelectionChange code in it and copy that to the new workbook.
If posting code please use code tags, see here.
The reason I'm asking is because I will be using a code to make lots of workbooks. I was hoping I could automate the process of copying a bit of code so I wouldn't have to do it hundreds of times.
Yes you can do it, but it would be easier to work with a template worksheet no matter how many workbooks you are creating.
For example to create a new workbook and copy a template worksheet to it:
To add the SelectionChange event to a worksheet in a newly created workbook using code:![]()
Dim wbNew As Workbook Dim wsTemp As Worksheet Set wsTemp = ThisWorkbook.Workhsheets("TempSheet") Set wbNew = Workbooks.Add wsTemp.Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
That's a very simple example and only creates code for a message box.![]()
Sub CreateWSSelectionChange() Dim wbNew As Workbook Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Set wbNew = Workbooks.Add Set VBProj = wbNew.VBProject Set VBComp = VBProj.VBComponents("Sheet1") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("SelectionChange", "Worksheet") LineNum = LineNum + 1 .InsertLines LineNum, " MsgBox ""Hello World!""" End With End Sub
Excellent. Now I have something to play with. I really appreciate your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks