Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blinking Cell (Excel 2000/XP)

    I have one sheet in my workbook which, when activated, groups itself with several other sheets. When you select a different sheet, I have the first sheet's deactivate event fire to ungroup the sheets. It was working fine until today.

    Now, when the user selects all but one of the other sheets, it works fine. But if he selects this one particular sheet, I can get Msgboxes to fire in the deactivate event, but none of the other code seems to run. Breaks in the code are ignored.

    When the newly selected sheet appears on the screen, still grouped with all the others, one of its cells is blinking. It appears to be the same cell that was last active on the first sheet. There are some formulas on the second sheet that use user-defined functions, but when I took them out, it didn't make any difference. I have no idea what a blinking cell in Excel might mean. There is no code associated with this worksheet, but when I added code in the Activate event, that code didn't run, either, except, as before, the message box.

    I was just playing with the workbook a bit, and it seems as though this worksheet ALWAYS blinks when you come to it from another sheet. What does a blinking cell mean?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Blinking Cell (Excel 2000/XP)

    Could you post the code?

    If the messageboxes run in code, the event is triggering. Perhaps the code just appears to do nothing.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blinking Cell (Excel 2000/XP)

    I'm not sure this will help without the rest of the workbook, but below is the code for the deactivate event. The variable bSelected is set to True when the worksheet that triggers the grouping is selected (see Worksheet Activate event below). If I put a break on the "If beSelected" line, it breaks there when I select any of the other worksheets except the one in question, the one with the blinking cell. When I select that one, the code runs (msgboxes at start and end of the code both fire) but it doesn't stop at the break. The code within the If statement runs (a msgbox inserted there also fires), but the sheets are not ungrouped. There is no code at all in any of the other sheets, including the misbehaving one. The only other code that should come into play is in the Workbook sheet activate event. It is also below.

    Private Sub Worksheet_Deactivate()
    Dim strName As String

    'ActiveSheet.Select
    strName = ActiveSheet.Name

    If bSelected = True Then
    strName = ActiveSheet.Name
    bSelected = False
    Sheets(strName).Select
    End If

    End Sub

    This is the sheet activate event:

    Private Sub Worksheet_Activate()
    Dim Cell As Range
    Dim arrSheets() As String
    Dim i As Integer

    bSelected = True

    i = -1
    'Loop through the list of grouped sheets
    For Each Cell In Worksheets("GroupedSheets").Range("GroupedSheets")
    i = i + 1
    ReDim Preserve arrSheets(i)
    arrSheets(i) = Cell
    Next Cell

    'Group the sheets
    Sheets(arrSheets).Select

    End Sub


    Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    'Show new toolbar, hide previous toolbar
    On Error Resume Next
    strSelected = Sh.Name
    Application.CommandBars("Budget Toolbar").Controls(strSelected).Visible = True
    'Application.CommandBars("Budget Toolbar").Controls(strPrevious).Visible = False
    strPrevious = strSelected
    On Error GoTo 0
    End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Blinking Cell (Excel 2000/XP)

    I am not sure what you are trying to accomplish. I am not sure what bSelected is supposed to do. Where is this variable declared?

    If the first cell in the "groupedsheets" range is not the sheet that has the sheetcode then a new sheet gets activated, triggering your deactivate sheet code which gets rid of the grouping (changing to "Sheets(strName).Activate" will fix this so you keep the grouping), it still will trigger the workbook activate sheet.

    What is the list of names in "grouped sheets" and which sheet has that sheetactivate/deactivate code in it?
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blinking Cell (Excel 2000/XP)

    Let's see if I can simplify this a bit. I got rid of all the code that wasn't relevant to the problem, and this is what I have left:

    Let's call the first worksheet "Trigger." On Trigger's Activate event, the code loops through a list of worksheet names (contained on another sheet) and groups them all. This list will be dynamic, thus the array.

    When the user selects another sheet, I need the group to be "disbanded," so to speak. The following code in Trigger's deactivate event does this correctly:

    ActiveSheet.Select

    EXCEPT when the user selects this one particular sheet. It is one that is part of the group. And when it is selected, the sheets stay grouped. And I have a blinking cell on that sheet, which may or may not have anything to do with it. I just need to make sure that once the user is off of Trigger, the grouping is removed.

    Adding Me.Select to the problem worksheet's Activate event doesn't work, either.

    Any ideas? Thanks!

    --Karyl

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Blinking Cell (Excel 2000/XP)

    What code is on the trigger sheet activate and deactivate? Do any other sheets have activate /deactivate code?

    You have workboob sheet activate which runs when any sheet is activated so some sheets do both codes. Can you attach a small file with the code in place (only the "problem areas") and explain how to replicate the problem?

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blinking Cell (Excel 2000/XP)

    I don't see how "ActiveSheet.Select" is going to do what you want. There are three situations that need to be considered:

    1- All sheets in the workbook are selected an therefore included in your group. In this case, clicking on any sheet in the workbook other than the active sheet will automatically unselect all sheets and then select the sheet that was clicked on. Having ActiveSheet.Select in the deactivate event does nothing extra.

    2- At least one sheet in the workbook is not selected and the sheet clicked on is not a selected sheet. In this case, again Excel will automatically unselect all of the selected sheets and select the one that was click on. Again, having ActiveSheet.Select in the deactivate routine does nothing extra.

    3- At least one sheet in the workbook is not selected and the sheet clicked on is one of the selected sheets. In this case, Excel will leave all of the sheets selected and change the active sheet to the one clicked on. Having the ActiveSheet.Select in the deactivate event routine sould not do anything since it is selecting a sheet that is in the collection of selected sheets, and that sould not cause the selected sheets to be unselected. Only selecting a sheet that is not in the collection will cause the collection to be unselected.

    The easy way to accomplish what you want would be to insert another worksheet into the workbook and name it "HiddenSheet" (or whatever you want if you change the code below), and then hide this sheet. Then put code like this into the Trigger sheet deactivate event routine:

    <pre>Private Sub Worksheet_Deactivate()
    Dim oSht As Worksheet
    Set oSht = ActiveSheet
    Application.ScreenUpdating = False
    Worksheets("HiddenSheet").Visible = True
    Worksheets("HiddenSheet").Select
    Worksheets("HiddenSheet").Visible = False
    oSht.Select
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blinking Cell (Excel 2000/XP)

    I don't know why the code was working, either, except it was. It could be related to the other event that is firing on Workbook_SheetActivate. But it is just changing which toolbars are displayed based on the sheet selection. It works the same for all the sheets, and none of the other sheets in the group have any code attached.

    When I used your code, it worked as it should, although I still had a blinking cell in the problem sheet. But at least the group was ungrouped! Interestingly, when I changed the code to just select one of the other sheets, one that wasn't in the group, only the three behaving sheets worked. Trying to switch to the misbehaving sheet left them all grouped. But as long as switching to a hidden sheet does the trick, for whatever reason, it solves the problem for now. And since I'm supposed to be demo-ing this tomorrow, that a BIG relief!

    Many thanks!

    --Karyl

Posting Permissions

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