Form Controls on a worksheet and Macro

  • Thread starter Thread starter Peter M
  • Start date Start date
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
 
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 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.
 
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
 
Maybe the index isn't what you think it is. Can you use its name?

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