| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enabled property failing when code tries to set it
Hi all,
I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
A shape doesn't have an "enabled" property.
What effect would "disabling" the shape have ? Tim "robs3131" wrote in message ... Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
Shapes don't have a .enabled property.
Some shapes can be enabled/disabled this way: shap.OLEFormat.Object.Enabled = False Some shapes can't be enabled or disabled. You may want to be more specific in your code and your question about the shapes that you're using. robs3131 wrote: Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
Thanks for your input. The shapes are command buttons. What I find strange
is that the following code does work. Any idea on why this code works and my initial code does not? Dim sh As Worksheet 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate Sheets("Main Menu").linksalesin.Enabled = False Sheets("Main Menu").linksaleshis.Enabled = False Sheets("Main Menu").cjsalesin.Enabled = False Sheets("Main Menu").cjsaleshis.Enabled = False Sheets("Main Menu").perfsalesin.Enabled = False Sheets("Main Menu").perfsaleshis.Enabled = False Sheets("Main Menu").linkpayin.Enabled = False Sheets("Main Menu").linkpayhis.Enabled = False Sheets("Main Menu").cjpayin.Enabled = False Sheets("Main Menu").cjpayhis.Enabled = False Sheets("Main Menu").perfpayin.Enabled = False Sheets("Main Menu").perfpayhis.Enabled = False Sheets("Main Menu").intierdata.Enabled = False Sheets("Main Menu").commdetrpt.Enabled = False Sheets("Main Menu").transclearbyaff.Enabled = False Sheets("Main Menu").viewtransclearbyaff.Enabled = False Sheets("Main Menu").cmdarchive.Enabled = False Sheets("Main Menu").cmdclearall.Enabled = False Sheets("Main Menu").cmdlinktiemerch.Enabled = False Sheets("Main Menu").perfopeninvoices.Enabled = False -- Robert "Dave Peterson" wrote: Shapes don't have a .enabled property. Some shapes can be enabled/disabled this way: shap.OLEFormat.Object.Enabled = False Some shapes can't be enabled or disabled. You may want to be more specific in your code and your question about the shapes that you're using. robs3131 wrote: Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
Is there a shape on the sheet which isn't a command button?
Tim "robs3131" wrote in message ... Thanks for your input. The shapes are command buttons. What I find strange is that the following code does work. Any idea on why this code works and my initial code does not? Dim sh As Worksheet 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate Sheets("Main Menu").linksalesin.Enabled = False Sheets("Main Menu").linksaleshis.Enabled = False Sheets("Main Menu").cjsalesin.Enabled = False Sheets("Main Menu").cjsaleshis.Enabled = False Sheets("Main Menu").perfsalesin.Enabled = False Sheets("Main Menu").perfsaleshis.Enabled = False Sheets("Main Menu").linkpayin.Enabled = False Sheets("Main Menu").linkpayhis.Enabled = False Sheets("Main Menu").cjpayin.Enabled = False Sheets("Main Menu").cjpayhis.Enabled = False Sheets("Main Menu").perfpayin.Enabled = False Sheets("Main Menu").perfpayhis.Enabled = False Sheets("Main Menu").intierdata.Enabled = False Sheets("Main Menu").commdetrpt.Enabled = False Sheets("Main Menu").transclearbyaff.Enabled = False Sheets("Main Menu").viewtransclearbyaff.Enabled = False Sheets("Main Menu").cmdarchive.Enabled = False Sheets("Main Menu").cmdclearall.Enabled = False Sheets("Main Menu").cmdlinktiemerch.Enabled = False Sheets("Main Menu").perfopeninvoices.Enabled = False -- Robert "Dave Peterson" wrote: Shapes don't have a .enabled property. Some shapes can be enabled/disabled this way: shap.OLEFormat.Object.Enabled = False Some shapes can't be enabled or disabled. You may want to be more specific in your code and your question about the shapes that you're using. robs3131 wrote: Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
No, they're all command buttons -- I tested this by putting in "?shap.name"
in the Immediate Window when the error comes up in the code execution -- the name of the shape is indeed one of the command buttons. And when I delete the command button that the code failed on, the next command button fails also. The sheet is not protected so that is not the issue...I also looked at how the properties were set for the command buttons and didn't see anything alarming -- I listed them below in case anyone can see something which would be an issue: Accelerator: (blank) AutoLoad: False AutoSize: False Enabled: True Font: Arial Locked: True **Failes with False also MouseIcon: (None) MousePointer: 0-fmMousePointerDefault Pictu (None) PicturePosition: 7 - fmPicturePositionAboveCenter Placement: 3 **Fails with 2 also PrintObject: True Shadow: True TakeFocusOnClick: True Visible: True WordWrap: False -- Robert "Tim Williams" wrote: Is there a shape on the sheet which isn't a command button? Tim "robs3131" wrote in message ... Thanks for your input. The shapes are command buttons. What I find strange is that the following code does work. Any idea on why this code works and my initial code does not? Dim sh As Worksheet 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate Sheets("Main Menu").linksalesin.Enabled = False Sheets("Main Menu").linksaleshis.Enabled = False Sheets("Main Menu").cjsalesin.Enabled = False Sheets("Main Menu").cjsaleshis.Enabled = False Sheets("Main Menu").perfsalesin.Enabled = False Sheets("Main Menu").perfsaleshis.Enabled = False Sheets("Main Menu").linkpayin.Enabled = False Sheets("Main Menu").linkpayhis.Enabled = False Sheets("Main Menu").cjpayin.Enabled = False Sheets("Main Menu").cjpayhis.Enabled = False Sheets("Main Menu").perfpayin.Enabled = False Sheets("Main Menu").perfpayhis.Enabled = False Sheets("Main Menu").intierdata.Enabled = False Sheets("Main Menu").commdetrpt.Enabled = False Sheets("Main Menu").transclearbyaff.Enabled = False Sheets("Main Menu").viewtransclearbyaff.Enabled = False Sheets("Main Menu").cmdarchive.Enabled = False Sheets("Main Menu").cmdclearall.Enabled = False Sheets("Main Menu").cmdlinktiemerch.Enabled = False Sheets("Main Menu").perfopeninvoices.Enabled = False -- Robert "Dave Peterson" wrote: Shapes don't have a .enabled property. Some shapes can be enabled/disabled this way: shap.OLEFormat.Object.Enabled = False Some shapes can't be enabled or disabled. You may want to be more specific in your code and your question about the shapes that you're using. robs3131 wrote: Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
You're not going through the shapes collection to get to the commandbuttons in
this code. Shapes don't have an .enabled property. You could use this if you went through the shapes collection, but it's still dangerous: shap.OLEFormat.Object.Enabled = False If I wanted to disable all the commandbuttons on a worksheet, I'd use: Dim OLEObj as oleobject for each oleobj in worksheets("Main Menu").oleobjects If TypeOf OLEObj.Object Is MSForms.commandbutton Then OLEObj.Object.enabled = false End If next oleobj Ron de Bruin has some very nice notes about shapes he http://www.rondebruin.nl/controlsobjectsworksheet.htm robs3131 wrote: Thanks for your input. The shapes are command buttons. What I find strange is that the following code does work. Any idea on why this code works and my initial code does not? Dim sh As Worksheet 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate Sheets("Main Menu").linksalesin.Enabled = False Sheets("Main Menu").linksaleshis.Enabled = False Sheets("Main Menu").cjsalesin.Enabled = False Sheets("Main Menu").cjsaleshis.Enabled = False Sheets("Main Menu").perfsalesin.Enabled = False Sheets("Main Menu").perfsaleshis.Enabled = False Sheets("Main Menu").linkpayin.Enabled = False Sheets("Main Menu").linkpayhis.Enabled = False Sheets("Main Menu").cjpayin.Enabled = False Sheets("Main Menu").cjpayhis.Enabled = False Sheets("Main Menu").perfpayin.Enabled = False Sheets("Main Menu").perfpayhis.Enabled = False Sheets("Main Menu").intierdata.Enabled = False Sheets("Main Menu").commdetrpt.Enabled = False Sheets("Main Menu").transclearbyaff.Enabled = False Sheets("Main Menu").viewtransclearbyaff.Enabled = False Sheets("Main Menu").cmdarchive.Enabled = False Sheets("Main Menu").cmdclearall.Enabled = False Sheets("Main Menu").cmdlinktiemerch.Enabled = False Sheets("Main Menu").perfopeninvoices.Enabled = False -- Robert "Dave Peterson" wrote: Shapes don't have a .enabled property. Some shapes can be enabled/disabled this way: shap.OLEFormat.Object.Enabled = False Some shapes can't be enabled or disabled. You may want to be more specific in your code and your question about the shapes that you're using. robs3131 wrote: Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enabled property failing when code tries to set it
Thanks Dave! This worked perfrectly. Sorry for the late update.
-- Robert "Dave Peterson" wrote: You're not going through the shapes collection to get to the commandbuttons in this code. Shapes don't have an .enabled property. You could use this if you went through the shapes collection, but it's still dangerous: shap.OLEFormat.Object.Enabled = False If I wanted to disable all the commandbuttons on a worksheet, I'd use: Dim OLEObj as oleobject for each oleobj in worksheets("Main Menu").oleobjects If TypeOf OLEObj.Object Is MSForms.commandbutton Then OLEObj.Object.enabled = false End If next oleobj Ron de Bruin has some very nice notes about shapes he http://www.rondebruin.nl/controlsobjectsworksheet.htm robs3131 wrote: Thanks for your input. The shapes are command buttons. What I find strange is that the following code does work. Any idea on why this code works and my initial code does not? Dim sh As Worksheet 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate Sheets("Main Menu").linksalesin.Enabled = False Sheets("Main Menu").linksaleshis.Enabled = False Sheets("Main Menu").cjsalesin.Enabled = False Sheets("Main Menu").cjsaleshis.Enabled = False Sheets("Main Menu").perfsalesin.Enabled = False Sheets("Main Menu").perfsaleshis.Enabled = False Sheets("Main Menu").linkpayin.Enabled = False Sheets("Main Menu").linkpayhis.Enabled = False Sheets("Main Menu").cjpayin.Enabled = False Sheets("Main Menu").cjpayhis.Enabled = False Sheets("Main Menu").perfpayin.Enabled = False Sheets("Main Menu").perfpayhis.Enabled = False Sheets("Main Menu").intierdata.Enabled = False Sheets("Main Menu").commdetrpt.Enabled = False Sheets("Main Menu").transclearbyaff.Enabled = False Sheets("Main Menu").viewtransclearbyaff.Enabled = False Sheets("Main Menu").cmdarchive.Enabled = False Sheets("Main Menu").cmdclearall.Enabled = False Sheets("Main Menu").cmdlinktiemerch.Enabled = False Sheets("Main Menu").perfopeninvoices.Enabled = False -- Robert "Dave Peterson" wrote: Shapes don't have a .enabled property. Some shapes can be enabled/disabled this way: shap.OLEFormat.Object.Enabled = False Some shapes can't be enabled or disabled. You may want to be more specific in your code and your question about the shapes that you're using. robs3131 wrote: Hi all, I'm getting the error below for the code below for the line that is preceded by '***' - I don't understand why this property cannot be set for this variable. Any input is greatly appreciated! Error: Run-time error '438: Object doesn't support this property or method Code: Dim sh As Worksheet Dim shap 'Select Main Menu, hide all other sheets, and protect Main Menu Sheets("Main Menu").Visible = xlSheetVisible Sheets("Main Menu").Select For Each sh In ActiveWorkbook.Sheets If sh.Name < "Main Menu" Then sh.Visible = xlVeryHidden Else End If Next Sheets("Main Menu").Activate For Each shap In ActiveSheet.Shapes *** shap.Enabled = False Next shap -- Robert -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| code failing in hidden rows | Excel Discussion (Misc queries) | |||
| userform Enabled property behavior changes between excel 97 and 2000/3 | Excel Programming | |||
| VBA - Code failing in Excel 97 | Excel Programming | |||
| Enabled Property of Shapes? | Excel Programming | |||
| BackColor property code | Excel Programming | |||
Hybrid Mode
