VBA code to click on a toggle button and hide some rows and columns in another sheet - how to unclick other buttons?

Question

I wrote the following code in VBA Excel. The intent is when we click on a toggle button, some rows and columns in another sheet will be hidden. I have 2 questions: 1) the operation is very slow, what's wrong with the code that takes too long? 2) how can I unclick the other 2 buttons automatically when I click on a button?

`Private Sub ToggleButton1_Click()
If ToggleButton1.Value Then
MsgBox "Both Earth and Moon are applicable. All rows and columns are now visible"
Sheets("DIA_ISO26262_ISO21448").Rows.Hidden = False
End If
End Sub

---

Private Sub ToggleButton3_Click()
Dim x As Range
If ToggleButton3.Value Then
MsgBox "Column D and Earth-specific rows are hidden"
For Each x In Sheets("DIA_ISO26262_ISO21448").Range("E3:E109")
    If x.Value = "" Then
    x.EntireRow.Hidden = True
    End If
Next x
Else
Sheets("DIA_ISO26262_ISO21448").Rows.Hidden = False
End If

Dim Earth As String
Earth = "D"
If ToggleButton3.Value Then

Application.Sheets("DIA_ISO26262_ISO21448").Columns(Earth).Hidden = True
Else
Application.Sheets("DIA_ISO26262_ISO21448").Columns(Earth).Hidden = False
End If
End Sub

---

Private Sub ToggleButton2_Click()
Dim c As Range
If ToggleButton2.Value Then
MsgBox "Column E and Moon-specific rows are hidden"
For Each c In Sheets("DIA_ISO26262_ISO21448").Range("D3:D109")
    If c.Value = "" Then
    c.EntireRow.Hidden = True
    End If
Next c
Else
Sheets("DIA_ISO26262_ISO21448").Rows.Hidden = False
 End If

Dim Moon As String
Moon = "E"
If ToggleButton2.Value Then

Application.Sheets("DIA_ISO26262_ISO21448").Columns(Moon).Hidden = True
Else
Application.Sheets("DIA_ISO26262_ISO21448").Columns(Moon).Hidden = False
End If
End Sub`

The code that I wrote is working but too slow.

Answer 1

Try something like this for question 1:

Dim x As Range
Dim mX As Range


For Each x In Sheets("DIA_ISO26262_ISO21448").Range("E3:E109")
  If x.Value2 = "" Then
    If mX Is Nothing Then
      Set mX = x
    Else
      Set mX = Union(mX, x)
    End If
  End If
Next

If Not mX Is Nothing Then
  mX.EntireRow.Hidden = True
End If

Answer 2

Concerning question 2, the toggling of buttons, I suggest outsourcing that to the mouseUp event. So something like this:

Private Sub ToggleButton1_Click()
    If ToggleButton1.Value Then
        MsgBox "Both Earth and Moon are applicable. All rows and columns are now visible"
        Sheets("DIA_ISO26262_ISO21448").Rows.Hidden = False
    End If
End Sub

Private Sub ToggleButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal Xs As Single, ByVal Ys As Single)
    ToggleButton2.Value = False
    ToggleButton3.Value = False
End Sub

Private Sub ToggleButton2_Click()
    Dim c As Range
    If ToggleButton2.Value Then
        MsgBox "Column E and Moon-specific rows are hidden"
        For Each c In Sheets("DIA_ISO26262_ISO21448").Range("D3:D109")
            If c.Value = "" Then
            c.EntireRow.Hidden = True
            End If
        Next c
    Else
        Sheets("DIA_ISO26262_ISO21448").Rows.Hidden = False
    End If
    
    Dim Moon As String
    Moon = "E"
    If ToggleButton2.Value Then
        Application.Sheets("DIA_ISO26262_ISO21448").Columns(Moon).Hidden = True
    Else
        Application.Sheets("DIA_ISO26262_ISO21448").Columns(Moon).Hidden = False
    End If
End Sub

Private Sub ToggleButton2_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal Xs As Single, ByVal Ys As Single)
    ToggleButton1.Value = False
    ToggleButton3.Value = False
End Sub

Private Sub ToggleButton3_Click()
    Dim X As Range
    If ToggleButton3.Value Then
        MsgBox "Column D and Earth-specific rows are hidden"
        For Each X In Sheets("DIA_ISO26262_ISO21448").Range("E3:E109")
            If X.Value = "" Then
            X.EntireRow.Hidden = True
            End If
        Next X
    Else
        Sheets("DIA_ISO26262_ISO21448").Rows.Hidden = False
    End If

    Dim Earth As String
    Earth = "D"
    If ToggleButton3.Value Then
        Application.Sheets("DIA_ISO26262_ISO21448").Columns(Earth).Hidden = True
    Else
        Application.Sheets("DIA_ISO26262_ISO21448").Columns(Earth).Hidden = False
    End If
End Sub

Private Sub ToggleButton3_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal Xs As Single, ByVal Ys As Single)
    ToggleButton1.Value = False
    ToggleButton2.Value = False
End Sub