Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing selection and strange scrolling
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Losing selection and strange scrolling
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
losing text from text box when pasting selection to word | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel 2003 | Excel Discussion (Misc queries) | |||
Strange paste selection behavior | Excel Discussion (Misc queries) | |||
strange scrolling in Excel 2003 | Excel Discussion (Misc queries) | |||
Strange or error : For each cl in Selection 'cl as range | Excel Programming |