i have a procedure that groups two worksheets then copies them to a new workbook, then among other things renames one of the worksheets. One worksheet that is copied can not be renamed and it seems to be related to the code on the sheet module. The sheet module has code for the Beforedoubleclick and BeforeRightClick events. Both of those procedures have calls to procedure in a regular module. I have other worksheets that can be copied and subsequently renamed, that also have those same procedures with calls to the same procedures in the regular module. the troublesome worksheet can be copied, but not renamed or even addressed in any way.
This is in the worksheet code module for the worksheet that can not be renamed:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Integer, rw As Integer
Dim k As String, fn As String, lnk As String
Dim db As Range
Dim check As Variant
Static ord As Boolean
'open or switch to item file if item is double-clicked
If Not Intersect(Range("items"), ActiveCell) Is Nothing Then
fn = "ITEM " & Target.Value & ".xlsm"
lnk = ActiveWorkbook.Path & "" & fn
Call SwitchOrOpen(fn, lnk)
Cancel = True
Exit Sub
End If
End Sub
If I comment out the Call SwithOrOpen line it can be renamed. I have retyped the entire module, and it still has the exact same issue. Tthere are several other procedures and everyone that has a call to another procedure has to have the call commented out or the code crashes on renaming the worksheet.
This is the code that copies the worksheets:
Private Sub CommandButton28_Click()
Dim sh As Variant
Dim wb As Workbook
Dim n As Integer, i As Integer, x As Integer
Dim str As String
'copy mhi pricing sheet to new book
Sheets(Array("Material", "MHI Pricing", "Rates")).Copy
Set wb = ActiveWorkbook
Set sh = ActiveSheet
Sheets("Material").Name = "Gov Copy"
Sheets("MHI Pricing").Name = "xxxx"
Exit Sub
i really only want to copy the rates and MHI Pricing worksheets, but I added the Material worksheet because it has nearly the identical sheet module procedures, including BeforeClick and BeforeDoubleClick events, with calls to procedures in a code module. There is only one sheet that doesn't work. I have changed the sequence of listing the sheets to be copied, made other changes to sheets post copying. I can do whatever I want to sheets except for MHI Pricing, and if I delete my calls to the code module from the worksheet code page, I can do what I want with that one too.
Doe anyone have any ideas?
Thanks
Ken
This is in the worksheet code module for the worksheet that can not be renamed:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Integer, rw As Integer
Dim k As String, fn As String, lnk As String
Dim db As Range
Dim check As Variant
Static ord As Boolean
'open or switch to item file if item is double-clicked
If Not Intersect(Range("items"), ActiveCell) Is Nothing Then
fn = "ITEM " & Target.Value & ".xlsm"
lnk = ActiveWorkbook.Path & "" & fn
Call SwitchOrOpen(fn, lnk)
Cancel = True
Exit Sub
End If
End Sub
If I comment out the Call SwithOrOpen line it can be renamed. I have retyped the entire module, and it still has the exact same issue. Tthere are several other procedures and everyone that has a call to another procedure has to have the call commented out or the code crashes on renaming the worksheet.
This is the code that copies the worksheets:
Private Sub CommandButton28_Click()
Dim sh As Variant
Dim wb As Workbook
Dim n As Integer, i As Integer, x As Integer
Dim str As String
'copy mhi pricing sheet to new book
Sheets(Array("Material", "MHI Pricing", "Rates")).Copy
Set wb = ActiveWorkbook
Set sh = ActiveSheet
Sheets("Material").Name = "Gov Copy"
Sheets("MHI Pricing").Name = "xxxx"
Exit Sub
i really only want to copy the rates and MHI Pricing worksheets, but I added the Material worksheet because it has nearly the identical sheet module procedures, including BeforeClick and BeforeDoubleClick events, with calls to procedures in a code module. There is only one sheet that doesn't work. I have changed the sequence of listing the sheets to be copied, made other changes to sheets post copying. I can do whatever I want to sheets except for MHI Pricing, and if I delete my calls to the code module from the worksheet code page, I can do what I want with that one too.
Doe anyone have any ideas?
Thanks
Ken