tamdunk
01-19-2017, 07:41 AM
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.
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.