How to Remove Code

Hi Mick,

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

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

Hope this helps.

Pete

On Jan 17, 8:38 am, "Vacuum Sealed" wrote:

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

On Jan 17, 3:41 am, Pete_UK wrote:

Hi Mick,

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

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

Hope this helps.

Pete

On Jan 17, 8:38 am, "Vacuum Sealed" wrote:



> 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- Hide quoted text -

- Show quoted text -

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

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

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.

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