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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
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 |