Keeping previous cell value if copied. 1

[Petemush]

Hi,

Got a right old problem here and have been racking my brain but cannot come up with an answer, or at least an answer I can code!

I currently have some code which moves a button about on a spreadsheet to be in line with whatever row is selected. See here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range(&quot;A&quot; & Target.Row).Value <> &quot;&quot; ]Then
With ActiveSheet
.CommandButton1.Top = ActiveCell.Top + (((ActiveCell.Height) / 2) - (CommandButton1.Height / 2))
End With
End If
End Sub

It works great, the problem is, if I select a cell and copy it, as soon as I move to the cell I want to copy it to, an unitentional consequence of the code above causes the previous cell to become not available for copying, might clear the clipboard, not sure.

So I can't find a method to reference the previously selected cell(can't get Application.PreviousSelections to work) and I'm pretty sure there's not a method to check if the clipboard has just had something pasted to it.

I was going to get the last cell's value, check it against the clipboard and if they were the same, after the code had completed I was going to put the data back into the clipboard but like I said, can't get Previous Cell value.

Also, I'm not sure if this will work since I think Ctrl-C and paste from menu relys on the cell still having the moving box(very technical I know) around it.

Any ideas?

Cheers,

Pete

 

[Petemush]

That's an accidental square bracket next to the first then statement by the way!

 

[SkipVought]

Hi,

Set up a reference sheet that will hold PrevAddr and ThisAddr

Code this in a MODULE

[PrevAddr] = [ThisAddr]
[ThisAddr] = ActiveCell.Address(External:= True)

In the selection change event, call this procedure.

Then you can use the PrevAddr

Range([PrevAddr])

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Petemush]

Hi Skip,

Thanks for your reply. The logic makes sense but I can't get it to work! I've added this to a module:

Sub hopeandpray()
[PrevAddr] = [ThisAddr]
[ThisAddr] = ActiveCell.Address(External:=True)
End Sub

And my selection change procedure now looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call hopeandpray
If Range(&quot;A&quot; & Target.Row).Value <> &quot;&quot; Then
With ActiveSheet
.CommandButton1.Top = ActiveCell.Top + (((ActiveCell.Height) / 2) - (CommandButton1.Height / 2))
End With
End If
On Error GoTo noSelections
Range([PrevAddr]).Copy
noSelections:
End Sub

I just did the copy method here as a test rather than write my final code. Unfortunately when I test it, when the hopeandpray procedure is called I get an &quot;Object Required&quot; error occur on the first line of hopeandpray() if I swap the two lines around I get the same error on the first line again.

Am I just being dumb?

Cheers,

Pete

 

[TopJack]

Try declaring your &quot;hopeandpray&quot; variables as public before your routine in the module........

Public PrevAddr, ThisAddr

Sub hopeandpray()
***************
End Sub

This makes the variables available to other routines now. All should work well. I foresee a problem though. The first time the routine runs it will not know what &quot;ThisAddr&quot; is. You need to 'prime' the variable beforehand - maybe when you open the workbook it notes the current selection.

Hope this helps.

 

[SkipVought]

PrevAddr and ThisAddr are RANGE NAMES. On a blank sheet (and then hide it), name each of 2 cells as such.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Petemush]

Ahhh, I forgot you could name cells in excel(used vba more than excel recently!) All compiles fine but I don't get anything when I use Range([PrevAddr]).

Looking at the sheet on which [PrevAddr] is on, the previous address is being recorded exactly as desired:

[Issues Log.xls]Issues Log'!$B$295

Is what appears in the PrevAddr cell.

In my code I try to make a comparison of the noted cell by using

If Range([PrevAddr]) = foo

and have also tried

If Range([PrevAddr]).Value = foo

but neither of these comparisons work. Have tried assigning both Range([PrevAddr]) and Range([PrevAddr]).Value to a variable to check that it's working properly but it's not, since both variables always remain empty.

Any idea why?

Cheers for all your help so far!

Pete

 

[TopJack]

Sorry Skip I missed the vital &quot;set up a reference sheet&quot; - I really must control my speed reading.

Would it not be better to hold the PrevAddr and ThisAddr in memory rather than use a hidden sheet ? If you drop all your '[' brackets it works great. Just a thought.

 

[TopJack]

Sorry it's me again ...... As above make your variables public - your getting no values because you have lost reference to them.

 

[SkipVought]

Regarding values in memory, would work as long as the variable is persistent.

Regarding Range([PrevAddr])

What are you wanting to do with Range([PrevAddr])?

If Range([PrevAddr]).Value = foo

what is the value of foo and what is the value of Range([PrevAddr])???

For instance if the cell referenced by [PrevAddr] has a value of 55, then

MsgBox Range([PrevAddr]).Value

would display 55.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com