Paste not available

J

June Macleod

I have a menu driven excel worksheet. All the tabs are hidden and the user
selects which sheet they wish to view from the menu.

I have to be able to let the users copy data from one sheet and Paste
Transpose it to another. I have added the Paste Transpose menu option to
the shortcut menu available with the right mouse click. It all works fine
when the Tabs are visible but when the user selects a sheet through the menu
the Paste option becomes greyed out.

The following code is how I select the sheet from the Menu Option
Dim shtname$
If CommandBars.ActionControl.Tag > "" Then
shtname = CommandBars.ActionControl.Tag
ElseIf CommandBars.ActionControl.Caption > "" Then
shtname = CommandBars.ActionControl.Caption
Else
shtname = ""
End If

Sheets(shtname).Select

If anyone could tell me how I can make it possible for the paste option
still to be available when I select a new sheet I would be very grateful.

Many Thanks

Neme
 
D

Dave Peterson

The bad news is that lots of macro code destroy the clipboard. And if you're
going to run a macro that destroys the clipboard, then I don't think you can do
anything to change the way excel behaves.

One way to work around it is to have your macro ask for the range to copy.
 
Top