Enabled property failing when code tries to set it

R

robs3131

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
 
T

Tim Williams

A shape doesn't have an "enabled" property.
What effect would "disabling" the shape have ?

Tim
 
D

Dave Peterson

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

robs3131

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
 
T

Tim Williams

Is there a shape on the sheet which isn't a command button?

Tim

robs3131 said:
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
Click to expand...
 
R

robs3131

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
Picture: (None)
PicturePosition: 7 - fmPicturePositionAboveCenter
Placement: 3 **Fails with 2 also
PrintObject: True
Shadow: True
TakeFocusOnClick: True
Visible: True
WordWrap: False
 
D

Dave Peterson

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 here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm


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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

delete modules 1
change code name 1
Code modification please 3
VBA set thick borders around non empty cells in range 0
Set Active Cell to A1 on Open Q 3
Paste to array of sheets 2
Protection Properties on WorkSheets 1
Select specific worksheets & copy - code problem 2

Top