Further ActiveX Control (TextBox) questions

[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.

 

[Bob Phillips]

Stuart,

The code that you have posted seems to bear no relation to the requirement.
Is there a relationship or do you just want code top meet those 4 points?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stuart]

Bob,

My ain is to present this as a template, available to users on
a networked drive running under 2003 Business Server
and Excel 2003

I'm currently developing it as *.xls, as opposed to a template
under Excel 2000. (Icould do this via 2003, if it i's best).

In the Workbook_BeforeSave Event, what I would ideally
like to achieve would be:

If ContractMaster=True Then
1) strip all code from the activeworkbook
(including embedded controls)
2) save the workbook giving the user the choice of
filename and the save folder, but forcing a
new filename, and forcing a .xls file extension.
3) Offer the user a chance to print the sheet
(I will be able to hard-code the range
and it will allways fit A4 Portrait).
4) Close the workbook, discarding any changes the
user may have made if they went the Print route
5) If user opens saved workbook... all is Locked,
to them, it's just a visible record.

Sorry if my poor code confused the issue, and thanks
for your response.

Regards