Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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 "Varne" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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" wrote: 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 "Varne" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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" wrote: 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" wrote: 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 "Varne" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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 "Mike H" wrote: 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" wrote: 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" wrote: 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 "Varne" wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
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" wrote: 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" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Driven Procedure
Looks like I misread your OP and am formatting red on the wrong sheet
change Set ws = Me.Worksheets(1) to Set ws = Me.Worksheets(2) ' or use "sheet-name" iso 2 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 "Varne" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
event procedure | Excel Programming | |||
help with an event driven sub argument please | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming | |||
user event driven macro issue | Excel Programming | |||
user event driven macro issue | Excel Programming |