Form Controls on a worksheet and Macro

P

Peter M

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
 
B

Bob Phillips

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)
 
M

Mark

Peter M said:
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
Click to expand...

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

Peter M

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
 
D

Dave Peterson

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

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