Solved: Delete SheetCode after save sheet to new workbook

[Erays]

Scenario I have 5 workbooks the sheets are being named by code, in the five workbooks i decide to save on sheet for each workbook say they are all named "week one" I combine the five sheets named "week one" and excel renames the sheet, but the code in the sheet VBAProject causes an error, I would like to delete the code form the worksheet when running the saveactive sheet code. Below is the code that I would want to programmatically remove


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Name <> [E2] Then ActiveSheet.Name = [E2]
End Sub

[mdmackillop]

Can you move your code to the workbook module?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> [E2] And [E2] <> "" Then ActiveSheet.Name = [E2]
End Sub

[Erays]

I probably can this runs when you select a cell it automatically names the sheet to cell value, is there a code that i can put on the sheet to run the script

[mdmackillop]

Any code you put on the sheet will be copied with it. The code I posted will rename all sheets to the E2 value when it is changed. If you only wish to allow changes to certain sheets, you could add an If function to exclude the "static" ones.

[Erays]

I have uploaded the file

[Norie]

Erays

Why not try mdmackillop's idea?

Put the code in the workbook module not the individual worksheet modules.

If you only want the code to apply to certain sheets just check the name.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Left(Sh.Name, 4) = "Bill" Then
If Sh.Name <> Sh.Range("E2") Then Sh.Name = Sh.Range("E2")
End If
End Sub

[Erdin? E. Ka]

Hi everyone,

I used the codes but there is a problem if E2 lenght bigger 31 digits. For this i rearranged as below;


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Len([E2]) <= 31 Then
If ActiveSheet.Name <> [E2] And [E2] <> "" Then ActiveSheet.Name = [E2]
Else
If ActiveSheet.Name <> Mid([E2], 1, 31) And [E2] <> "" Then ActiveSheet.Name = Mid([E2], 1, 31)
End If

End Sub


As you understand, if lenght of E2 cell bigger 31 then sheet name being only first 31 digits.

:peace:

[mdmackillop]

Hi Erays,
Are you using code to copy the similarly named worksheets To or From the other workbook, If so can you post your code?

[Erays]

I have a new workbook each week all with the same sheet names that are being named by code when you double click on an empty cell , E2 is pulling the persons name off of a setup sheet with 25 different names, at the end of the quarter I take the page out of each weekly book and put them in a workbor for that person such as bill but when I click on the page it gives me an error because it is trying to name the page bill

[mdmackillop]

Solution from Johnske's Article on VBE Programming.

This will set your sheet name as before and then call code to delete all code in the worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Name <> [E2] Then ActiveSheet.Name = [E2]
DeleteAllCodeInModule ActiveSheet.CodeName
End Sub


Add the following code to a standard module


Option Explicit
Sub DeleteAllCodeInModule(ThsSht)
Dim FirstLine As Long, NumberOfLines As Long
With ThisWorkbook.VBProject.VBComponents(ThsSht).CodeModule
FirstLine = 1
NumberOfLines = .CountOfLines
.DeleteLines FirstLine, NumberOfLines
End With
End Sub

[Jacob Hilderbrand]

For this to work though, the user will have to check Trust access to visual basic project in Tools | Macro | Security | Trusted Sources.

Another option may be to simply copy and paste the data from the sheet to the new sheet. This way the code will not be copied along with it.

And a third option, which I generally use, is to move any event code on the worksheet module to the workbook module (if possible). Then I just control what sheets that code will run for be checking the sheet name. This way will allow you to copy the entire sheet without taking the code along.

:2p:

[Erays]

Well here is what I would like to do I want to put a button on one page that will delete all the worksheet module in the workbook Can this be done? If so how do I code this

[Erays]

Thanks for all of your help

[mdmackillop]

try

Option Explicit
Sub DeleteAllCodeInSheetModules()
Dim FirstLine As Long, NumberOfLines As Long, sh as worksheet
For Each sh In Worksheets
With ThisWorkbook.VBProject.VBComponents(sh.CodeName).CodeModule
FirstLine = 1
NumberOfLines = .CountOfLines
.DeleteLines FirstLine, NumberOfLines
End With
Next
End Sub

[Erays]

Well I have learned alot today I thank all of you who have helped This solves my small problem


Thank You Again