user-defined type not defined - Command Bar Control

mond007

New Member
Joined
Oct 9, 2008
Messages
34
Hi <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I am trying to capture with Worksheet Change a command bar action like paste but I am getting an error : <o:p></o:p>
<o:p> </o:p>
“User-Defined type not defined”<o:p></o:p>
<o:p> </o:p>
Do I need a library of sort in References ??<o:p></o:p>
<o:p> </o:p>
Code:
[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:p></o:p>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mond007

New Member
Joined
Oct 9, 2008
Messages
34
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 :

Code:
 Dim oControl As CommandBarControl

Thanks again Kuldip
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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 :

Code:
 Dim oControl As CommandBarControl

Thanks again Kuldip

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

mond007

New Member
Joined
Oct 9, 2008
Messages
34

ADVERTISEMENT

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Gary</st1:place></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-com:eek:ffice:eek:ffice" /><o:p></o:p>
Code:
[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:p> </o:p>
The following line is the culprit. <o:p></o:p>
<o:p> </o:p>
Code:
[/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:p> </o:p>
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:p></o:p>
<o:p> </o:p>
I am also aware that this Right Click control is at Excel Level and not worksheet/workbook lebel.<o:p></o:p>
<o:p> </o:p>
Could it be References or something.<o:p></o:p>
<o:p> </o:p>
Thanks in advance.<o:p></o:p>
<o:p> </o:p>
p.s. I am now aware I can reset the control by using.<o:p></o:p>
Application.CommandBars("Cell").Reset<o:p></o:p>
<o:p></o:p>
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 9, 2008
Messages
34

ADVERTISEMENT

I have cracked it this time. I think !!! I am testing now.
Code:
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
 

mond007

New Member
Joined
Oct 9, 2008
Messages
34
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

New Member
Joined
Oct 9, 2008
Messages
34
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-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Code:
[FONT=Arial]   Application.CommandBars("Cell").Reset<o:p></o:p>[/FONT]
[FONT=Arial]   Application.OnKey "^v"[/FONT]
<o:p></o:p>
<o:p>I do this in the following instances : </o:p>
<o:p>
Code:
[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:p>

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top