Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issue with being able to select 64 bit Data Sources in Excel 2007 | Excel Discussion (Misc queries) | |||
List Box - Multi Select Issue | Excel Worksheet Functions | |||
Select Case - issue | Excel Discussion (Misc queries) | |||
VBA Range Issue : Cannot programatically select more than 42 columns | Excel Programming | |||
Select Sheet then Select Range | Excel Programming |