Range.Select Issue
 

I have a workbook on sheet 1, at D8 i have a dropdown validation that points
to values in Range("D15:D20"). I have this code in the Worksheet_Change
Event.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$8" Then
Range("K1").Select
End If

End Sub


Everything works as expected.

Now, in the workbook that I am having issues in, the Select method is not
working and I cannot figure out why. Here is my code. I apologize for the
length.

The sheet is protected with an editable range, all cells are locked except
for the ones listed in the edit range
....
With ActiveWorksheet
.Unprotect
While .Protection.AllowEditRanges.Count0
.Protection.AllowEditRanges(1).Delete
DoEvents
Wend
.Protection.AllowEditRanges.Add Title:="Editable_Range",
Range:=Range("H3,B5,F5,C6,I6,L5:L6,L8,D11:E13,G11, H13,L10,B8:B10")
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
....

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo StepLogicErr

'For Berevity
Dim mSteps() As String
Dim iLoop As Integer, jLoop As Integer, bFound As Boolean
ReDim mSteps(3)

bFound = False 'Catch all Flag

mSteps(0) = "$H$3"
mSteps(1) = "$B$5"
mSteps(2) = "" 'Reserved for later use
mSteps(3) = "$C$6"

'Loop over all steps to determine which we are at
For iLoop = 0 To UBound(mSteps)

'Determine which step we are on
If Target.Address = mSteps(iLoop) Then

'Loop over remaining steps until we find a valid address
jLoop = iLoop

Do While 1 = 1
jLoop = jLoop + 1

'If we are out of steps, move to the next sheet
If jLoop UBound(mSteps) Then

'Select next sheet
If Not Target.Worksheet Is Nothing Then
Target.Worksheet.Next.Activate
bFound = True
Exit For
End If

Else

'If not a reserved step, then select it
If Not mSteps(jLoop) = "" Then

If Not Range(mSteps(jLoop)).Locked Then
Range(mSteps(jLoop)).Select 'No error generated and yet
nothing happens
bFound = True
Exit For
End If

End If

End If

Loop

End If

Next iLoop

If Not bFound Then
'If we couldn't determine where we where supposed to go
'reposition back at the starting point
Range("Start_Position").Select
'Provide a clue that something is wrong
MsgBox "I don't know how it happened, but you managed to break me and I
lost my place. I will attempt to recover from your sly fox ways, however;
you should contact someone about your treachery.", vbOKOnly
End If

Exit Sub

StepLogicErr:
Debug.Print "Error in step logic:" & Target.Address & vbCrLf &
Err.Description
Err.Clear
Resume Next

End Sub

Range.Select Issue
 

Woo Hoo! I found my problem and have a resolution for it.


If turns out that I have user defined functions in some of my formulas and
they are causing the issue. All I had to do was turn off calculation and
the problem is resolved. So I turn of calculation and manually calculate as
appropriate. Since I am managing the input from worksheet_change() this is
not an inconvenient solution

Too bad I will have to be cautious of the user enabling calculations, but
this is a working solution and that is my main concern. Man that took a
long time to figure out. Thanks anyway gang.

Sample formula =
"=IF(C33="",1,C33)*IF(C31="",1,C31)*IF(C30="",1,C3 0)*IF(C29="",1,IF(GetIsNumber(C29),C29,0))*IF(C28= "",1,C28)*IF(C27="",1,C27)*IF(C20="",1,C20)*IF(C19 ="",1,C19)*IF(C18="",1,C18)*IF(C17="",1,C17)*IF(C1 6="",1,C16)"

Private Sub Workbook_Open()

With Application
.Calculation = xlCalculationManual
.MaxChange = 0.001
End With

ActiveWorkbook.PrecisionAsDisplayed = False

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
... Selection & Management Code
Calculate
End Sub

--=Module UDF=--

Public Function DoDateAdd(Incrementer, IncValue, Value) As Date

DoDateAdd = DateAdd(Incrementer, IncValue, Value)

End Function

Public Function GetIsNumber(Value) As Boolean

GetIsNumber = IsNumeric(Value)

End Function

Public Function DoDatediff(Incrementer, Date1, Date2) As Variant

DoDatediff = DateDiff(Incrementer, Date1, Date2)

End Function

Public Function IsBetween(Value, Test1, Test2) As Boolean

If Value = Test1 And Value <= Test2 Then
IsBetween = True
Else
IsBetween = False
End If

End Function


"AMDRIT" wrote in message
...
I have a workbook on sheet 1, at D8 i have a dropdown validation that
points to values in Range("D15:D20"). I have this code in the
Worksheet_Change Event.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$8" Then
Range("K1").Select
End If

End Sub


Everything works as expected.

Now, in the workbook that I am having issues in, the Select method is not
working and I cannot figure out why. Here is my code. I apologize for
the length.

The sheet is protected with an editable range, all cells are locked except
for the ones listed in the edit range
...
With ActiveWorksheet
.Unprotect
While .Protection.AllowEditRanges.Count0
.Protection.AllowEditRanges(1).Delete
DoEvents
Wend
.Protection.AllowEditRanges.Add Title:="Editable_Range",
Range:=Range("H3,B5,F5,C6,I6,L5:L6,L8,D11:E13,G11, H13,L10,B8:B10")
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo StepLogicErr

'For Berevity
Dim mSteps() As String
Dim iLoop As Integer, jLoop As Integer, bFound As Boolean
ReDim mSteps(3)

bFound = False 'Catch all Flag

mSteps(0) = "$H$3"
mSteps(1) = "$B$5"
mSteps(2) = "" 'Reserved for later use
mSteps(3) = "$C$6"

'Loop over all steps to determine which we are at
For iLoop = 0 To UBound(mSteps)

'Determine which step we are on
If Target.Address = mSteps(iLoop) Then

'Loop over remaining steps until we find a valid address
jLoop = iLoop

Do While 1 = 1
jLoop = jLoop + 1

'If we are out of steps, move to the next sheet
If jLoop UBound(mSteps) Then

'Select next sheet
If Not Target.Worksheet Is Nothing Then
Target.Worksheet.Next.Activate
bFound = True
Exit For
End If

Else

'If not a reserved step, then select it
If Not mSteps(jLoop) = "" Then

If Not Range(mSteps(jLoop)).Locked Then
Range(mSteps(jLoop)).Select 'No error generated and yet
nothing happens
bFound = True
Exit For
End If

End If

End If

Loop

End If

Next iLoop

If Not bFound Then
'If we couldn't determine where we where supposed to go
'reposition back at the starting point
Range("Start_Position").Select
'Provide a clue that something is wrong
MsgBox "I don't know how it happened, but you managed to break me and I
lost my place. I will attempt to recover from your sly fox ways, however;
you should contact someone about your treachery.", vbOKOnly
End If

Exit Sub

StepLogicErr:
Debug.Print "Error in step logic:" & Target.Address & vbCrLf &
Err.Description
Err.Clear
Resume Next

End Sub