Hi,

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

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

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

--
lux77
-----------------------------------------------------------------------
lux770's Profile: http://www.excelforum.com/member.php...fo&userid=2497
View this thread: http://www.excelforum.com/showthread.php?threadid=39055

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

--
Regards,
Tom Ogilvy

"lux770" wrote in
message ...

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

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

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" wrote in
message ...

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

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

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

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

--
lux77
-----------------------------------------------------------------------
lux770's Profile: http://www.excelforum.com/member.php...fo&userid=2497
View this thread: http://www.excelforum.com/showthread.php?threadid=39055