Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing to worksheet procedures (VBA/Excel 2000)

    I was playing with using the following event procedures to prevent users from editing only certain worksheets in a workbook. It has the advantage of placing restrictions on only the particular worksheets which contain this code in their Worksheet events:

    <pre>Private Sub Worksheet_Activate()
    Application.DisplayFormulaBar = False

    End Sub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox "No can do."

    End Sub

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox "No can do."

    End Sub

    Private Sub Worksheet_Deactivate()
    Application.DisplayFormulaBar = True

    End Sub</pre>


    It's not the method I've settled on because it's lacking in certain areas, but it has brought up a puzzling question. The problem is that these particular sheets are generated using other code and data, so it's not possible to insert the above code "at design time" as it were. Is there any way to have code "write code" to a worksheet's event procedures?

    thanks

    Alan

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

    Re: Writing to worksheet procedures (VBA/Excel 2000)

    Set a reference (in Tools | References...) to the "Microsoft Visual Basic for Applications Extensibility 5.3" library. This library enables you to write code to write code. Here is a simplistic example:

    Sub MakeEventProcs()
    Dim vbc As VBComponent
    Dim lngPos As Long

    Set vbc = Application.VBE.ActiveVBProject.VBComponents("Shee t1")

    With vbc.CodeModule
    lngPos = .CreateEventProc("Activate", "Worksheet")
    .InsertLines lngPos + 1, " Application.DisplayFormulaBar = False"

    lngPos = .CreateEventProc("Deactivate", "Worksheet")
    .InsertLines lngPos + 1, " Application.DisplayFormulaBar = True"
    End With

    Set vbc = Nothing
    End Sub

    If you click in a keyword such as CreateEventProc and press F1, you'll get (limited) online help.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to worksheet procedures (VBA/Excel 2000)

    Thanks Hans. Always interesting to pick up on a new area.
    Is it possible to pass the name of a worksheet to a procedure like MakeEventProcs() (as a string)?

    cheers

    Alan

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

    Re: Writing to worksheet procedures (VBA/Excel 2000)

    >> Is it possible to pass the name of a worksheet to a procedure like MakeEventProcs() (as a string)?

    Of course:

    Sub MakeEventProcs(SheetName As String)
    ...
    Set vbc = Application.VBE.ActiveVBProject.VBComponents(Sheet Name)
    ...

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to worksheet procedures (VBA/Excel 2000)

    Cheers! I was hoping as much, but thought that maybe there were some sort of restrictions related to the addin, with which I'm unfamiliar. Glad it was unfounded. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Edited - And a little more about it at Working with the Microsoft Visual Basic for Applications Extensibility Library.

    Alan

Posting Permissions

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