Toggle Data Button Excel VBA

Question

I have a range of data AO4:AO30. It is a list of dates, some cells are blank but most are populated. I want to make a macro button called “show data”. I want to button to clear the data when clicked and then repopulate the range with the same data once clicked again. The range is conditionally formatted to colour a Gantt chart style calendar so it matters if the data is actually there or not. I have no experience with VBA and have been having a hard time trying to google my way through it. Any advice helps, thanks!

I’ve tried getting chatgpt to make a code for me and that didn’t work. I’ve tried several websites but I don’t fully know what I’m looking for so that has been difficult.

Answer 1

Without more details or some code you've tried it's hard to say that this is the best answer, but here's an idea:

• The Show Data button will look at the range of cells with the dates in them, AO4:AO30 as you mentioned
• If there are dates in the range, they will be stored in a variable (or on another sheet if you prefer)
• If there are no dates in the range, they will be added back to the range
• Storing the dates in a variable is "cleaner" in the sense that you don't need another sheet/location to temporarily store data in, but it comes with the potential risk of losing your data if you close the sheet and save it while the dates are stored in it. There are ways to safeguard against this, but it would probably be simpler to just have the dates stored in Excel somewhere else.

Here's the data I'm using to test

Using a global variable to store and retrieve dates

Option Explicit

' This is my global variable to store the dates
Dim storageArray() As Variant

Private Sub ShowData()
    
    ' The range the dates are stored in (A1 to A4 on sheet1 in this example)
    Dim dateRange As Range
    Set dateRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A4")
    
    ' Check if the range is empty
    If WorksheetFunction.CountA(dateRange) = 0 Then
        
        ' The range is empty
        ' Add the dates back to the range
        dateRange.Value = storageArray
    
    Else
    
        ' The range is not empty
        ' Store the dates in the global variable and clear the range
        storageArray = dateRange.Value
        dateRange.Clear
        
    End If
    
End Sub

Using another location to store and retrieve dates

Option Explicit

Private Sub StoreData()

    ' The range the dates are stored in (A1 to A4 on sheet1 in this example)
    Dim dateRange As Range
    Set dateRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A4")
    
    ' The other range to temporarily store the dates
    ' Does not have to be on the same sheet
    Dim storageRange As Range
    Set storageRange = ThisWorkbook.Sheets("Sheet1").Range("C1:C4")
    
    ' Check if the range is empty
    If WorksheetFunction.CountA(dateRange) = 0 Then
        
        ' The range is empty
        ' Add the dates back to the range
        dateRange.Value = storageRange.Value
        storageRange.Clear
    
    Else
    
        ' The range is not empty
        ' Store the dates in the other location and clear the range
        storageRange.Value = dateRange.Value
        dateRange.Clear
        
    End If

End Sub