Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Remove VBA (Excel 2002)

    Hi

    Hi I have inherited 1,300 Worksheets from a person who has left the company and I need to do some work on these sheets.
    We were given the password to the Workbook and Worksheets, I have created a macro to remove these, but there is VBA code in This Worksheet which completely restricts the use of the sheet. which I need to remove with a macro, after several attempts I have failed.

    I have created a macro to insert the new data.

    My problem is removing the VBA. Basicly I have a start and an end but no middle.

    Please see attached Sheet. The password is HappyDay for worksheet and workbook

    Many Thanks Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Remove VBA (Excel 2002)

    Hi Braddy,

    What you have in your workbook is a hidden sheet (named 'WARNING'), which you can make visible again with:

    Sub UnHide_WS()
    Worksheets(2).Visible = True
    End Sub

    I don't see any vba in your workbook/worksheet, though.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Macropod

    This what I was reffering to a vba
    Private Const dsWarningSheet As String = "WARNING"

    Private Sub Workbook_Open()
    Dim ds As Object
    On Error Resume Next
    Application.CommandBars("File").Controls.Item("Sav e As...").Enabled = False
    Application.CommandBars("ToolBar List").Enabled = False
    Application.CommandBars("Tools").Controls.Item("Cu stomize...").Enabled = False
    Application.ActiveWorkbook.Unprotect "HappyDay"
    Sheets(dsWarningSheet).Select
    For Each ds In ActiveWorkbook.Sheets
    ds.Visible = True
    Next
    ActiveSheet.Visible = xlVeryHidden
    Application.ActiveWorkbook.Protect "HappyDay", True, True
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ds As Object
    On Error Resume Next
    Application.CommandBars("File").Controls.Item("Sav e As...").Enabled = True
    Application.CommandBars("ToolBar List").Enabled = True
    Application.CommandBars("Tools").Controls.Item("Cu stomize...").Enabled = True
    Application.ActiveWorkbook.Unprotect "HappyDay"
    For Each ds In ActiveWorkbook.Sheets
    If LCase(dsWarningSheet) = LCase(ds.Name) Then
    ds.Visible = True
    Else
    ds.Visible = xlVeryHidden
    End If
    Next
    Application.ActiveWorkbook.Protect "HappyDay", True, True
    End Sub

    Private Sub Workbook_Deactivate()
    On Error Resume Next
    If Application.CutCopyMode = xlCopy Then
    Application.CutCopyMode = False
    End If
    End Sub

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    On Error Resume Next
    If Application.CutCopyMode = xlCopy Then
    Application.CutCopyMode = False
    End If
    End Sub

    Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ds As Object
    On Error Resume Next
    Application.ActiveWorkbook.Unprotect "HappyDay"
    For Each ds In ActiveWorkbook.Sheets
    If LCase(dsWarningSheet) = LCase(ds.Name) Then
    ds.Visible = True
    Else
    ds.Visible = xlVeryHidden
    End If
    Next
    If Worksheets(1).Visible <> 2 Then
    Worksheets(2).Visible = True
    Worksheets(1).Visible = xlVeryHidden
    End If
    Application.ActiveWorkbook.Protect "HappyDay", True, True
    End Sub

    Private Sub Workbook_SheetSelectionChange(ByVal ds As Object, ByVal Target As Excel.Range)
    Application.ActiveWorkbook.Unprotect "HappyDay"
    If LCase(ds.Name) = LCase(dsWarningSheet) Then
    For Each ds In ActiveWorkbook.Sheets
    ds.Visible = True
    Next
    ActiveSheet.Visible = xlVeryHidden
    End If
    Application.ActiveWorkbook.Protect "HappyDay", True, True
    End Sub

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Again

    I am sorry I attached a file with the vba already removed.

    Please see attached

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove VBA (Excel 2002)

    Check out Chip Pearson's site for code to remove code in in a workbook via VB.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Steve

    I am bit out of my depth here but I looked a Chip Pearsons site, It seems mainly for removing vba from modules, but the vba I want to remove is in General Declarations as when I double click this worksheet.

    Thanks for your reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove VBA (Excel 2002)

    Deleting all code from a module should work.

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Steve

    I did mention I am bit weak in this area.

    Tried what you suggested and got an error.

    See attached.

    Thanks
    Bradd7
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove VBA (Excel 2002)

    From Chip Pearson's site:
    <hr>Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. This enables VBA to find the definitions of these objects. If you are using Excel97, this library will appear in the References list without a version number: "Microsoft Visual Basic For Applications Extensibility". If you are using Excel 2000 or 2002, it will appear with a version number: "Microsoft Visual Basic For Applications Extensibility 5.3". It is very important that you reference the proper library. If you reference the wrong library, you will receive "Type Mismatch" errors. If you don't reference the extensibility library at all, you will receive "User Defined Type Not Defined Error".<hr>
    Please set the proper reference, as explained above.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Hans

    I apolgise for not reading the instructions about the refrerence to the correct library, However after doing as requested I still get the following error.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove VBA (Excel 2002)

    You are supposed to adapt this code for your own use. The code in your screenshot will clear a module named "NewModule". You should replace this with the name of the module you want to clear, otherwise you'll get an error message.

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Hans

    As I explained in an earlier post the vba I want to remove is in General Declarations as when I double click this worksheet and not in a module.

    Thanks for your reply

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove VBA (Excel 2002)

    ALL VBA code is in some kind of module. Replace NewModule by the name of the worksheet for which you want to remove the code.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove VBA (Excel 2002)

    <P ID="edit" class=small>(Edited by sdckapr on 09-Oct-04 12:32. Added PS)</P>Change "New Module" to "thisWorkbook".

    Also note if you want to do it to a particular (or referenced) workbook object change the thisworkbook to the appropriate object/name of the workbook

    Steve
    PS Note Chip also lists code to delete all the code from a project (ie all modules) which also might be useful to you in this)

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Remove VBA (Excel 2002)

    Hi Hans

    I did chang New Module for Sheet1(Warning) and ThisWorkbook, whilst This Workbook gave no error it did not remove the vba,

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •