Re : Excel event handler Worksheet_SelectionChange

[TKT-Tang]

Re : Excel event handler Worksheet_SelectionChange

Mr. Mike,

I was eager to attempt something useful (believe me) for myself.
Nevertheless, the on-going programming exercise could hardly be spoken
of neutrally ; it's in a state of quagmire and so, it's not hereby
presentable.

I have attempted to install a Scrollbar on an Excel Worksheet ; and
then, the Scrollbar should avail itself at an instant notice (namely,
a single click) by virtue of SelectionChange. I would have deployed
BeforeDoubleClick. However, in comparison, the latter summon could
only be served with twice as much effort, sigh.

The following concoction (for which I must hasten to offer apology) is
merely an instance to illustrate the plight over the suspension of
Excel Cut-and-Paste operations.

Private Sub ScrollBar1_Change()

ActiveCell = ActiveCell.Address(ReferenceStyle:=xlR1C1)

AcellRow = ActiveCell.Row: ScroColumn = ActiveCell.Column

SB1value = 5: RegFontHeight = 10.2

'The alignment of ScrollBar1 on to ScroColumn will commence
hereinafter.
ScrollBar1.Top = ActiveSheet.Cells(AcellRow, ScroColumn + 1).Top
ScrollBar1.Left = ActiveSheet.Cells(AcellRow, ScroColumn + 1).Left
ScrollBar1.Height = SB1value * RegFontHeight
ActiveCell.RowHeight = SB1value * RegFontHeight

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveCell = ActiveCell.Address

AcellRow = ActiveCell.Row: ScroColumn = ActiveCell.Column

SB1value = 5: RegFontHeight = 10.2

'The alignment of ScrollBar1 on to ScroColumn will commence
hereinafter.
ScrollBar1.Top = ActiveSheet.Cells(AcellRow, ScroColumn + 1).Top
ScrollBar1.Left = ActiveSheet.Cells(AcellRow, ScroColumn + 1).Left
ScrollBar1.Height = SB1value * RegFontHeight
ActiveCell.RowHeight = SB1value * RegFontHeight

End Sub

By virtue of the code above, the ActiveCell displays its own
A1-address ; clicking the Scrollbar would convert the address to R1C1
style.
The requirement is, Copy-and-Paste operations should be restored ;
Please enlighten.
Otherwise, how to circumvent the suspension of Copy-and-Paste ? What
is the viable and amicable alternative to achieve the same ?

Thank you for your comment. Regards.

 

[Patrick Molloy]

You are very unclear as what you are trying to achieve
here. If you only want the cell's address, then what is
the point of the scroll bar?

Now, if you just want to see the address there's a really
simple way to put this onto the status bar.
The following code is in the worksheets code page:

Private Sub Worksheet_SelectionChange(ByVal Target _
As Range)
Application.StatusBar = Target.Address & "/ R" & _
Target.Row & "C" & Target.Column
End Sub

Patrick Molloy
Microsoft Excel MVP