#1
|
|||
|
|||
Worksheet_Activate only if .xltm
The workbook template opens on worksheet1 which contains some instructions. When the user clicks on the worksheet2 tab to fill in the required information then the following original code caused a dropdown list to appear:
Code:
Private Sub Worksheet_Activate() Range("H4").Select Sendkeys "%{up}{NUMLOCK}", True End Sub Code:
Private Sub Worksheet_Activate() If ActiveWorkbook.FileFormat = xlOpenXMLWorkbookMarcoEnabled Then Exit Sub Range("H4").Select Sendkeys "%{up}{NUMLOCK}", True End Sub Where am I going wrong? cheers Mike Last edited by kiwimtnbkr; 07-23-2019 at 01:41 PM. Reason: restructured question to reflect what is actual happening |
#2
|
|||
|
|||
Resolved by deleting the macro when saving the file as a .xlsm file. The following code was placed inside a Module.
Code:
Sub DeleteWorksheet_Activate() Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents Dim VBProj As VBIDE.VBProject Dim CodeMod As VBIDE.CodeModule Dim StartLine As Long Dim NumLines As Long Dim ProcName As String Set VBComps = This Workbook.VBProject.VBComponents Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBcomponent("Sheet4") Set CodeMod = VBComp.CodeModule For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_MSForm VBComps.Remove VBComp End Select Next VBComp ProcName = "Worksheet_Activate" With CodeMod Startline = .ProcStartLine(ProcName, vbext_pk_Proc) Numline = .ProcCountLine(ProcName, vbext_pk_Proc) .DeleteLines StartLine:=StartLine, Count:=NumLines End With End Sub |
Thread Tools | |
Display Modes | |
|