S
Stuart
A single sheet workbook opens. If ContractMaster = True
(in the Workbook_Open Event) then an ActiveX Control
(a Textbox) is enabled, and user can make their changes.
When user comes to save, I am using the Workbook_BeforeSave
Event to test for ContractMaster as follows:
If ContractMaster = True Then
Dim ws As Worksheet
Application.ScreenUpdating = False
With Workbooks
.Add
Workbooks(wkbkname).Sheets("Master Fax").Copy _
Before:=ActiveWorkbook.Sheets(1)
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
With ws
If .Name <> "Master Fax" Then
.Delete
End If
End With
Next
With ActiveWorkbook.Sheets("Master Fax")
.Range("C19").Select
.Protect
.EnableSelection = xlUnlockedCells
End With
'To hide the textbox,try:
ActiveSheet.OLEObjects("Textbox1").Visible = False
'To prevent editing, try:
ActiveSheet.OLEObjects("Textbox1").Enabled = False
End With
End If
I'm creating a new workbook and copying the data sheet to
that, because I cannot get Chip's DeleteAllVBA code to
run correctly (he does say it is unreliable when run from a
module that, itself, is to be deleted
It's not working as intended. Ideally, if ContractMaster =
True, then
a) strip all code from the workbook
b) remove the ActiveX Control
c) save the changed file (I have code that lets the user
choose the filename and folder)
d) leave the changed file visible as the activeworkbook
Can this be achieved, please?
Regards.
(in the Workbook_Open Event) then an ActiveX Control
(a Textbox) is enabled, and user can make their changes.
When user comes to save, I am using the Workbook_BeforeSave
Event to test for ContractMaster as follows:
If ContractMaster = True Then
Dim ws As Worksheet
Application.ScreenUpdating = False
With Workbooks
.Add
Workbooks(wkbkname).Sheets("Master Fax").Copy _
Before:=ActiveWorkbook.Sheets(1)
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
With ws
If .Name <> "Master Fax" Then
.Delete
End If
End With
Next
With ActiveWorkbook.Sheets("Master Fax")
.Range("C19").Select
.Protect
.EnableSelection = xlUnlockedCells
End With
'To hide the textbox,try:
ActiveSheet.OLEObjects("Textbox1").Visible = False
'To prevent editing, try:
ActiveSheet.OLEObjects("Textbox1").Enabled = False
End With
End If
I'm creating a new workbook and copying the data sheet to
that, because I cannot get Chip's DeleteAllVBA code to
run correctly (he does say it is unreliable when run from a
module that, itself, is to be deleted
It's not working as intended. Ideally, if ContractMaster =
True, then
a) strip all code from the workbook
b) remove the ActiveX Control
c) save the changed file (I have code that lets the user
choose the filename and folder)
d) leave the changed file visible as the activeworkbook
Can this be achieved, please?
Regards.