Excel: Worksheet_SelectionChange code clears clipboard?

[VBAjedi]

I have a piece of code in a Worksheet_SelectionChange event that tests Target to see if it is in MyRange, then:

Initiates a custom data validation routine if so
-OR-
Sets ComboBox1.Visible = False if not

The problem is that the if the ComboBox1.Visible = False code is triggered it seems to clear the clipboard or cancel Copy.

For example, if I select a cell outside MyRange, and choose Copy (so far so good), then click on a different cell outside MyRange - ZINGO! Nothing to paste!

If I comment out ComboBox1.Visible = False it fixes it. It's a complicated worksheet, so it might be caused by something else, but it sure looks like this code is the culprit. What the heck?
VBAjedi

 

[RobBroekhuis]

Welcome to the wacky world of Excel's use of the clipboard! I've actually not encountered your particular problem before (I don't often work with ActiveX controls on my worksheets), and must admit I'm surprised that that code would clear cutcopymode. You could test by msgbox-ing the value of cutcopymode right before and right after the .visible assignment. Mike helped me a while ago with some code to preserve the clipboard when doing a .copy in VBA. I never got it to work perfectly, but it's worth a look (is the search capability here back up and working?)
Rob

 

[VBAjedi]

Rob,

On my way out the door - I'll research that on Monday. Thanks for the suggestion.

The combobox is actually part of an "advanced data validation" scheme that I devised. It appears over any cell you click on in MyRange, and is populated "on the fly" with a list of valid values for that location within MyRange(with values that have already been placed in MyRange being filtered out). It's a pretty trick little function.
VBAjedi

 

[VBAjedi]

Rob,

My clipboard status msgbox test confirmed that this line clears the clipboard. So does modifying other properties of that textbox.

I tried to implement the workaround you described in your other post. I used this code:

Dim ClipSave As DataObject

Set ClipSave = New DataObject
If Application.CutCopyMode > 0 Then
ClipSave.GetFromClipboard
End If
ComboBox1.Visible = False
ClipSave.PutInClipboard
ClipSave.Clear

However, the code hangs at "ClipSave.PutInClipboard", telling me "DataObject.PutInClipboard not implemented".

Is .PutInClipboard not available in Excel 97?

Thanks!
VBAjedi

 

[RobBroekhuis]

Hmmm, I don't know. I use XL2000. Since the dim statement works, you obviously have the correct reference set. I think Mike also put up some API functions that might be helpful - did you find that thread?
Rob

 

[VBAjedi]

No, I didn't see that thread. I'll look, but I like the simplicity of your solution if I can get it to go.

I determined that .PutInClipboard is available in 97, so I'm wondering if my DataObject (ClipSave) was not set correctly or was cleared somehow. The .PutInClipboard statement does not work even if I comment out my "ComboBox1.Visible = False" code. How can I test ClipSave's contents?

Thanks, Rob!
VBAjedi

 

[RobBroekhuis]

Try and see if Clipsave.Gettext gives the expected result. Of course the putinclipboard method won't work if the getfromclipboard method didn't run - are you sure it did (i.e., that cutcopymode <> 0?)
Rob

 

[VBAjedi]

Rob,

Yes, the GetFromClipboard mode seems to have worked. CutCopyMode was greater than 0, and Clipsave.gettext returned the correct value (plus two non-printing characters) before AND after the &quot;ComboBox1.Visible = false&quot; statement. But the Clipsave.PutInClipboard code still errs out. (Naturally, Microsoft Support has no information on this error number!).

I came up with a klunky workaround by using a msgbox to warn users if there is data on the clipboard that will be lost (giving them the option to cancel and paste it where they will). I'd still rather get this to work though.

Thanks!
VBAjedi