How to Remove Code

[Vacuum Sealed]

Hi everyone

I recieved some great assistance on a project I'm working on just recently,
and I'm 99% done.

As I do a backup of each day, I insert the date into the File.Name so that
others in the office can access it and do their respective thing with the
information enclosed the following day.

What they don't need to see, or have access to is the code attached to it,
both in "ThisWorkbook" & "Modules".

I read a thread a while ago that explained how to do it, but I didn't think
it important at the time so I dismissed it...Go Figure...!

That said.!

Attached to [ALT-F11] - ThisWorkbook is 2 Subs:

Start_Timer() & Stop_Timer() + there is 7 Modules also attached with a
variety of Subs & Functions

Ideally, I would like for all of it to disappear, though I would still be
happy if it was just the Sub Start_Timer() code that is removed as it is
triggered when the Workbook opens, this represents a major inconvenience and
annoyance to those who do not require it, or know how & where to switch the
timer off.

So something like:

AllModules.Select
Selection.Delete, "AllModules", Save = vbNo
AllVBACode.Select
Selection.Delete, "AllVBA"
Application.Compile, Save = vbYes

....Pause

Lastly, is it possible to insert something like the following to clean up
the sheet just prior to closing

On_Close()

' This section contains CmdBtns that will be rendered useless and require
removal leaving only a Header Row.

Rows("1:3").select
Selection.Delete Shift:=xlUp
With Workbook
..Save
..close
End With

TIA
Mick

 

[Pete_UK]

Hi Mick,

take a look at Chip Pearson's site, here:

http://www.cpearson.com/excel/vbe.aspx

Hope this helps.

Pete

 

[Don Guillett Excel MVP]

Hi Mick,

take a look at Chip Pearson's site, here:

http://www.cpearson.com/excel/vbe.aspx

Hope this helps.

Pete

Hi everyone

I recieved some great assistance on a project I'm working on just recently,
and I'm 99% done.

As I do a backup of each day, I insert the date into the File.Name so that
others in the office can access it and do their respective thing with the
information enclosed the following day.

What they don't need to see, or have access to is the code attached to it,
both in "ThisWorkbook" & "Modules".

I read a thread a while ago that explained how to do it, but I didn't think
it important at the time so I dismissed it...Go Figure...!

That said.!

Attached to [ALT-F11] - ThisWorkbook is 2 Subs:

Start_Timer() & Stop_Timer() + there is 7 Modules also attached with a
variety of Subs & Functions

Ideally, I would like for all of it to disappear, though I would still be
happy if it was just the Sub Start_Timer() code that is removed as it is
triggered when the Workbook opens, this represents a major inconvenience and
annoyance to those who do not require it, or know how & where to switchthe
timer off.

So something like:

AllModules.Select
Selection.Delete, "AllModules", Save = vbNo
AllVBACode.Select
Selection.Delete, "AllVBA"
Application.Compile, Save = vbYes

Lastly, is it possible to insert something like the following to clean up
the sheet just prior to closing

' This section contains CmdBtns that will be rendered useless and require
removal leaving only a Header Row.

Rows("1:3").select
Selection.Delete Shift:=xlUp
With Workbook
.Save
.close
End With

TIA
Mick- Hide quoted text -

- Show quoted text -

In additiion to that, if you are using xl2007 you can simply save as
an .xlsX file

 

[Vacuum Sealed]

Thank you Don & Pete

I checked out Chips Stie which had what looked like code that would do the
job, but It may be limited to 2007 onwards.

The work computer only has 2003 and it halts on ** This ?Section **:

** Set VBProj = ActiveWorkbook.VBProject **

Set VBComp = VBProj.VBComponents("ThisWorkbook")
Set CodeMod = VBComp.CodeModule

ProcName = "Private Sub Workbook_Open()"

With CodeMod
StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
.DeleteLines StartLine:=StartLine, Count:=NumLines
End With

Chip if you happen to be reading this, I could certainly use your guidance
please.

I then thought to myself that, as I was executing this within the workbook I
was attempting to rid the hidden code of, it may have some influence on
whether or not it would allow itself to trigger, so I tried executing it
from another workbook and it popped up with:

Error # 1004 - You do not have permission to do that.

Now, I'm assuming I do not have permission to delete/remove the code behind
"ThisWorkbook", not sure, it's disappointing as this was the final hurdle to
complete this project.

Open to any suggestions

Cheers
Mick

 

[Vacuum Sealed]

And here another perfect example of "Foot in Mouth" desease whereby I have
engaged fingers before throwing my brain in to gear.

Turns out for those who happen to "Not" read the instruction carefully, well
you might find that you have to activate the VBE reference, and or if not
installed, go download it, then do it....

Welcome to Muppet Central, I'm your host.........

DOH......

LMAO.....

Cheers
Mick.

 

[minimaster]

As an Addin I've a commandbar that gives me an easy access to the
complete workbook structure, sheets, modules, and procedures in a menu
structure. It shows as well any hidden sheets. When accessing hidden
sheets via my menu structure I'm adding some code to these hidden
sheets to make the sheets automatically hidden again when the sheet is
being deselected (utilizing some examples from Chip).
In other words this procedure adds some event code to a module which
deletes itself (the code) again when the event is happening.
Just an example how to add and delete VBA code. Assuming references
and security seetings are ok ,-)


Sub AddEventProcedure(Optional hideMode As Variant = xlHidden)
'
' This sub adds a self-deleting event procedure to the "ThisWorkbook"
code module
' this added event procedure will hide the sheet again when the
"hidden" sheet is deselected
' and then it will delete itself (the event procedure)

Dim VBEHwnd As Long, StartLine As Long, NumLines As Long
' some code to prevent the VB editor window showing up when
writing code
' from http://www.cpearson.com/excel/vbe.htm
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk", _
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If
'
' the code to write code
With
ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
' first check whether the event procedure to be created does
not already exist!
On Error Resume Next
StartLine = .ProcStartLine("Workbook_SheetDeactivate", 0)
On Error GoTo ErrH:
If StartLine > 0 Then
Select Case MsgBox("To hide the hidden sheet automatically
when it is deselected the Add-in would like to write a visual basic
procedure to the code module of ThisWorkbook. " _
& vbCrLf & "However a
Workbook_SheetDeactivate event procedure in the ThisWorkbook module
already exists!" _
& vbCrLf & "Do you want to keep it?" _
, vbYesNo Or vbExclamation Or
vbDefaultButton1, "Procedure Exists!")
Case vbYes
LockWindowUpdate 0&
Exit Sub
Case vbNo
NumLines = .ProcCountLines("Workbook_SheetDeactivate",
0)
.DeleteLines StartLine:=StartLine, Count:=NumLines
End Select
End If

' okay now we can create an event procedure
StartLine = .CreateEventProc("SheetDeactivate", "Workbook") +
1
'Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) '
created by previous line
.InsertLines StartLine, _
" Dim StartLine As Long, HowManyLines As Long" &
Chr(13) & Chr(13) & _
" Sh.Visible = " & hideMode & Chr(13) & _
" With
ActiveWorkbook.VBProject.VBComponents(""ThisWorkbook"").CodeModule" &
Chr(13) & _
" StartLine
= .ProcStartLine(""Workbook_SheetDeactivate"", 0)" & Chr(13) & _
" HowManyLines
= .ProcCountLines(""Workbook_SheetDeactivate"", 0)" & Chr(13) & _
" .DeleteLines StartLine, HowManyLines" &
Chr(13) & _
" End With"
'End Sub ' created by CreateEventProc( , see above
End With
'
' to make windows act normal again
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&

End Sub

 

[Vacuum Sealed]

Wow

Thx heaps for that, I will most certainly keep this for another project
coming up, al-be-it it is not quite what I had in mind for this one.

As a minimum, I would be happy to just have the StopTimer() triggered and
the StartTimer() removed to prevent it from triggering when the sheet is
opened by another user down the line.

Most, if not all the other members working within the office accessing the
file do not have the knowledge to turn it off, hence the need to do it for
them.

Cheers
Mick

 

[Vacuum Sealed]

Ok

I managed to overcome the code halting and it runs all the way through to
the end.

But it still is not removing the modules or the lines of code from anywhere
within the VBE window.

I have synchronised it to ensure that it is in fact what it is I'm looking
at and it just doesn't wanna do the removal.

Any pointers please.

TIA
Mick

 

[minimaster]

What version of Excel do you work with?
You have to enable access to the VBA project under the Excel options
in most Excel version this is somehow deeply hidden.
inn 2007/2010 this can be found in Options -> Trust Center -> Trust
Center Settings.. ->Macro Settings -> x Trust access to the VBA
project object model
in 2002/2003 in should be somwhere under Options .. or under Macro
Security. Have a look yourself, I can't look it up right now.

 

[Vacuum Sealed]

Cheer Mini

have got it working like a charm after realising just exactly that.

Appreciate everyones efforts.

Cheers
Mick

 

[Vacuum Sealed]

One last thing

Using 2003, Is there a way to programatically enable/disable the Trust
Access so that just prior to my code that removes all modules and codes it
is Enabled, so that it will allow the code to run, then once it is finished,
Disable it.

At present, I only have 1 workbook that requires the VBOM value be changed
as it is the only one that has its modules and other workbook codes deleted.

TIA
Mick

 

[GS]

Sounds to me like you need to put the code in a XLA so it isn't in the
project wkb in the first place. This would allow you to start each wkb
from a template, process your code on it as 'ActiveWorkbook', save it
using 'SaveAs' with the appropriate filename as you described.

Other Alternatives:
You could put the code in PERSONAL.XLS so it's there whenever you open
Excel. You could also set up custom menus to run the code from your own
custom toolbar (also available when Excel is running)!

Seems easier than messing around with Macro Security/Trust settings or
having to remove the code.<g>

 

[Vacuum Sealed]

Thx again Garry

In retrospect, that'd probably be the easy way around it, but I'm thinking
outside the egg, playing it safe so that in the event I am ever away, the
not so familiar can stumble through it without turning it into a dog's
dinner.

The Macro Security aspect isn't of major concern as I work in the Transport
Industry and the few who use the projects I create in the office where I
work, Well...!!! let's just say they come from a different planet than those
of us who visit here....It's a small hurdle that I'm not all that concerned
with, just seemed a nice tidy finish to the project.

Cheers
Mick.

 

[GS]

After serious thinking Vacuum Sealed wrote :

Thx again Garry

In retrospect, that'd probably be the easy way around it, but I'm thinking
outside the egg, playing it safe so that in the event I am ever away, the not
so familiar can stumble through it without turning it into a dog's dinner.

The Macro Security aspect isn't of major concern as I work in the Transport
Industry and the few who use the projects I create in the office where I
work, Well...!!! let's just say they come from a different planet than those
of us who visit here....It's a small hurdle that I'm not all that concerned
with, just seemed a nice tidy finish to the project.

Cheers
Mick.

Hi Mick,

My point was that if you made the project an addin then it can't be
messed with by other (at least not easily) and so they can do the same
work without the workbook they work on having any code in it. You can
distribute the addin to everyone else as needed so it resides on their
machine. Your addin could provide them its own toolbar that has
everything they need to do the intended task. This keeps the workbook
in tact as you expect, and keeps people away from your code as you can
password protect the project from viewing.<g>

 

[Vacuum Sealed]

Thx Garry

There in lies the beauty of it

They dont need all the eye candy of their own toolbars as such, all they do
is open the file, look at whatever is they need to and close it.

I was actually thinking the simplest way may be to save it in a 2003
NonMacro format, something like an .xlm to disable all the code and just
leave the sheet as is.

It doesn't have to be pretty, just basic functionality for them to do their
thing.

The window of opportunity on this project has closed as I have started
another.

But I really appreciate all your input and efforts.

Cheers
Mick.

 

[GS]

Vacuum Sealed explained on 1/27/2011 :

Thx Garry

There in lies the beauty of it

They dont need all the eye candy of their own toolbars as such, all they do
is open the file, look at whatever is they need to and close it.

I was actually thinking the simplest way may be to save it in a 2003 NonMacro
format, something like an .xlm to disable all the code and just leave the
sheet as is.

It doesn't have to be pretty, just basic functionality for them to do their
thing.

The window of opportunity on this project has closed as I have started
another.

But I really appreciate all your input and efforts.

Cheers
Mick.

You're welcome!
All the best with your endeavors...