Anything else that would stop Undo Function in Spreadsheet

[miricleman]

Hi,

I have been developing a dashboard for my business and have been using some of the way other people concepts on designing their dashboards. All was going well until the other day. Went to continue to work on the dashboard yesterday and found that i could not undo anything once i had moved out of a cell.

So i had a look on the net on how to solve this problem. I have ensured that there is no VBA code that is affecting the workbook(WB). In Fact there is very little code in VBA in the WB. I rebooted and opened up a new workbook and found that i could undo in this okay, until i opened up the problem WB. Thus i can only assume it is the WB that is causing the issue and no regedit issue. I have checked the name manger for any issue and there does not seem to be. Was wondering if any form controls can casue this to happen?

Is there anywhere else i could look? to resolve this issue with this WB as i do not really want to start over again.

Any help would be great.

Thanks

 

[miricleman]

Ok so i have found the offending VBA code, but i am not sure how to loop this only to work when the cell B52 is change and if anything else in the workbook is changed i do not want the code to run stopping me from undoing

(BTW - i use Option form buttons to change the value in "B52")

Can anyone help???

Private Sub Worksheet_Calculate()
     
    
     If Range("b52").Value = "Temuka" Then
          Me.Shapes("Rectangle 216").Visible = True
     Else
            Me.Shapes("Rectangle 216").Visible = False
     End If
  
     If Range("b52").Value = "Toll" Then
          Me.Shapes("Rectangle 215").Visible = True
    Else
            Me.Shapes("Rectangle 215").Visible = False
     End If
   
     
     If Range("b52").Value = "Temuka" Then
          Me.Shapes("Chart 217").Visible = True
     Else
          Me.Shapes("Chart 217").Visible = False
     End If
  
     
     If Range("b52").Value = "Waharoa" Then
          Me.Shapes("Chart 218").Visible = True
     Else
            Me.Shapes("Chart 218").Visible = False
     End If
      
   
     If Range("b52").Value = "Toll" Then
         Me.Shapes("Chart 219").Visible = True
     Else
            Me.Shapes("Chart 219").Visible = False
     End If
     
End Sub

 

[Jaafar Tribak]

Is there a formula in cell b52 ? If not, then instead of using the worksheet_calculate event use the worksheet_change event and check if the Target argument corresponds to cel b52.

 

[miricleman]

Hi

B52 is just an linking cell to another cell in the workbook.

i think i ended up solving it by luck more than good coding. i ended up putting a inverted coma in front of the "Private sub Worksheet Calculate" to turn of the macro so i could use undo. This turned it from a worksheet task to a general declaration. It seems that this was all i needed to do. Still scratching my head about this but i am sure i will figure it out sometime soon.

Thanks for responding.

'Private Sub Worksheet_Calculate()


Dim KeyCell As Range
Set KeyCell = Range("B52")



 If Not Intersect(KeyCell, Me.Range("b52")) Is Nothing Then
     
     If Range("b52").Value = "Temuka" Then
          Me.Shapes("Rectangle 216").Visible = True
     Else
            Me.Shapes("Rectangle 216").Visible = False
     End If
  
     If Range("b52").Value = "Toll" Then
          Me.Shapes("Rectangle 215").Visible = True
    Else
            Me.Shapes("Rectangle 215").Visible = False
     End If
   
     
  
     If Range("b52").Value = "Temuka" Then
          Me.Shapes("Chart 217").Visible = True
     Else
          Me.Shapes("Chart 217").Visible = False
     End If
  
  
   
     If Range("b52").Value = "Waharoa" Then
          Me.Shapes("Chart 218").Visible = True
     Else
            Me.Shapes("Chart 218").Visible = False
     End If
   
   
   
     If Range("b52").Value = "Toll" Then
         Me.Shapes("Chart 219").Visible = True
     Else
            Me.Shapes("Chart 219").Visible = False
     End If
     
End If

End Sub