+ Reply to Thread
Results 1 to 5 of 5

Public Array in a form?

  1. #1
    Registered User
    Join Date
    07-06-2005
    Posts
    6

    Public Array in a form?

    Hi,

    I am trying to create a user form that toggles the spreadsheet view between the normal cells interior colors and colors that depend on the cell content (e.g. yellow for a formula etc.)
    I have created the form and the procedures that actually perform the actions. My problem is that when loading the form, I want to store the color of each cell of the UsedRange in an bi-dimensional array, so that I can restore the original colors of the spreadsheet (making sense so far? )
    That array needs to be public because I will need to use it in several procedures such as togglebutton1_change, userform_terminate etc. Therefore I have tried to delare it at the top of the form's code as a public variable.

    Unfortunately I get the following message: "Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module"

    The VBA help suggests to "use a set of Property procedures that accept and return a Variant containing an array", but I am afraid I have not reach that level of competency yet. Could someone explain to me in detail what I should do?
    Many thanks!

  2. #2
    Tom Ogilvy
    Guest

    Re: Public Array in a form?

    Declare it as public in a general module and it will be visible/usable in
    all modules.

    --
    Regards,
    Tom Ogilvy

    "lux770" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi,
    >
    > I am trying to create a user form that toggles the spreadsheet view
    > between the normal cells interior colors and colors that depend on the
    > cell content (e.g. yellow for a formula etc.)
    > I have created the form and the procedures that actually perform the
    > actions. My problem is that when loading the form, I want to store the
    > color of each cell of the UsedRange in an bi-dimensional array, so that
    > I can restore the original colors of the spreadsheet (making sense so
    > far? )
    > That array needs to be public because I will need to use it in several
    > procedures such as togglebutton1_change, userform_terminate etc.
    > Therefore I have tried to delare it at the top of the form's code as a
    > public variable.
    >
    > Unfortunately I get the following message: "-Constants, fixed-length
    > strings, arrays, user-defined types, and Declare statements not allowed
    > as Public members of an object module-"
    >
    > The VBA help suggests to "-use a set of Property procedures that accept
    > and return a Variant containing an array-", but I am afraid I have not
    > reach that level of competency yet. Could someone explain to me in
    > detail what I should do?
    > Many thanks!
    >
    >
    > --
    > lux770
    > ------------------------------------------------------------------------
    > lux770's Profile:

    http://www.excelforum.com/member.php...o&userid=24970
    > View this thread: http://www.excelforum.com/showthread...hreadid=390558
    >




  3. #3
    Registered User
    Join Date
    07-06-2005
    Posts
    6
    Thanks Tom, I have applied what you suggest and the form now loads correctly.
    Unfortunatelu as soon as I press one of the toggle buttons, I get the following message:
    "Error: Array or user-defined type expected"

    To give you some more details, I have created a procedure that calls that form in a new module. At the top of that module I have declared my array of integers as Public.

    This is the code for the toggle button and the procedure that is called:

    Please Login or Register  to view this content.


    It looks to me that calling the form works because it is done from the module where the array is declared, but then all actions within the form are performed from the form module, where there is no declaration for that array. Or am I missing something?

    Many thanks for your help.

  4. #4
    Tom Ogilvy
    Guest

    Re: Public Array in a form?

    I assume you are using xl2000 or later. I had this at the top of a general
    module:
    Public iCellsColors() As Variant
    Public bNormal As Boolean

    Sub Showform()
    Dim rng As Range
    bNormal = True
    Set rng = ActiveSheet.UsedRange
    ReDim iCellsColors(1 To rng.Rows.Count, _
    1 To rng.Columns.Count)
    For i = 1 To UBound(iCellsColors, 1)
    For j = 1 To UBound(iCellsColors, 2)
    iCellsColors(i, j) = Cells(i, j).Interior.ColorIndex
    Next
    Next
    UserForm1.Show

    End Sub

    In a userform Module I had basically your code slightly modified to act as a
    toggle:

    ' I made tglOriginal a commandbutton and used the boolean variable to
    control the toggle.

    Private Sub tglOriginal_Click()

    If bNormal Then
    For Each cell In ActiveSheet.UsedRange
    cell.Interior.ColorIndex = Int(Rnd() * 56 + 1)
    Next
    bNormal = False
    Else
    Restore_BkGrnd ActiveSheet.UsedRange, iCellsColors
    bNormal = True
    End If

    End Sub

    Private Sub Restore_BkGrnd(rngUsed As Range, iColorsArr() As Variant)

    Dim lMaxRows, lMaxCol As Long
    Dim l_Row, l_Col As Long

    lMaxRows = rngUsed.Rows.Count
    lMaxCol = rngUsed.Columns.Count

    For l_Row = 1 To lMaxRows
    For l_Col = 1 To lMaxCol

    ' my array was 1 based, so I removed the -1 in iColorsArr

    rngUsed.Cells(l_Row, l_Col).Interior.ColorIndex = iColorsArr(l_Row, l_Col)
    Next l_Col
    Next l_Row

    End Sub

    this worked fine for me.

    --
    Regards,
    Tom Ogilvy


    "lux770" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks Tom, I have applied what you suggest and the form now loads
    > correctly.
    > Unfortunatelu as soon as I press one of the toggle buttons, I get the
    > following message:
    > -"Error: Array or user-defined type expected"-
    >
    > To give you some more details, I have created a procedure that calls
    > that form in a new module. At the top of that module I have declared my
    > array of integers as Public.
    >
    > This is the code for the toggle button and the procedure that is
    > called:
    >
    >
    > Code:
    > --------------------
    > Private Sub tglOriginal_Change()
    >
    > If tglOriginal.Value = True Then
    > Restore_BkGrnd ActiveSheet.UsedRange, iCellsColors
    > End If
    >
    > End Sub
    >
    > Private Sub Restore_BkGrnd(rngUsed As Range, iColorsArr() As Variant)
    >
    > Dim lMaxRows, lMaxCol As Long
    > Dim l_Row, l_Col As Long
    >
    > lMaxRows = rngUsed.Rows.Count
    > lMaxCol = rngUsed.Columns.Count
    >
    > For l_Row = 1 To lMaxRows
    > For l_Col = 1 To lMaxCol
    > rngUsed.Cells(l_Row, l_Col).Interior.ColorIndex = iColorsArr(l_Row - 1,

    l_Col - 1)
    > Next l_Col
    > Next l_Row
    >
    > End Sub
    > --------------------
    >
    >
    > It looks to me that calling the form works because it is done from the
    > module where the array is declared, but then all actions within the
    > form are performed from the form module, where there is no declaration
    > for that array. Or am I missing something?
    >
    > Many thanks for your help.
    >
    >
    > --
    > lux770
    > ------------------------------------------------------------------------
    > lux770's Profile:

    http://www.excelforum.com/member.php...o&userid=24970
    > View this thread: http://www.excelforum.com/showthread...hreadid=390558
    >




  5. #5
    Registered User
    Join Date
    07-06-2005
    Posts
    6

    Thumbs up

    It works!!!!
    Apparently the key was to populate the array from the general module code, not the form module.

    I have made a few more change to make the form modeless and prevent errors when the user switches to another sheet/workbook.

    Thank you very much indeed, you've been very helpful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1