frank_m
12-25-2011, 09:18 PM
If I use this code and I exit the sub before the second portion of the code runs, it works fine to scroll left or right one cell at a time depending on the selection made left or right.
But if I uncomment the Exit Sub '(2ND HALF) of code, which creates a shape to underline the selected row, my prevCol variable no longer effects the first half on the code, causing it to always scroll right no matter whether the selection is changed to the right or left.
Any idea how I can get it to work/ (by the way I do not want to use conditional formatting to highlight the selection, for reasons that it would take too long to explain. (Sample workbook attached)
Thanks
Edit: I noticed now that the 1st half of the code some times works, but most of the time does nothing. - But if I comment out the Exit sub to allow the 2nd half of the code to run, the 2nd half works fine, and the 1st half works, except not as expected.
- If I completely delete the 2nd half of the code, then the 1st half works fine :dunno
Option Explicit
Private prevCol As Integer
Private prevRow As Long
Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim iLeft As Integer, iTop As Long, iWidth As Integer, iHeight As Integer
Dim shp As Object
If target.Row > 2 Then
With ActiveSheet
If target.Column > 12 And target.Column > prevCol Then
ActiveWindow.SmallScroll ToRight:=1 ' scroll right 1, when selecting next cell to right
ElseIf target.Column < prevCol Then
ActiveWindow.SmallScroll ToRight:=-1 ' scroll left 1, when selecting next cell to left
End If
End With
End If
prevCol = target.Column
'//***//'//***//'//***//'//***//'//***//
Exit Sub 'Comment out the Exit Sub here to run (2ND HALF) of code. - You will see 1st half act goofy
'//***//'//***//'//***//'//***//'//***//
If Not prevRow = target.Row Then
For Each shp In ActiveSheet.Shapes
If shp.Name <> "CommandButton1" And shp.Width > 700 Then
shp.Delete
End If
Next
iTop = target.Top + target.Height
iHeight = 3
iLeft = target.EntireRow.Cells(3).Left
iWidth = target.EntireRow.Cells(3).Resize(, 28).Width
If target.Row > 1 And Selection.Rows.Count = 1 Then
Set shp = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=iLeft, Top:=iTop, Width:=iWidth, Height:=iHeight)
shp.Object.TakeFocusOnClick = False
shp.Name = "UnderlineRow"
End If
End If
prevRow = target.Row
End Sub
But if I uncomment the Exit Sub '(2ND HALF) of code, which creates a shape to underline the selected row, my prevCol variable no longer effects the first half on the code, causing it to always scroll right no matter whether the selection is changed to the right or left.
Any idea how I can get it to work/ (by the way I do not want to use conditional formatting to highlight the selection, for reasons that it would take too long to explain. (Sample workbook attached)
Thanks
Edit: I noticed now that the 1st half of the code some times works, but most of the time does nothing. - But if I comment out the Exit sub to allow the 2nd half of the code to run, the 2nd half works fine, and the 1st half works, except not as expected.
- If I completely delete the 2nd half of the code, then the 1st half works fine :dunno
Option Explicit
Private prevCol As Integer
Private prevRow As Long
Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim iLeft As Integer, iTop As Long, iWidth As Integer, iHeight As Integer
Dim shp As Object
If target.Row > 2 Then
With ActiveSheet
If target.Column > 12 And target.Column > prevCol Then
ActiveWindow.SmallScroll ToRight:=1 ' scroll right 1, when selecting next cell to right
ElseIf target.Column < prevCol Then
ActiveWindow.SmallScroll ToRight:=-1 ' scroll left 1, when selecting next cell to left
End If
End With
End If
prevCol = target.Column
'//***//'//***//'//***//'//***//'//***//
Exit Sub 'Comment out the Exit Sub here to run (2ND HALF) of code. - You will see 1st half act goofy
'//***//'//***//'//***//'//***//'//***//
If Not prevRow = target.Row Then
For Each shp In ActiveSheet.Shapes
If shp.Name <> "CommandButton1" And shp.Width > 700 Then
shp.Delete
End If
Next
iTop = target.Top + target.Height
iHeight = 3
iLeft = target.EntireRow.Cells(3).Left
iWidth = target.EntireRow.Cells(3).Resize(, 28).Width
If target.Row > 1 And Selection.Rows.Count = 1 Then
Set shp = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=iLeft, Top:=iTop, Width:=iWidth, Height:=iHeight)
shp.Object.TakeFocusOnClick = False
shp.Name = "UnderlineRow"
End If
End If
prevRow = target.Row
End Sub