Predict number of Rows/Cols of text 1

[Sylv4n]

Ok here is my problem, I am unable to use the Excel track changes in my application as it restricts other Excel functionality. so I have written my own VBA code to handle this, it works simply by using:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

so that when the user selects a cell or range it stores all of the data in that range in an array

and in

Private Sub Worksheet_Change(ByVal Target As Range)

it goes through the array and finds the old values and marks it as being changed with a old and new value.


Now the above works fine when the user selects all the cells that they want to change, if however they only click one cell and paste then it wont know about all the other cells (because they are not in the temporary array because they didn’t select them)

My initial idea for this involves looking at the clipboard (which I can do) and predicting how many rows/cols there are in that text when the user selects a cell and store the relevnt number of extra rows & cols. but I don’t know how to turn the text in the clipboard:

(Note: the col headdings dont line up in text - but I copied this off and excel spreadsheet and used code to pull the details back and copied and pasted the result)

Col 1 Col 2 Col 3
Row 1 1 10 20
Row 2 2 11 21
Row 3 3 12 22
Row 4 4 13 23
Row 5 5 14 24

into 6 rows (5 + the col heading) and 4 cols (3 + row heading) so that when the user selects just one cell the code will store 6 rows down and 4 cols across in the array.


Any ideas on how to do this or an alternative solution?

can post code if necessary or clarify the problem =)

Many thanks
Sylvan

 

[SkipVought]

Please seach this page on Process TGML and learn to use them when posting code and spreadsheet copy 'n' paste examples.

What is a valid "prediction?"

What if the user makes ONLY one cell copy ranges?

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[xlbo]

You need to pick up the dimensions of the selection in the selection change event and apply them in the selection change event

numRows = selection.rows.count
numCols = selection.columns.count


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[EBGreen]

I think what the OP wants to do is to handle this scenario:

[ol][li]User copies a selection that is say 4x4 from some completely unrelated source.[/li]
[li]User then clicks one cell in the OP's worksheet.[/li]
[li]User hit Ctrl-v and pastes over a 4x4 section of the OP's worksheet.[/li]
[li]OP wants to capture the data in the 4x4 section that is being pasted over before the paste happens.[/li][/ol]

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[xlbo]

If the data is coming from anothert excel spreadsheet, then as I said, you ca pick up the dimensions from the selection change event

They then need to get applied if a change event happens directly after a selection change event

If the data is coming from a different app then you're going to struggle

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Sylv4n]

okay sorry for the late reply,
Skip - for one cell ranges there is no problem becouse when they selct a cell I store the old value, so when its changed I can report what it was and what it is changed to. I have got that working

xlbo - I am using the selection change event to store the values in these cells into a temporary array, so that when they change I can remember the old values, and yes these will be comming from another spreadsheet

EBGreen - you hit the nail on the head, the only thing I would add to that is after they click a cell, I can find out what that cell's value is and if they select a 4x4 grid I can remember all the values, so if they do select a 4x4 grid before they paste it all works well, the problem is if they click 1 cell and the clipboard has a 4x4 grid then I dont know how many cells I need to store in the array.

 

[xlbo]

My point was that if data is coming from excel, you can use the selection.rows.count etc to pre define what the paste area will be. I know you are using it to store the prior text but you can also use it to store the dimensions of the previously selcted area

If data is being copied from outside of excel, you're pretty much stuffed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Sylv4n]

I have worked up a solution that works

it basically searches the clipboard text for 'vbLf' and counts how many it can find (the number of rows) then it counts how many 'vbTab' it can find before the first 'vbLf' (the number of cols)

and Skip I even used the code tags =)

Public Function GetOffClipboard() As Variant
    Dim MyDataObj As New DataObject
    Dim clipboardText As Variant
    Dim x As Integer
    Dim lineCount As Integer
    Dim colCount As Integer
    
    MyDataObj.GetFromClipboard

    clipboardText = MyDataObj.GetText()
    
    [COLOR=green]'get the number of Lines[/color]
    lineCount = 0
    x = 1
    While InStr(x, clipboardText, vbLf) <> 0 [COLOR=green]'WHILE there is another line[/color]
        x = InStr(x, clipboardText, vbLf) + 1
        lineCount = lineCount + 1
    Wend
    Debug.Print "Number of lines: " & lineCount

    [COLOR=green]'get the number of Cols[/color]
    x = 1
    colCount = 0
    While x < InStr(1, clipboardText, vbLf)[COLOR=green]'WHILE x < the first vbLF[/color]
        x = InStr(x, clipboardText, vbTab) + 1
        colCount = colCount + 1
    Wend
    Debug.Print "Number of tabs: "; colCount
    
    [COLOR=green]'GetOffClipboard = MyDataObj.GetText() 'Return the clipboard [/color]
    
End Function

So now I can when the user clicks a cell I can make a prediction on what cells they will be changed if they click paste.
however the only major limitation that I can see is if they select a cell (nothing in the clipboard so it only stores that one value in the array) they then go to another spreadsheet and copy, when they paste I wont have the old values in the array and it will be too late (becouse they have already pasted when the code is run) to get the values pre-paste.


if anyone has any suggestions/improvements please let me know

Many thanks
Sylvan

 

[xlbo]

Nice - love it when I'm proved wrong - especially when I learn something new!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question