I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

Dim bEnable As Boolean
bEnable = False ' true to enable
ActiveSheet.OLEObjects("CommandButton1").Object.En abled = bEnable

Regards,
Peter T

"Dr. Schwartz" wrote in message
...
I have a command button that I want to enable and disable using code. I
have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith




"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson

ps. Maybe the worksheet is protected before your code hits the .locked line.

If that doesn't help, how is it failing?

Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...


Thanks.
Keith



"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Before we go any further, what are these shapes?

Personally, if they're textboxes from the control toolbox toolbar, I'd wouldn't
go through the shapes collection. I'd use .oleobjects.

Without know what those shapes are, I wouldn't venture a guess.

Keith Broaddrick wrote:

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Yes they are just text boxes and combo boxes. I'll try playing with them as
objects instead of shapes and see if that makes a difference.
I am not really sure how I first determined they were shapes anyway.

They were all added to the worksheet from the control toolbox toolbar.

Thanks.
Keith

"Dave Peterson" wrote:

Before we go any further, what are these shapes?

Personally, if they're textboxes from the control toolbox toolbar, I'd wouldn't
go through the shapes collection. I'd use .oleobjects.

Without know what those shapes are, I wouldn't venture a guess.

Keith Broaddrick wrote:

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

OK Dave.

I used your code from below:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj

It works great, sorry I didn't use it earlier. But if I replace
OLEObj.enabled = false with OLEObj.locked = true, it still doesn't lock them.

Again, thanks for the help.
Keith


"Keith Broaddrick" wrote:

Yes they are just text boxes and combo boxes. I'll try playing with them as
objects instead of shapes and see if that makes a difference.
I am not really sure how I first determined they were shapes anyway.

They were all added to the worksheet from the control toolbox toolbar.

Thanks.
Keith

"Dave Peterson" wrote:

Before we go any further, what are these shapes?

Personally, if they're textboxes from the control toolbox toolbar, I'd wouldn't
go through the shapes collection. I'd use .oleobjects.

Without know what those shapes are, I wouldn't venture a guess.

Keith Broaddrick wrote:

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Try:

OLEObj.Object.Locked = True



Keith Broaddrick wrote:

OK Dave.

I used your code from below:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj

It works great, sorry I didn't use it earlier. But if I replace
OLEObj.enabled = false with OLEObj.locked = true, it still doesn't lock them.

Again, thanks for the help.
Keith

"Keith Broaddrick" wrote:

Yes they are just text boxes and combo boxes. I'll try playing with them as
objects instead of shapes and see if that makes a difference.
I am not really sure how I first determined they were shapes anyway.

They were all added to the worksheet from the control toolbox toolbar.

Thanks.
Keith

"Dave Peterson" wrote:

Before we go any further, what are these shapes?

Personally, if they're textboxes from the control toolbox toolbar, I'd wouldn't
go through the shapes collection. I'd use .oleobjects.

Without know what those shapes are, I wouldn't venture a guess.

Keith Broaddrick wrote:

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Yep, that did it. I suppose I should try using my brain a little more.

Thanks Dave.


"Dave Peterson" wrote:

Try:

OLEObj.Object.Locked = True



Keith Broaddrick wrote:

OK Dave.

I used your code from below:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj

It works great, sorry I didn't use it earlier. But if I replace
OLEObj.enabled = false with OLEObj.locked = true, it still doesn't lock them.

Again, thanks for the help.
Keith

"Keith Broaddrick" wrote:

Yes they are just text boxes and combo boxes. I'll try playing with them as
objects instead of shapes and see if that makes a difference.
I am not really sure how I first determined they were shapes anyway.

They were all added to the worksheet from the control toolbox toolbar.

Thanks.
Keith

"Dave Peterson" wrote:

Before we go any further, what are these shapes?

Personally, if they're textboxes from the control toolbox toolbar, I'd wouldn't
go through the shapes collection. I'd use .oleobjects.

Without know what those shapes are, I wouldn't venture a guess.

Keith Broaddrick wrote:

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

For anyone else getting help from this thread...

Search for "using activex controls on sheets" in the visual basic editor
help and you'll find text that helps explain what went on here. Here's an
excerpt that is directly related to what Dave helped me with:

*Quote*
Control properties that are not shown as properties of the OLEObject object
can be set by returning the actual control object using the Object property.
The following example sets the caption for CommandButton1.

Worksheets(1).OLEObjects("CommandButton1").Object. Caption = "run me"
*End Quote*

-Keith


"Keith Broaddrick" wrote:

Yep, that did it. I suppose I should try using my brain a little more.

Thanks Dave.


"Dave Peterson" wrote:

Try:

OLEObj.Object.Locked = True



Keith Broaddrick wrote:

OK Dave.

I used your code from below:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj

It works great, sorry I didn't use it earlier. But if I replace
OLEObj.enabled = false with OLEObj.locked = true, it still doesn't lock them.

Again, thanks for the help.
Keith

"Keith Broaddrick" wrote:

Yes they are just text boxes and combo boxes. I'll try playing with them as
objects instead of shapes and see if that makes a difference.
I am not really sure how I first determined they were shapes anyway.

They were all added to the worksheet from the control toolbox toolbar.

Thanks.
Keith

"Dave Peterson" wrote:

Before we go any further, what are these shapes?

Personally, if they're textboxes from the control toolbox toolbar, I'd wouldn't
go through the shapes collection. I'd use .oleobjects.

Without know what those shapes are, I wouldn't venture a guess.

Keith Broaddrick wrote:

Dave, thanks for being so kind as to help a fledgling....

Here is what I finally tinkered around and got to work about 10 minutes
before your last post.

ActiveSheet.OLEObjects(shp.name).enabled = false

However, in my thinking, this should work also:

ActiveSheet.OLEObjects(shp.name).locked = true

but it doesn't. I am calling my 'LockObjects' function from a button's click
event, and both of these lines of code are in that function. After the
LockObjects function runs, I then protect the sheet. I was assuming I could
lock these objects (shapes) and then the protect function would keep users
from entering or editing text in the locked boxes. If I run the LockObjects
function without the .Enable = false, the protect the sheet, I can still
enter or edit text in the boxes, UNTIL I enter design mode, then I can't
select the box.

I am working with an Excel template, .xlt file if that makes any difference.
I just seem to be having a lot of trouble referring to controls and accessing
their properties...

Sorry to be such a newbie.
Keith

"Dave Peterson" wrote:

If you know the names of the textboxes (are they all textboxes????), then you
may be able to do something like this:

Dim iCtr as long
for ictr = 1 to 7
me.OLEObjects("textbox" & ictr).Object.Enabled = False
next ictr

This assumes that the code is in the worksheet module (the Me keyword refers to
the worksheet that owns the
code) and your textboxes are named textbox1, textbox2, ..., textbox7


Or just 7 different lines:

me.textbox1.enabled = false
me.textbox2.enabled = false
me.textbox3.enabled = false
....
me.textbox7.enabled = false

If you don't know how many textboxes there are--or they aren't named nicely:

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.enabled = false
End If
Next OLEObj


Keith Broaddrick wrote:

Thanks Dave.

Ok, I was able to use your first suggestion and it works. I can even just
use it like this:
TextBox7.enabled = False
and that works too.

Now I just have to figure out how to substitute the variable for each
textbox so I can apply this to all of the other textboxes and combo boxes on
the sheet, and not just "TextBox7".

I've tried these to no avail:
ActiveSheet.shp.Enabled = False
shp.enabled = False
ActiveSheet.Shapes(shp).enabled = False

I've tried setting the shp.name to a string variable and using this:
ActiveSheet.Shapes(strShpName).Enabled = False
strShpName.Enabled = False

I can't seem to get it right.

What's the proper syntax to get the variable 'shp' to react the same as
using the control's explicit name?

Also, I have noticed that this when I execute this line:
activesheet.textbox7.locked = true
without disabling it, I can still enter text in the box.

What I ultimately am trying to do is loop through all the editable controls
on the sheet and disable and lock them, but keep the text 'undimmed' so the
box looks normal, but can't receive the focus.

I can do this type of thing no problem in Access VBA, but I am finding the
Excel VBA is a whole 'nother animal...

Thanks.
Keith

"Dave Peterson" wrote:

Just use the control directly:

ActiveSheet.textbox7.Enabled = False

Or

ActiveSheet.OLEObjects("textbox7").Object.Enabled = False



Keith Broaddrick wrote:

I have a worksheet with some buttons and text boxes and combo boxes, all from
the control toolbox. I am looping through them and wanting to disable the
boxes and lock them.

If I look at the property sheet of the text box, I see it has an enabled
property, but if I try to set it to enabled = false with VBA code, I get the
error message that the object doesn't support the method.

I added a new textbox "TextBox7" just for testing purposes. It appears that
locking isn't working right either. Maybe it's something else I am doing...

'****StartCode:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Name = "TextBox7" Then
shp.Locked = True
ActiveSheet.Shapes("TextBox7").Enabled = False
End If
Next shp

Range("B6,B7,D6:D7,B10,B13,B14,B17,B18,B19,B21,B23 ,B25,B27,B29,B31,B33,B35,B37,B39,B41,B42,B43,B44,B 45,B47:B62").Select
Selection.Locked = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:=strPassword
'****EndCode

Please help?
Thanks.
Keith

"Dave Peterson" wrote:

Another one:

Worksheets("sheet1").CommandButton1.Enabled = False



Dr. Schwartz wrote:

I have a command button that I want to enable and disable using code. I have
tried this:

ActiveSheet.Shapes("PrintJour").Enabled = True

Without any luck. Can anyone guide me?

The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson