user-defined type not defined - Command Bar Control

[mond007]

Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am trying to capture with Worksheet Change a command bar action like paste but I am getting an error : <o></o>
<o> </o>
“User-Defined type not defined”<o></o>
<o> </o>
Do I need a library of sort in References ??<o></o>
<o> </o>

[FONT=Courier New]Public Sub Right_Click()<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Dim oControl As CommandBarControl<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]For Each oControl In CommandBars("Cell").Controls<o:p></o:p>[/FONT]
[FONT=Courier New]    Debug.Print oControl.Caption<o:p></o:p>[/FONT]
[FONT=Courier New]    If oControl.Caption = "&Paste" Then<o:p></o:p>[/FONT]
[FONT=Courier New]        oControl.OnAction = "MyPaste"<o:p></o:p>[/FONT]
[FONT=Courier New]    End If<o:p></o:p>[/FONT]
[FONT=Courier New]Next oControl<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]End Sub<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p></o:p>[/FONT]

Thanks in Advance Kuldip.<o></o>

 

[Andrew Poulsom]

That code works fine for me. What is checked under Tools|References? Any MISSING?

 

[mond007]

Sorry I meant to say that I am using version Excel 2003, in which it does nto work but I manmaged to crack it now. I used the following :

 Dim oControl As CommandBarControl

Thanks again Kuldip

 

[Andrew Poulsom]

Sorry I meant to say that I am using version Excel 2003, in which it does nto work but I manmaged to crack it now. I used the following :

 Dim oControl As CommandBarControl

Thanks again Kuldip

Isn't that what you had in the first place?

 

[mond007]

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Gary</st1lace></st1:City>, you are quite correct. I do not understand. I took the problem out of the BIG workbook and tried solving the puzzle in a mini test and it workedand compiled. But the same declaration line does not compile in the BIG worksheet.
I can not figure out why as there must be something I am missing.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

[COLOR=black][FONT=Trebuchet MS] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]Public Sub MyPaste()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]'Selection.PasteSpecial xlPasteValues<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]MsgBox "You can not 'Paste' onto this Worksheet", vbCritical<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]End Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]Public Sub Right_Click()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]Dim oControl As commandbarcontrol<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]    If ActiveSheet.Name = "Supplier Input Sheet" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]        'MsgBox "Supplier Input Sheet DISABLE From PASTE"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]        For Each oControl In CommandBars("Cell").Controls<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]        <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]            If oControl.Caption = "&Paste" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]                oControl.OnAction = "MyPaste"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]            End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]        Next oControl<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]    End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]                                                'Reset "Cell" popup menu<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]                                                'CommandBars("Cell").Reset<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS]End Sub<o:p></o:p>[/FONT][/COLOR]

<o> </o>
The following line is the culprit. <o></o>
<o> </o>

[/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS][COLOR=black][FONT=Trebuchet MS]Dim oControl As commandbarcontrol[/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Trebuchet MS][COLOR=black][FONT=Trebuchet MS][/FONT][/COLOR]

<o> </o>
When I take this code and put in into the mini test it works. BUT when placed in my main BIG worksheet with loads of formulas and code in it it fails to compile. <o></o>
<o> </o>
I am also aware that this Right Click control is at Excel Level and not worksheet/workbook lebel.<o></o>
<o> </o>
Could it be References or something.<o></o>
<o> </o>
Thanks in advance.<o></o>
<o> </o>
p.s. I am now aware I can reset the control by using.<o></o>
Application.CommandBars("Cell").Reset<o></o>
<o></o>

 

[Andrew Poulsom]

If you have Intellisense on, CommandBarControl should be in the list that pops up when you type Dim oControl As . Isn't it for you?

 

[mond007]

I have cracked it this time. I think !!! I am testing now.

Public Sub Right_Click()
'Dim oControl As commandbarcontrol
    If ActiveSheet.Name = "Supplier Input Sheet" Then
        'MsgBox "Supplier Input Sheet DISABLE From PASTE"
        For Each oControl In Application.CommandBars("Cell").Controls
            If oControl.Caption = "&Paste" Then
                oControl.OnAction = "MyPaste"
            End If
        Next oControl
    End If
End Sub

In despiration I got rid on the Dim altogether and put "application." and it all works.

By the way what is Intellisence ????????????

Thanks Kuldip

 

[Andrew Poulsom]

It's what you get when Auto List Members is checked under Tools|Options|Editor tab.

http://en.wikipedia.org/wiki/IntelliSense

 

[mond007]

Sorry I see what you mean, almost like predictive/suggestive next line. They always pop up when you typing in a line from scratch.

Well I got it working in the end, thanks for helping really appreciate this. Can't believe I spent a day on this but i suppose thats the way it goes sometimes.

Cheers ... Kuldip

 

[mond007]

Ok nothing is ever smooth,

I have noticed that when I disable either Control V and Right Cllick and then subseuently "Reset" the operations (for otherworkbooks/Apps) by using the following code, I loose Control Z functionality in this workbook.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

[FONT=Arial]   Application.CommandBars("Cell").Reset<o:p></o:p>[/FONT]
[FONT=Arial]   Application.OnKey "^v"[/FONT]

<o></o>
<o>I do this in the following instances : </o>
<o>

[FONT=Arial]<o:p> </o:p>[/FONT]
[FONT=Arial][FONT=Arial]Private Sub Workbook_BeforeClose(Cancel As Boolean)<o:p></o:p>[/FONT]
Private Sub Workbook_Open()<o:p></o:p>[/FONT]
[FONT=Arial]Private Sub Workbook_SheetActivate(ByVal Sh As Object)<o:p></o:p>[/FONT]
[FONT=Arial]Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)<o:p></o:p>[/FONT]
[FONT=Arial]Private Sub Workbook_WindowActivate(ByVal Wn As Window)<o:p></o:p>[/FONT]
[FONT=Arial]Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)<o:p></o:p>[/FONT]
[FONT=Arial]Private Sub Workbook_Activate()<o:p></o:p>[/FONT]
[FONT=Arial]Private Sub Workbook_Deactivate()<o:p></o:p>[/FONT]

</o>
Any Ideas why I loose Control Z functionality in this workbook.
(All Other workbooks are unafected !!! I suspect when it does the "Resets" it resets ControlZ too ???

Thanks Kuldip.