How to hide option button on Userform if the caption has no text-CodePudding

I have 4 option buttons and their caption is set to different cells. However, sometimes that cell value doesn't contain any text, and I want to hide the option button from the Userform if that is the case. But my code is hiding the option buttons even if the caption contains text. I'm sure this is simple, but I can't solve it.

Call ifBlank

OptionButton1.Caption = qRange1.Value
OptionButton2.Caption = qRange2.Value
OptionButton3.Caption = qRange3.Value
OptionButton4.Caption = qRange4.Value

Sub ifBlank()


If OptionButton3.Caption = "" Then

    OptionButton3.Visible = False

If OptionButton4.Caption = "" Then

    OptionButton4.Visible = False
    
End If
End If

CodePudding user response：

a) Call ifBlank after setting the captions, not before.

b) You can simply write

Sub ifBlank()
    OptionButton1.Visible = (OptionButton1.Caption <> "")
    OptionButton2.Visible = (OptionButton2.Caption <> "")
    OptionButton3.Visible = (OptionButton3.Caption <> "")
    OptionButton4.Visible = (OptionButton4.Caption <> "")
End If

CodePudding user response：

You can make your like easier by collating your option buttons and QRanges in collections. Your code will be a little more extensive when setting up the user form but later on code becomes simpler.

Option Explicit

Private Type State

    Buttons                 As Collection
    QRanges                 As Collection

End Type

Private s                   As State


Private Sub UserForm_Initialize()

    Set s.Buttons = New Collection
    With s.Buttons
    
        .Add OptionButton1
        .Add OptionButton2
        .Add OptionButton3
        .Add OptionButton4
        
    End With
    
    Set s.QRanges = New Collection
    With s.QRanges
    
        .Add QRange1
        .Add QRange2
        .Add QRange3
        .Add QRange4
        
    End With
    
    'other initialisation code
    
End Sub



Public Sub UpdateButtonCaptions()

    Dim myIndex As Long
    For myIndex = 1 To s.Buttons.Count
    
        ' the test for an QRange may need to be more rigourous
        If s.QRanges(myIndex) = "" Then
       
            s.Buttons(myIndex).Visible = False
            
        Else
        
            s.Buttons(myIndex).Visible = True
            s.Buttons(myIndex) = s.QRanges(myIndex).Value
    
        End If
    
    Next
    
End Sub

I'm not a regular excel/form user so you may need to tweak some of the code above.