Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-22-2019, 08:15 PM
kiwimtnbkr kiwimtnbkr is offline Worksheet_Activate only if .xltm Windows 10 Worksheet_Activate only if .xltm Office 2010 64bit
Advanced Beginner
Worksheet_Activate only if .xltm
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default 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
When the workbook template saves it is saved as an .xlsm. I added the below code to stop the Sub from running when the now .xlsm file is opened

Code:
Private Sub Worksheet_Activate()
If ActiveWorkbook.FileFormat = xlOpenXMLWorkbookMarcoEnabled Then Exit Sub
Range("H4").Select
Sendkeys "%{up}{NUMLOCK}", True
End Sub
but this has now caused the dropdown list to no longer drop down in the .xltm file when the user clicks on the worksheet2 tab.



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
Reply With Quote
  #2  
Old 07-24-2019, 09:03 PM
kiwimtnbkr kiwimtnbkr is offline Worksheet_Activate only if .xltm Windows 10 Worksheet_Activate only if .xltm Office 2010 64bit
Advanced Beginner
Worksheet_Activate only if .xltm
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Other Forums: Access Forums

All times are GMT -7. The time now is 10:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft