Using Target.Address in Excel 98

[Noemi]

Hi
I have a code which as Target.Address which works in Excel 2000 but will not
work in excel 98.

Does anyone know what code to use for when a workbook change is to take
affect.

The current code which works in Excel 2000 is below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$26" Then
If Range("G26") = "Other" Then
ActiveSheet.Unprotect
Sheets("Recording Sheet").Range("E28") = "Please speficy:"
Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 2
Sheets("Recording Sheet").Range("G28:I28") = ""
Sheets("Recording Sheet").Range("G28:I28").Locked = False
ActiveSheet.Protect
Sheets("Recording Sheet").Range("G28:I28").Select
ElseIf Range("G26").Value = "" Then
ActiveSheet.Unprotect
Sheets("Recording Sheet").Range("E28") = ""
Sheets("Recording Sheet").Range("G28:I28") = ""
Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
Sheets("Recording Sheet").Range("G28:I28").Locked = True
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Sheets("Recording Sheet").Range("E28") = ""
Sheets("Recording Sheet").Range("G28:I28") = ""
Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
Sheets("Recording Sheet").Range("G28:I28").Locked = True
ActiveSheet.Protect
End If
End If
End Sub

Any help would be appreciated.

Thanks
Noemi

 

[Jim Rech]

You would have a better chance of getting help if you tell us exactly where
the code breaks down.

One no-no you've commited though is not turning off events in the sheet
change event handler when your code is itself making changes to the sheet.
This causes an endless loop in theory. So add an Application.EnableEvents =
False before making a sheet change, and set the same to True after your last
sheet change.

--
Jim
| Hi
| I have a code which as Target.Address which works in Excel 2000 but will
not
| work in excel 98.
|
| Does anyone know what code to use for when a workbook change is to take
| affect.
|
| The current code which works in Excel 2000 is below:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| If Target.Address = "$G$26" Then
| If Range("G26") = "Other" Then
| ActiveSheet.Unprotect
| Sheets("Recording Sheet").Range("E28") = "Please speficy:"
| Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 2
| Sheets("Recording Sheet").Range("G28:I28") = ""
| Sheets("Recording Sheet").Range("G28:I28").Locked = False
| ActiveSheet.Protect
| Sheets("Recording Sheet").Range("G28:I28").Select
| ElseIf Range("G26").Value = "" Then
| ActiveSheet.Unprotect
| Sheets("Recording Sheet").Range("E28") = ""
| Sheets("Recording Sheet").Range("G28:I28") = ""
| Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
| Sheets("Recording Sheet").Range("G28:I28").Locked = True
| ActiveSheet.Protect
| Else
| ActiveSheet.Unprotect
| Sheets("Recording Sheet").Range("E28") = ""
| Sheets("Recording Sheet").Range("G28:I28") = ""
| Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
| Sheets("Recording Sheet").Range("G28:I28").Locked = True
| ActiveSheet.Protect
| End If
| End If
| End Sub
|
| Any help would be appreciated.
|
| Thanks
| Noemi

 

[Don Guillett]

I know of no excel 98. Please clarify. I just tested
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$2" Then MsgBox "Hi"
End Sub
in xl97sr2 on xp home and it worked just fine.
is recordingsheet your active sheet?
=====something like this would be cleaner
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> "$G$26" Then Exit Sub
With Target.Worksheet
.Unprotect
If UCase(Target) = "OTHER" Then
.Range("E28") = "Please speficy:"
.Range("G28:I28").Interior.ColorIndex = 2
.Range("G28:I28") = ""
.Range("G28:I28").Locked = False
Else
.Range("E28,G28:I28") = ""
.Range("G28:I28").Interior.ColorIndex = 1
.Range("G28:I28").Locked = True
End If
.Protect
End With
End Sub