Code issue
 

Hi all,
I have a workbook with a fair amount of code written into it. I emailed
myself a copy of the workbook home on the weekend to work on it and kept
getting an error with one of the macros at home. I am (was) using the SAME
version of Excel - 2002 SP3 until I got completely frustrated and dropped the
$$$ for 2007 for my PC at home. Same problem.
The part of the code that is hanging is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, Cell As Range
Set rng = Worksheets("Main").Range("C8:C17")
If Not Intersect(Target, rng) Is Nothing Then
For Each sh1 In Worksheets
ERROR --- bVisible = False
If LCase(sh1.Name) < "main" Then
For Each Cell In rng
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(Cell.Value)
On Error GoTo 0
If Not sh Is Nothing Then
If sh.Name = sh1.Name Then
bVisible = True
Exit For
End If
End If
Next Cell
If bVisible Then
sh1.Visible = xlSheetVisible
Else
sh1.Visible = xlSheetHidden
End If
End If
Next sh1
End If
End Sub

Any ideas on how to fix this issue? It seems that the same libraries /
add-ins are installed.

Thanks in advance,
Tom

Code issue
 

Not sure if this is the issue - but where is bVisible defined?

"Tom" wrote:

Hi all,
I have a workbook with a fair amount of code written into it. I emailed
myself a copy of the workbook home on the weekend to work on it and kept
getting an error with one of the macros at home. I am (was) using the SAME
version of Excel - 2002 SP3 until I got completely frustrated and dropped the
$$$ for 2007 for my PC at home. Same problem.
The part of the code that is hanging is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, Cell As Range
Set rng = Worksheets("Main").Range("C8:C17")
If Not Intersect(Target, rng) Is Nothing Then
For Each sh1 In Worksheets
ERROR --- bVisible = False
If LCase(sh1.Name) < "main" Then
For Each Cell In rng
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(Cell.Value)
On Error GoTo 0
If Not sh Is Nothing Then
If sh.Name = sh1.Name Then
bVisible = True
Exit For
End If
End If
Next Cell
If bVisible Then
sh1.Visible = xlSheetVisible
Else
sh1.Visible = xlSheetHidden
End If
End If
Next sh1
End If
End Sub

Any ideas on how to fix this issue? It seems that the same libraries /
add-ins are installed.

Thanks in advance,
Tom

Code issue
 

Hi
1. Do you mean .Visible = False rather than bVisible = False
2. Aternatively (unlikely though), is bVisible a variable that has not
been declared?!

regards
Paul

On May 29, 10:00 am, Tom wrote:
Hi all,
I have a workbook with a fair amount of code written into it. I emailed
myself a copy of the workbook home on the weekend to work on it and kept
getting an error with one of the macros at home. I am (was) using the SAME
version of Excel - 2002 SP3 until I got completely frustrated and dropped the
$$$ for 2007 for my PC at home. Same problem.
The part of the code that is hanging is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, Cell As Range
Set rng = Worksheets("Main").Range("C8:C17")
If Not Intersect(Target, rng) Is Nothing Then
For Each sh1 In Worksheets
ERROR --- bVisible = False
If LCase(sh1.Name) < "main" Then
For Each Cell In rng
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(Cell.Value)
On Error GoTo 0
If Not sh Is Nothing Then
If sh.Name = sh1.Name Then
bVisible = True
Exit For
End If
End If
Next Cell
If bVisible Then
sh1.Visible = xlSheetVisible
Else
sh1.Visible = xlSheetHidden
End If
End If
Next sh1
End If
End Sub

Any ideas on how to fix this issue? It seems that the same libraries /
add-ins are installed.

Thanks in advance,
Tom

Code issue
 

Hi
looking further down your code, bVisible is a variable. You need to
put
Dim bVisible as Boolean

at the top of your code. I would guess you have "Option Explicit"
turned on at home but not at work. This means you have to explicitly
declare each variable before you use it (which is good).
regards
Paul

On May 29, 10:15 am, wrote:
Hi
1. Do you mean .Visible = False rather than bVisible = False
2. Aternatively (unlikely though), is bVisible a variable that has not
been declared?!

regards
Paul

On May 29, 10:00 am, Tom wrote:



Hi all,
I have a workbook with a fair amount of code written into it. I emailed
myself a copy of the workbook home on the weekend to work on it and kept
getting an error with one of the macros at home. I am (was) using the SAME
version of Excel - 2002 SP3 until I got completely frustrated and dropped the
$$$ for 2007 for my PC at home. Same problem.
The part of the code that is hanging is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, Cell As Range
Set rng = Worksheets("Main").Range("C8:C17")
If Not Intersect(Target, rng) Is Nothing Then
For Each sh1 In Worksheets
ERROR --- bVisible = False
If LCase(sh1.Name) < "main" Then
For Each Cell In rng
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(Cell.Value)
On Error GoTo 0
If Not sh Is Nothing Then
If sh.Name = sh1.Name Then
bVisible = True
Exit For
End If
End If
Next Cell
If bVisible Then
sh1.Visible = xlSheetVisible
Else
sh1.Visible = xlSheetHidden
End If
End If
Next sh1
End If
End Sub

Any ideas on how to fix this issue? It seems that the same libraries /
add-ins are installed.

Thanks in advance,
Tom- Hide quoted text -

- Show quoted text -

Code issue
 

Hi Paul,
I will add that to the code to see if it makes a difference at home and also
check into see if I have option explicit on/off as well. Thanks for taking a
look at it and for the response.

Tom

" wrote:

Hi
looking further down your code, bVisible is a variable. You need to
put
Dim bVisible as Boolean

at the top of your code. I would guess you have "Option Explicit"
turned on at home but not at work. This means you have to explicitly
declare each variable before you use it (which is good).
regards
Paul

On May 29, 10:15 am, wrote:
Hi
1. Do you mean .Visible = False rather than bVisible = False
2. Aternatively (unlikely though), is bVisible a variable that has not
been declared?!

regards
Paul

On May 29, 10:00 am, Tom wrote:



Hi all,
I have a workbook with a fair amount of code written into it. I emailed
myself a copy of the workbook home on the weekend to work on it and kept
getting an error with one of the macros at home. I am (was) using the SAME
version of Excel - 2002 SP3 until I got completely frustrated and dropped the
$$$ for 2007 for my PC at home. Same problem.
The part of the code that is hanging is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, Cell As Range
Set rng = Worksheets("Main").Range("C8:C17")
If Not Intersect(Target, rng) Is Nothing Then
For Each sh1 In Worksheets
ERROR --- bVisible = False
If LCase(sh1.Name) < "main" Then
For Each Cell In rng
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(Cell.Value)
On Error GoTo 0
If Not sh Is Nothing Then
If sh.Name = sh1.Name Then
bVisible = True
Exit For
End If
End If
Next Cell
If bVisible Then
sh1.Visible = xlSheetVisible
Else
sh1.Visible = xlSheetHidden
End If
End If
Next sh1
End If
End Sub

Any ideas on how to fix this issue? It seems that the same libraries /
add-ins are installed.

Thanks in advance,
Tom- Hide quoted text -

- Show quoted text -