Event Driven Procedure

[Varne]

Hi!

In an Excel workbook with 2 pages the 2nd is hidden. If it is unhidden the
following Macro should be triggered;

Sub Test ()

Worksheets(1).Cells(1,1).Interior.Colorindex=3

End Sub

Could someone help?

Thank You!

 

[Mike H]

Hi,

Right click the sheet tab of the 'first sheet' view code and paste this in
and try it

Private Sub Worksheet_Activate()
For x = 1 To Worksheets.Count
If Sheets(x).Visible Then
viz = viz + 1
End If
Next
If viz < 2 Then
Worksheets(1).Cells(1, 1).Interior.ColorIndex = 3
Else
Worksheets(1).Cells(1, 1).Interior.ColorIndex = xlNone
End If
End Sub


Mike

 

[Peter T]

Paste the following in the ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim cidx As Long
Dim ws As Worksheet

Set ws = Me.Worksheets(1)

With ws.Range("A1").Interior
cidx = .ColorIndex
' only apply if necessary to avoid loss of undo
If ws.Visible = xlSheetVisible Then
If cidx <> 3 Then .ColorIndex = 3
ElseIf cidx <> xlNone Then
.ColorIndex = xlNone
End If
End With

End Sub

Post back you are checking more than one cell, would need to change
slightly.

Regards,
Peter T

 

[Varne]

Hi!

It works but only after 2 events. Unhiding the second sheet and activating
the first sheet. If possible could you please adjust the codes to show red
immediately after unhiding the second sheet.

Thank You!

 

[Mike H]

Hi,

I'm not sure what you mean the act of unhiding the second sheet selects it
so you can't see in the first sheet whether A1 is red or not. You could put
this in the other sheet that gets hidden

Private Sub Worksheet_Activate()
Worksheets(1).Cells(1, 1).Interior.ColorIndex = xlNone
End Sub

Mike

 

[Varne]

Hi!

Sorry if I am asking for something not possible!

I cannot see but I can deduce if the cells(1,1) in Sheet1 has gone red or
not by extending your codes as given below. Cell(1,1) does not go red when I
unhide Sheet2.

If I do not want any codes written in 'MS Excel Object Sheet2' or in 'MS
Excel Object ThisWorkbook' is there an other way to make this happen?

Like the inbuilt 'Activate' event can't we create an 'Unhide' event?

Thanks.

Private Sub Worksheet_Activate()

For x = 1 To Worksheets.Count
If Sheets(x).Visible = True Then
viz = viz + 1
End If
Next
If viz > 1 Then
Worksheets(1).Cells(1, 1).Interior.ColorIndex = 3
Else
Worksheets(1).Cells(1, 1).Interior.ColorIndex = xlNone
End If

If Worksheets(1).Cells(1, 1).Interior.ColorIndex = 3 Then
Worksheets(2).Cells(1, 1).Interior.ColorIndex = 3
Else
Worksheets(2).Cells(1, 1).Interior.ColorIndex = xlNone
End If

End Sub

 

[Varne]

Hi!

Sorry for responding late!

Worksheets(1).cells(1,1) goes red when Worksheets(2) in unhidden. However
when Worksheets(2) is hidden the red remains. As I am not familiar with your
coding style my adjustments did not work.

Also I found something else;

Usually I keep the procedures in Modules and made them called (For example
when a sheet gets activated) on events. Your codes could not be handled that
way.

Please reply.

Thanks.

 

[Peter T]

Looks like I misread your OP and am formatting red on the wrong sheet

change
Sheet hide/unhide does not of itself trigger an event. However it normally
(always if done manually) it has the effect of activating or deactivating a
sheet in the activeworkbook. The simplest way is to trap the activate event
of all sheets at workbook level and format the cell as required, but only if
necessary (see note re preserving undo).

In the Workbook_SheetActivate (in the ThisWorkbook module) you could indeed
call a routine in an ordinary module to do the work, no need to pass the
activated sheet object in this case but change "Me" to ThisWorkbook. There's
good reason to minimise code in "object" modules but for this simple routine
probably not much point to move it elsewhere.

If you don't want any code at all in sheet/Thisworkbook modules you can trap
events in a class module using "WithEvents", you can trap events of any or
all other workbooks so your code could go in (say) an addin or your
Personal.

Regards,
Peter T