Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio buttons and
check boxes. After some data is entered and the buttons
and check boxes are set, I'd like to disable them so thay
cannot be inadvertantly changed. I thought the lock and
protect features would address this, but it seems like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets the
check box to lock (box checked), and calls the macro (by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check box to
unlock (box uncheched), and calls the macro to enable the
particular controls, allowing their values to be reset.

Thanks so much for your time!
William DeLeo

Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo" wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio buttons and
check boxes. After some data is entered and the buttons
and check boxes are set, I'd like to disable them so thay
cannot be inadvertantly changed. I thought the lock and
protect features would address this, but it seems like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets the
check box to lock (box checked), and calls the macro (by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check box to
unlock (box uncheched), and calls the macro to enable the
particular controls, allowing their values to be reset.

Thanks so much for your time!
William DeLeo

Thanks so much!!! I can certainly work with that.


-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"
wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio buttons
and
check boxes. After some data is entered and the buttons
and check boxes are set, I'd like to disable them so
thay
cannot be inadvertantly changed. I thought the lock and
protect features would address this, but it seems like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets the
check box to lock (box checked), and calls the macro (by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check box to
unlock (box uncheched), and calls the macro to enable
the
particular controls, allowing their values to be reset.

Thanks so much for your time!
William DeLeo




.

I am getting a compile error:

"User-defined type not defined" with reference to line:

TypeOf obj.Object Is MSForms.CheckBox

???


-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"
wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio buttons
and
check boxes. After some data is entered and the buttons
and check boxes are set, I'd like to disable them so
thay
cannot be inadvertantly changed. I thought the lock and
protect features would address this, but it seems like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets the
check box to lock (box checked), and calls the macro (by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check box to
unlock (box uncheched), and calls the macro to enable
the
particular controls, allowing their values to be reset.

Thanks so much for your time!
William DeLeo




.

Inside the VBE, tools|References and check
MS Forms 2.0 Object library

(And are your checkboxes/optionbuttons from the control toolbox toolbar or the
Forms toolbar?)



william deleo wrote:

I am getting a compile error:

"User-defined type not defined" with reference to line:

TypeOf obj.Object Is MSForms.CheckBox

???

-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"
wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio buttons
and
check boxes. After some data is entered and the buttons
and check boxes are set, I'd like to disable them so
thay
cannot be inadvertantly changed. I thought the lock and
protect features would address this, but it seems like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets the
check box to lock (box checked), and calls the macro (by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check box to
unlock (box uncheched), and calls the macro to enable
the
particular controls, allowing their values to be reset.

Thanks so much for your time!
William DeLeo




.


--

Dave Peterson

Ok ... that library was not checked, so I fixed that
(thanks!). And yes they were off the forms toolbar, so
that made the error message go away.

But, the conditional isn't finding any checkboxes or
control buttons on the sheet (I added a simple message box
inside and it never comes up) and therefore it never
executes the enable/disable. Is there a way to view the
objects contained in "ActiveSheet.OLEObjects"?

Thanks so much!



-----Original Message-----
Inside the VBE, tools|References and check
MS Forms 2.0 Object library

(And are your checkboxes/optionbuttons from the control
toolbox toolbar or the
Forms toolbar?)



william deleo wrote:

I am getting a compile error:

"User-defined type not defined" with reference to line:

TypeOf obj.Object Is MSForms.CheckBox

???

-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"
wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio
buttons
and
check boxes. After some data is entered and the
buttons
and check boxes are set, I'd like to disable them so
thay
cannot be inadvertantly changed. I thought the lock
and
protect features would address this, but it seems
like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet
which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets
the
check box to lock (box checked), and calls the macro
(by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check
box to
unlock (box uncheched), and calls the macro to enable
the
particular controls, allowing their values to be
reset.

Thanks so much for your time!
William DeLeo




.


--

Dave Peterson

.

For items from the forms toolbar

Sub tester10()
Dim cbx As CheckBox
Dim oBtn As OptionButton
For Each cbx In ActiveSheet.CheckBoxes
cbx.Enabled = False
Next
For Each oBtn In ActiveSheet.OptionButtons
oBtn.Enabled = False
Next

End Sub

--
Regards,
Tom Ogilvy


"william deleo" wrote in message
...
Ok ... that library was not checked, so I fixed that
(thanks!). And yes they were off the forms toolbar, so
that made the error message go away.

But, the conditional isn't finding any checkboxes or
control buttons on the sheet (I added a simple message box
inside and it never comes up) and therefore it never
executes the enable/disable. Is there a way to view the
objects contained in "ActiveSheet.OLEObjects"?

Thanks so much!



-----Original Message-----
Inside the VBE, tools|References and check
MS Forms 2.0 Object library

(And are your checkboxes/optionbuttons from the control
toolbox toolbar or the
Forms toolbar?)



william deleo wrote:

I am getting a compile error:

"User-defined type not defined" with reference to line:

TypeOf obj.Object Is MSForms.CheckBox

???

-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"
wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio
buttons
and
check boxes. After some data is entered and the
buttons
and check boxes are set, I'd like to disable them so
thay
cannot be inadvertantly changed. I thought the lock
and
protect features would address this, but it seems
like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet
which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets
the
check box to lock (box checked), and calls the macro
(by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check
box to
unlock (box uncheched), and calls the macro to enable
the
particular controls, allowing their values to be
reset.

Thanks so much for your time!
William DeLeo




.


--

Dave Peterson

.

Or just:

With ActiveSheet
.CheckBoxes.Enabled = False
.OptionButtons.Enabled = False
End With

<g

Regards,

Vasant.


"Tom Ogilvy" wrote in message
...
For items from the forms toolbar

Sub tester10()
Dim cbx As CheckBox
Dim oBtn As OptionButton
For Each cbx In ActiveSheet.CheckBoxes
cbx.Enabled = False
Next
For Each oBtn In ActiveSheet.OptionButtons
oBtn.Enabled = False
Next

End Sub

--
Regards,
Tom Ogilvy


"william deleo" wrote in message
...
Ok ... that library was not checked, so I fixed that
(thanks!). And yes they were off the forms toolbar, so
that made the error message go away.

But, the conditional isn't finding any checkboxes or
control buttons on the sheet (I added a simple message box
inside and it never comes up) and therefore it never
executes the enable/disable. Is there a way to view the
objects contained in "ActiveSheet.OLEObjects"?

Thanks so much!



-----Original Message-----
Inside the VBE, tools|References and check
MS Forms 2.0 Object library

(And are your checkboxes/optionbuttons from the control
toolbox toolbar or the
Forms toolbar?)



william deleo wrote:

I am getting a compile error:

"User-defined type not defined" with reference to line:

TypeOf obj.Object Is MSForms.CheckBox

???

-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"
wrote in message
...
Hello,

Is there a way to disable worksheet controls? For
example, I have a sheet with a series of radio
buttons
and
check boxes. After some data is entered and the
buttons
and check boxes are set, I'd like to disable them so
thay
cannot be inadvertantly changed. I thought the lock
and
protect features would address this, but it seems
like
they do not effect the value of the control (only the
location and size and so forth).

In the end, I'd like a macro button on the sheet
which
enables and disables particular controls based on the
value of a sepparate check box. I.e., the user sets
the
check box to lock (box checked), and calls the macro
(by
pressing a button) to disable particular controls and
fixing their values. Or, the user sets the check
box to
unlock (box uncheched), and calls the macro to enable
the
particular controls, allowing their values to be
reset.

Thanks so much for your time!
William DeLeo




.


--

Dave Peterson

.

I can't thank you all enough. I hope this job pays well ;)


-----Original Message-----
Or just:

With ActiveSheet
.CheckBoxes.Enabled = False
.OptionButtons.Enabled = False
End With

<g

Regards,

Vasant.


"Tom Ogilvy" wrote in message
...
For items from the forms toolbar

Sub tester10()
Dim cbx As CheckBox
Dim oBtn As OptionButton
For Each cbx In ActiveSheet.CheckBoxes
cbx.Enabled = False
Next
For Each oBtn In ActiveSheet.OptionButtons
oBtn.Enabled = False
Next

End Sub

--
Regards,
Tom Ogilvy


"william deleo"
wrote in message
...
Ok ... that library was not checked, so I fixed that
(thanks!). And yes they were off the forms toolbar,
so
that made the error message go away.

But, the conditional isn't finding any checkboxes or
control buttons on the sheet (I added a simple
message box
inside and it never comes up) and therefore it never
executes the enable/disable. Is there a way to view
the
objects contained in "ActiveSheet.OLEObjects"?

Thanks so much!



-----Original Message-----
Inside the VBE, tools|References and check
MS Forms 2.0 Object library

(And are your checkboxes/optionbuttons from the
control
toolbox toolbar or the
Forms toolbar?)



william deleo wrote:

I am getting a compile error:

"User-defined type not defined" with reference to
line:

TypeOf obj.Object Is MSForms.CheckBox

???

-----Original Message-----
Control Toolbox Toolbar Controls:

Sub Tester1()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Or _
TypeOf obj.Object Is MSForms.OptionButton
Then
obj.Object.Enabled = False
End If
Next

End Sub

--
Regards,
Tom Ogilvy



"William Deleo"

wrote in message
...
Hello,

Is there a way to disable worksheet controls?
For
example, I have a sheet with a series of radio
buttons
and
check boxes. After some data is entered and the
buttons
and check boxes are set, I'd like to disable
them so
thay
cannot be inadvertantly changed. I thought the
lock
and
protect features would address this, but it
seems
like
they do not effect the value of the control
(only the
location and size and so forth).

In the end, I'd like a macro button on the sheet
which
enables and disables particular controls based
on the
value of a sepparate check box. I.e., the user
sets
the
check box to lock (box checked), and calls the
macro
(by
pressing a button) to disable particular
controls and
fixing their values. Or, the user sets the
check
box to
unlock (box uncheched), and calls the macro to
enable
the
particular controls, allowing their values to be
reset.

Thanks so much for your time!
William DeLeo




.


--

Dave Peterson

.





.