Form Controls on a worksheet and Macro
 

I have a number of checkboxes on a worksheet that I wish to disable or
enable under macro control, but I dont seem to be able to identify them in
the excel object model.

Any hints or directions gratefully received.

Peter

Form Controls on a worksheet and Macro
 

Peter,

For Each chk In Activesheet.Checkboxes
Debug.Print chk.Name, chk.Value
Next chk

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Peter M" wrote in message
...
I have a number of checkboxes on a worksheet that I wish to disable or
enable under macro control, but I dont seem to be able to identify them in
the excel object model.

Any hints or directions gratefully received.

Peter

Form Controls on a worksheet and Macro
 

"Peter M" wrote in message ...
I have a number of checkboxes on a worksheet that I wish to disable or
enable under macro control, but I dont seem to be able to identify them in
the excel object model.

Any hints or directions gratefully received.

Peter

Just encountered a similar problem myself. My problem was a bit more
complicated but the solution was similar; Here it is...

'In this function a Boolean is passed in to determine the Enabled
property
'of the checkbox. It cuts your coding in half. The screenupdating is
'probably unnecessary, but it is a habit of mine.


Function CheckboxesONOFF(EnableDisable As Boolean)

Application.ScreenUpdating = False
Dim CBX As OLEObject
For Each CBX In Sheets("YOUR SHEET NAME").OLEObjects
If TypeOf CBX.Object Is MSForms.CheckBox Then
CBX.Enabled = EnableDisable
End If
Next
Application.ScreenUpdating = True

End Function

With the above function you can enter some code into both of your
command buttons That would look like this...


CmdEnable_Click()
Call CheckboxesONOFF(True)
End Sub

CmdDisable_Click()
Call CheckboxesONOFF(False)
End Sub

Hope that gets the job done for you.

Form Controls on a worksheet and Macro
 

Bob,

I have now been able to identify the checkboxes as you describe, but the
following line of code achieves nothing, where m is set to the relevant
checkbox index number:

activesheet.checkboxes(m).enabled = false

I have also tried setting the .visible property to false, but that seems to
have no effect either?

What am I doing wrong?

Peter


"Bob Phillips" wrote in message
...
Peter,

For Each chk In Activesheet.Checkboxes
Debug.Print chk.Name, chk.Value
Next chk

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Peter M" wrote in message
...
I have a number of checkboxes on a worksheet that I wish to disable or
enable under macro control, but I dont seem to be able to identify them
in
the excel object model.

Any hints or directions gratefully received.

Peter

Form Controls on a worksheet and Macro
 

Maybe the index isn't what you think it is. Can you use its name?

ActiveSheet.CheckBoxes("check box 1").Enabled = False



Peter M wrote:

Bob,

I have now been able to identify the checkboxes as you describe, but the
following line of code achieves nothing, where m is set to the relevant
checkbox index number:

activesheet.checkboxes(m).enabled = false

I have also tried setting the .visible property to false, but that seems to
have no effect either?

What am I doing wrong?

Peter

"Bob Phillips" wrote in message
...
Peter,

For Each chk In Activesheet.Checkboxes
Debug.Print chk.Name, chk.Value
Next chk

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Peter M" wrote in message
...
I have a number of checkboxes on a worksheet that I wish to disable or
enable under macro control, but I dont seem to be able to identify them
in
the excel object model.

Any hints or directions gratefully received.

Peter





--

Dave Peterson