Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Array of WorkSheets, CodeModule to Handle each WorkSheet

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to create an dynamic array of worksheets, place a commandbutton on each worksheet, write marco code via .Insertlines in a seperate CodeMolule to handle each commandbutton on each worksheet in the array.

    All works well, until I .Insertlines and then I loose the reference (scope??) to the main array of worksheets.
    So the created Marco doesn't work (no array)

    Actual code can be posted...

    Help again, guys





  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Where are you declaring your array?

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi TsTome,

    I'm declaring the array as a global in Module1, "redim"ing as I go along.

    Option Explicit
    Option Base 1
    Dim wS() As Worksheet, uF As VBComponent, pt As Integer
    ....code....
    If pt = 1 Then ReDim wS(pt) Else ReDim Preserve wS(pt)
    Sheets.Add after:=Worksheets(Worksheets.Count)
    Set wS(pt) = ActiveSheet
    ....code....

    The CodeModule is added in the ThisWorkbook Module

    ThisWorkbook.VBProject.VBComponents.Add(1).Name = "cM"

    The following sub is ran in Module1 (with NO reference to the array)-

    Sub softM()
    Dim sSub As CodeModule, l As Integer
    Set sSub = ThisWorkbook.VBProject.VBComponents("cM").CodeModule
    With sSub
    l = .CountOfLines
    .InsertLines l + 1, "Sub Scope_" & pt & "()"
    .InsertLines l + 2, " setMM(" & pt & ")"
    .InsertLines 1 + 3, "End Sub" & Chr(13)
    End With
    End Sub

    After the sub runs the array has no variables.

    If I comment out the ".InsertLines" statements, the sub works OK, i.e. I can count the lines and retain the array.

    Tks...



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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