Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    modifying sheet code by VBA (Excel2000)

    Is it possible to use VBA to modify existing code assigned to a particular sheet event?

    In particular, I need to make changes to the Worksheet_BeforeDoubleClick routine.
    I am OK with using VBA to add and remove modules from workbooks, but seem to recall that you might not be able to do this for worksheet code.

    zeddy

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

    Re: modifying sheet code by VBA (Excel2000)

    If you're already familiar with modifying code in code, I'll restrict myself to mentioning ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: modifying sheet code by VBA (Excel2000)

    Thanks Hans.

    zeddy

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: modifying sheet code by VBA (Excel2000)

    Probably the simplest and safest way to change Worksheet code modules is to create a text file containg all the code you require for the worksheet. You can then use the AddFromFile method to actually make the update.

    The following example code removes all existing code from Sheet1 in an open workbook named WorkBook1.xls,and replaces it with the contents of the Text file CodeUpDate.txt. You will need to include the full path to the text file.<pre> Sub UpDateWorkSheetCode()
    Dim i As Long
    Application.EnableEvents = False
    Workbooks("WorkBook1.xls").Activate
    With ActiveWorkbook.VBProject.VBComponents _
    ("Sheet1").CodeModule
    If .CountOfLines > 0 Then
    For i = .CountOfLines To 1 Step -1
    .DeleteLines i
    Next
    End If
    .AddFromFile ("CodeUpDate.txt")
    End With
    Application.EnableEvents = True
    End Sub
    </pre>

    Andrew C

  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: modifying sheet code by VBA (Excel2000)

    See Chip Pearson's site for some discussion on Hans' suggestion.

    Steve

Posting Permissions

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