Losing selection and strange scrolling

[Ken McLennan]

G'day there One & All,

I've developed yet another problem that I not only can't seem to
resolve, but can't even find where to look. The Google's NewsGroups
Archive haven't revealed anything that has pointed me in a useful
direction. Here's what's happening:

My worksheet is intended as a replacement for an existing one.
Therefore, in the migration the users should just copy & paste their
existing data (simply a basic list) from the old into the new.

The first column of data could contain either a Section Heading
(String) or an employee number (Long), or a blank cell. The remaining
cells are irrelevent for my purposes.

My intent is that having copied their data, my users will
highlight a range from the top row of the work area (in reality, Row 6)
down to the bottom row of their data. This shouldn't be more than 100 -
120 rows and more than likely a lot less. This could include any number
of columns but, obviously, there has to be at least one. They then
invoke my macro:

Public Sub grpFmtSrt()
Dim rws As Range
' rws is set to first cell in every row of the selection.
' Even if the selection doesn't include Column 1
Set rws = Intersect(Sheets(1).Columns(1), Selection.EntireRow)
For Each rws In Selection
Call fmtRow(rws.Row, TypeName(Cells(rws.Row, 1).Value))
Next
End Sub

Public Sub fmtRow(tgtRow, dType)
' Assign row formatting according to 1st column entry
Select Case dType
Case Is = "String"
Cells(tgtRow, 1).Select
' 35 row format block doesn't seem to work unless the target
' area is selected first.
Selection.Resize(, 35).Select
Range("Data!txtCellFmtTmplt").Copy
With Selection.Columns(1)
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:
=xlNone, SkipBlanks:=False, Transpose:=False
.RowHeight = 22
End With
Case Is = "Long"
Cells(tgtRow, 1).Select
Selection.Resize(, 35).Select
Range("Data!numCellFmtTmplt").Copy
With Selection.Columns(1)
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:
=xlNone, SkipBlanks:=False, Transpose:=False
.RowHeight = 22
End With
' Treat blank rows and anything else as String entry
Case Else
Cells(tgtRow, 1).Select
Selection.Resize(, 35).Select
Range("Data!txtCellFmtTmplt").Copy
With Selection.Columns(1)
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:
=xlNone, SkipBlanks:=False, Transpose:=False
.RowHeight = 22
End With
End Select
End Sub

Depending on the content of the first cell in the row, this should
copy a format template for text or number onto the first 35 cells of
each selected row starting at column 1. This seems to work fine.
Except...

Having invoked my macro to test it, I can't paste any cells. I can
highlight a cell (or range), and get the marching ants when I cut or
copy, but as soon as I select a target cell, the original range is
deselected. Data remains, I just lose the selection.

Also, if I press the Down cursor key the active cell keeps going
down until it hits the row that seems to be one below the bottom of the
range where I just tested the above code. It then goes one column to the
right, and back to Row 6 before commencing downwards again.

Trying my macro again on a new & different set of rows (I deleted
the reformatted rows before trying anything else) just moves the bottom
most cell to whichever row is one below where my selection was this
time. My formatting template only involves borders - an outline for each
cell, background colour & font size/colour. All cells in the templates
are unlocked so there are no locked cells being carried across, and the
cells I'm checking aren't locked either. Nor can I think of any format
that would cause this problem.

Removing protection fixes the scrolling problem, but not the copy
& cut selection disappearing trick.

I can't find anything like it described anywhere.

Does anyone have the foggiest idea of what is going on?

I'm well & truly stumped!!

See ya,
Thanks in advance,
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there People,

Further to this strange confusion, I've discovered that when I
have protection on, and check the cell formatting, the Alignment tab of
the Format Cells dialogue shows a greyed out checkmark for the Merge
Cells Text Control. I've no idea whether that's significant or not, but
it's the only difference I detect between these and cells from sheets
that don't display this problem.

Still working on it,
Ken McLennan
Qld, Australia