Paste not available

[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

 

[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.

 

[June Macleod]

Thanks for that. I took your advice and used the RanEdit control which all
seems to work fine.

Much appreciated.

JM

 

[Dave Peterson]

application.inputbox(type:=8)

could do the same kind of thing without the need for a userform.

 

[June Macleod]

application.inputbox(type:=8)

Thanks, I didn't realise you could use this method.

June

 

[Dave Peterson]

Something like:

dim rng as range
on error resume next
set rng = application.inputbox(prompt:="hi there",type:=8)
on error goto 0

if rng is nothing then
'user hit cancel
else
'you have a range
end if

application.inputbox(type:=8)

Thanks, I didn't realise you could use this method.

June