Enable and disable worksheet controls?

[William Deleo]

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

 

[Tom Ogilvy]

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

 

[william deleo]

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

 

[william deleo]

I am getting a compile error:

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

TypeOf obj.Object Is MSForms.CheckBox

???

 

[Dave Peterson]

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]

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!

 

[Tom Ogilvy]

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

 

[Vasant Nanavati]

Or just:

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

<g>

Regards,

Vasant.

 

[william deleo]

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