CommandBar Popup not displayed correctly

[rfskinner]

Dear Mr Excel,

I have registered today and this is my first post so please forgive any mistakes in how I present my question.

After searching a range of forums already I can't find a reference to this particular problem anywhere.

My issue is with a CommandBar Popup which appears when you right-click in a textbox on my userform giving two command buttons: copy and paste.

When the user right-clicks the textbox, the popup is not appearing correctly as shown in the image at the link below.


http://postimage.org/image/2ewlgahqc/


If the user notices the faint outline of the popup and hovers over the buttons then they appear as shown in the second image/link.


http://postimage.org/image/2eynvh3yc/

Once the user has clicked one of the buttons then any further right-clicks display the popup correctly.

The only property which seems likely to have any impact on the visibility of the popup is the .visible but even with this set to True the popup misbehaves first time.

I have altered my code a number of times and although the function of the popup is the same, every alteration results in the invisible popup.


Public Sub TBEnqdetails_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = vbKeyRButton Then
Set ctl = UserForm2.Controls("TBEnqDetails")
CreateCmdBar
CommandBars("tbCmdBar").ShowPopup
End If
End Sub


Public Sub CreateCmdBar()
CmdBarDel ' to prevent duplications
With CommandBars.Add("tbCmdBar", Position:=msoBarPopup)
.Controls.Add(Type:=msoControlButton, ID:=19).OnAction = "CopySub"
.Controls.Add(Type:=msoControlButton, ID:=22).OnAction = "PasteSub"
End With
End Sub


Public Sub CmdBarDel()
On Error Resume Next
CommandBars("tbCmdBar").Delete
End Sub


Public Sub copysub()
Set MyData = New DataObject
If ctl.SelText <> "" Then
MyData.SetText ctl.SelText
Else
MyData.SetText ctl.Text
End If
MyData.PutInClipboard
ctl.Text = ctl.Text
CmdBarDel
End Sub


Public Sub pastesub()
Set MyData = New DataObject
MyData.GetFromClipboard
If ctl.SelText = "" Then
ctl.Text = ctl.Text & MyData.GetText
Else
ctl.SelText = MyData.GetText
End If
CmdBarDel
End Sub


Thanks in advance for any help.

 

[Andrew Poulsom]

The popup appears correctly for me in Excel 2003. What version of Excel are you using?

Here's an alternative way of doing what you want:

http://www.dicks-blog.com/archives/2004/12/07/using-copypaste-in-userforms/

 

[rfskinner]

Thanks Andrew,

Apologies for the slow reply. I have tried the alternative code that you suggested in your link and I still had the issue with the popup starting out invisible.

Whilst the use of a class module (my first attempt at a Calss Module) meant that I do not need to add code to each text box, it added an additional problem in that not only did the popup not appear correctly, but also that once I had clicked one of the buttons, the other button did not appear at all. a bit of an issue if you click copy and then need to click paste.

I am using excel 2007 but the file is currently saved as excel 97-2003 due to one of the users not having 2007. I am on a Windows 7 PC but users are on XP, Vista and 7.

I have reverted to my original code pending any further thoughts but I will definatley be looking more into class modules now that I have had a brief glimpse of their potential.

Thanks again for your suggestions.

Regards,



Russ

 

[rfskinner]

Solved it....

As the popup was showing correctly after one of the buttons had been clicked I included an execute command for one of the command bar buttons in the form initialization which worked. The popup appeared correctly when the form had opened but another problem arose.

I had set application.screenupdating to false but as the form was loading, the worksheet from which the information is taken was flickering away as though screenupdating was set to True.

adding screenupdating = true before and = false after the showpopup has ensured that the popup appears correctly each time.

Application.ScreenUpdating = True
CommandBars("tbCmdBar").ShowPopup
Application.ScreenUpdating = False

Is there a way to mark this thread as solved?

 

[Andrew Poulsom]

Is there a way to mark this thread as solved?

No, but you have said it in your reply.