macro to create code for sheets
I have a form that creates custom sheets dynamically as the user needs them. The problem that I have is that I need to add two sub procedures associated with each sheet, BeforeDoubleClick and SelectionChange.
When the form creates the sheet I need it to add the following code as well:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(ActiveCell) <> True Or ActiveCell.Column <> "3" Then
Range("C" & ActiveCell.Row).Select
How would I do this from a macro??
Any help would be great
See Chip Pearson's site - Programming To The Visual Basic Editor.
I was able to revise code there:
Thanks heaps that worked well....just have one more issue. With this line:
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
If my sheet name is say "test". If I put that instead of Sheet1 I get subscript out of range.
It is listed as Sheet1(test) under mircosoft excel objects. How do i either find out what sheet is attached to a sheet name or reference that name of the sheet
If you run the code immediately after adding the sheet, it should be the activesheet.
Try replacing ("Sheet1") with (ActiveSheet.Name).
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1