Corrupt data in workbook?

[tamdunk]

Hi, I don't really knwo where to start with this problem so I'll try and make it as brief as possible, any suggestions would be appreciated.

I'm developing a workbook which is essentially a set of worksheets with various sets of information (it's mainly just links to various guidance related to my work)

I've noticed that when I try to group certain objects (shapes/buttons etc), the system sometimes freezes. Looking into this suggested there was some corrupt data somewhere in the file.

The odd thing is when I first load the workbook I can group objects together fine, and when I go into different worksheets I can still group & ungroup with no issues. The problem specifically arises when I click on the home button, then try to group or ungroup it freezes up the workbook. This suggests an issue with the home macro, but when I go through it in break mode step by step, then it still works fine. It's only when I run it at normal speed that it screws up the workbook. This makes me think that it's maybe not an issue with the code, but something else.

Does anyone have any ideas at what could be causing this. I can provide some parts of the code if anyone thinks it will help, but it's a bit complicated and interlinked so I wouldn't really know what to include.


Sub Home()

Application.ScreenUpdating = True
'<<Button Press>>
Dim vTopType As Variant
Dim iTopInset As Integer
Dim iTopDepth As Integer
Dim mpid As Integer
Dim role As String

'Record original button properties
With ActiveSheet.Shapes(Application.Caller).ThreeD
vTopType = .BevelTopType
iTopInset = .BevelTopInset
iTopDepth = .BevelTopDepth
End With

'Button Down
With ActiveSheet.Shapes(Application.Caller).ThreeD
.BevelTopType = msoBevelSoftRound
.BevelTopInset = 2
.BevelTopDepth = 4
End With
Application.ScreenUpdating = True

'Button Up - set back to original values
With ActiveSheet.Shapes(Application.Caller).ThreeD
.BevelTopType = vTopType
.BevelTopInset = iTopInset
.BevelTopDepth = iTopDepth
End With


'Find Role
ActiveWorkbook.Sheets("User Roles").Visible = xlSheetVisible
Sheets("User Roles").Select
role = Range("E1").Value

'Select screen based on role
Select Case role
Case "Adv"
ActiveWorkbook.Sheets("advisers").Visible = xlSheetVisible
Sheets("advisers").Select
Case "TL"
ActiveWorkbook.Sheets("Team Leaders").Visible = xlSheetVisible
Sheets("Team Leaders").Select
Case Else
Sheets("advisers").Select

End Select

End Sub

This is the home sub code It take you to a different page depending on the employee role. The code for each worksheet activation page is basically just hiding the other worksheets in the workbook. Anyone have any clue what might be causing this? When I try to open and repair the workbook it just crashes instantly.

[SamT]

This effectively does nothing, although it is run completely through every time "Sub Home" runs. Note I only removed the redundant "With... End With"s.

With ActiveSheet.Shapes(Application.Caller).ThreeD
vTopType = .BevelTopType
iTopInset = .BevelTopInset
iTopDepth = .BevelTopDepth

.BevelTopType = msoBevelSoftRound
.BevelTopInset = 2
.BevelTopDepth = 4

.BevelTopType = vTopType
.BevelTopInset = iTopInset
.BevelTopDepth = iTopDepth
End With
That code will run so fast you can't see it make any esthetic changes, but it might be causing problems elsewhere.

You don't need to see or select a sheet to use it

'Find Role
'ActiveWorkbook.Sheets("User Roles").Visible = xlSheetVisible
'Sheets("User Roles").Select
'role = Range("E1").Value

'Select screen based on role
Select Case Sheets("User Roles").Range("E1")
'etcThis will also reduce CPU load.

Avoid "ActiveWorkbook" as much as possible, even when working with multiple Workbooks. Too many things outside your influence can change the "Active" object even while code is running. If you must use "ActiveWorkbook," immediately assign it to a variable and use the variable in your code.

If the code only works on the book it is in, do not use "ActiveWorkbook" at all. "ThisWorkbook" is acceptable when you must differentiate between this and other Workbooks.

[SamT]

Try posting again.