Disable Optionbutton Control

[badgerzz1]

I have option buttons on an excel worksheet which are disabled when certain
tasks on the sheet are completed. I can enable and disable them OK but when
they are disabled there are "greyed out" - this results in poor quality
printing.

Is there a way to disable the control while retaining the text and
background properties of the control? i.e. black text

 

[Norman Jones]

Hi Badgerzz1,

In the Workbook's ThisWorkbook
module, paste:

'=============>>
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call EnableDisable(True)
Application.OnTime Now, "AfterPrint"
End Sub
'<<=============

This is event code and should be pasted
into the worksheets's code module (not a
standard module and not the workbook's
ThisWorkbook module):

Right-click the worksheet's tab |
Select 'View Code' from the menu
Paste the code
Alt-F11 to return to Excel.


In a standard module, paste:

'=============>>
Option Explicit

'-------------->>
Public Sub AfterPrint()
Call EnableDisable(False)
End Sub

'-------------->>
Public Sub EnableDisable(blEnable As Boolean)
Dim WB As Workbook
Dim SH As Worksheet
Dim arr As Variant
Dim oleObj As OLEObject
Dim i As Long

Set WB = Workbooks("Book3")
Set SH = WB.Sheets("Sheet1")

For Each oleObj In SH.OLEObjects
With oleObj
If TypeOf .Object Is MSForms.OptionButton Then
.Object.Enabled = blEnable
End If
End With
Next oleObj

End Sub
'<<=============

 

[Norman Jones]

Hi Badgerzz1,

Having re-read your requirement, replace
the sugested code witrh the following
version:

In the workbook's Thisworkbook
module, paste:

'=============>>
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim SH As Worksheet
Dim oleObj As OLEObject
Dim i As Long

Set SH = Me.Sheets(shName)
Erase arr

For Each oleObj In SH.OLEObjects
With oleObj
If TypeOf .Object Is MSForms.OptionButton Then
If .Object.Enabled = False Then
i = i + 1
ReDim Preserve arr(1 To i)
arr(i) = .Name
End If
End If
End With
Next oleObj

Call EnableDisable(True)
Application.OnTime Now, "AfterPrint"
End Sub
'<<=============

In a standard module, at the head of the
module, and before any other procedures,
paste the foling code:

'=============>>
Option Explicit
Public Const shName As String = "Sheet1" '<<==== CHANGE
Public arr() As String

'-------------->>
Public Sub AfterPrint()
Call EnableDisable(False)
End Sub

'-------------->>
Public Sub EnableDisable(blEnable As Boolean)
Dim WB As Workbook
Dim SH As Worksheet
Dim oleObj As OLEObject
Dim i As Long

Set WB = ThisWorkbook
Set SH = WB.Sheets(shName)

For i = LBound(arr) To UBound(arr)
With SH.OLEObjects(arr(i))
.Object.Enabled = blEnable
End With
Next i

End Sub
'<<=============