I tried creating a macro (with chatGPT, not too great with VBA myself) that hides the lay-out of certain cells based on its value and shows it again when clicked upon a second time.
However I can't seem to get it exactly right.
In the image you can see what the worksheet looks like. The goal is to get the macro to hide all of the cells containing the value 0, so the lay-out and the value 0 itself.
The 0 is a result of a drop-down list selection from another tab that hasn't been filled.
The macro does a good job of selecting the cells containing a 0, but it removes all edges of the cells on the first click (hide) and uses a slightly different colour on the second click (show)
However I can't seem to get it exactly right.
In the image you can see what the worksheet looks like. The goal is to get the macro to hide all of the cells containing the value 0, so the lay-out and the value 0 itself.
The 0 is a result of a drop-down list selection from another tab that hasn't been filled.
The macro does a good job of selecting the cells containing a 0, but it removes all edges of the cells on the first click (hide) and uses a slightly different colour on the second click (show)
VBA Code:
Dim originalFormats As Collection
Dim isHidden As Boolean
Sub ToggleZeroValueFormatting()
Dim rng As Range
Set rng = ActiveSheet.UsedRange ' Pas dit aan naar het gewenste bereik
If originalFormats Is Nothing Then
' Bewaar de originele opmaak en waarde van cellen met waarde 0
Set originalFormats = New Collection
For Each cell In rng
If cell.Value = 0 Then
originalFormats.Add Array(cell.Address, cell.Interior.ColorIndex, cell.Font.ColorIndex, cell.Value, cell.Borders(xlEdgeTop).LineStyle, cell.Borders(xlEdgeBottom).LineStyle, cell.Borders(xlEdgeLeft).LineStyle, cell.Borders(xlEdgeRight).LineStyle)
cell.Interior.ColorIndex = xlNone
cell.Font.ColorIndex = xlNone
cell.Value = ""
cell.Borders(xlEdgeTop).LineStyle = xlNone
cell.Borders(xlEdgeBottom).LineStyle = xlNone
cell.Borders(xlEdgeLeft).LineStyle = xlNone
cell.Borders(xlEdgeRight).LineStyle = xlNone
End If
Next cell
isHidden = True
Else
' Herstel de originele opmaak en waarde van cellen
For Each formatInfo In originalFormats
Set cell = rng.Parent.Range(formatInfo(0))
cell.Interior.ColorIndex = formatInfo(1)
cell.Font.ColorIndex = formatInfo(2)
cell.Value = formatInfo(3)
cell.Borders(xlEdgeTop).LineStyle = formatInfo(4)
cell.Borders(xlEdgeBottom).LineStyle = formatInfo(5)
cell.Borders(xlEdgeLeft).LineStyle = formatInfo(6)
cell.Borders(xlEdgeRight).LineStyle = formatInfo(7)
Next formatInfo
Set originalFormats = Nothing
isHidden = False
End If
End Sub