Spaces:
Sleeping
Sleeping
File size: 5,024 Bytes
0dc6fb4 b084340 0dc6fb4 b084340 0dc6fb4 b084340 0dc6fb4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | Attribute VB_Name = "ClearClearedData"
Option Explicit
' =============================================================================
' Clear Data button macro for the "Cleared Data" worksheet.
'
' INSTALL:
' 1. Import ClearClearedData.bas into Template.xlsm
' 2. On Cleared Data sheet: Developer → Insert → Button (Form Control)
' Draw the button, assign macro: ClearClearedDataSheet
' Edit button text to: Clear Data
' Or run: CreateClearDataButton (once) to place the button automatically
'
' Clears A2 onward (including ItemCode in column A); preserves row 1 headers.
' Does not delete rows/columns, formatting, buttons, or sheet structure.
' Independent of AmazonUrlCleaner and ClearSheet.ClearSheetData.
' =============================================================================
Private Const CLEARED_DATA_SHEET As String = "Cleared Data"
Private Const FIRST_DATA_ROW As Long = 2
Private Const FIRST_DATA_COL As Long = 1
Public Sub ClearClearedDataSheet()
Dim ws As Worksheet
Dim dataRange As Range
Dim previousEnableEvents As Boolean
Dim previousScreenUpdating As Boolean
Dim answer As VbMsgBoxResult
On Error GoTo ErrorHandler
Set ws = GetClearedDataSheet()
If ws Is Nothing Then
MsgBox "Worksheet '" & CLEARED_DATA_SHEET & "' was not found.", _
vbExclamation, "Clear Data"
Exit Sub
End If
answer = MsgBox( _
"Are you sure you want to clear all data from the Cleared Data sheet?", _
vbYesNo + vbExclamation, _
"Clear Data")
If answer <> vbYes Then Exit Sub
previousEnableEvents = Application.EnableEvents
previousScreenUpdating = Application.ScreenUpdating
Application.EnableEvents = False
Application.ScreenUpdating = False
Set dataRange = GetClearedDataClearRange(ws)
If Not dataRange Is Nothing Then
' ClearContents keeps formatting, formulas structure, buttons, sheet layout
dataRange.ClearContents
End If
Application.EnableEvents = previousEnableEvents
Application.ScreenUpdating = previousScreenUpdating
MsgBox "Cleared Data sheet has been successfully cleared.", _
vbInformation, "Clear Data"
Exit Sub
ErrorHandler:
Application.EnableEvents = previousEnableEvents
Application.ScreenUpdating = previousScreenUpdating
MsgBox "Clear Data could not be completed." & vbCrLf & _
Err.Description, vbCritical, "Clear Data"
End Sub
' Place / refresh a Form Control button labeled "Clear Data" on Cleared Data.
Public Sub CreateClearDataButton()
Dim ws As Worksheet
Dim btn As Button
Dim existing As Shape
Dim leftPos As Double
Dim topPos As Double
On Error GoTo ErrorHandler
Set ws = GetClearedDataSheet()
If ws Is Nothing Then
MsgBox "Worksheet '" & CLEARED_DATA_SHEET & "' was not found.", _
vbExclamation, "Clear Data"
Exit Sub
End If
' Remove previous auto-created button if present
On Error Resume Next
For Each existing In ws.Shapes
If existing.Type = msoFormControl Then
If StrComp(existing.OnAction, "ClearClearedDataSheet", vbTextCompare) = 0 Or _
StrComp(existing.Name, "btnClearClearedData", vbTextCompare) = 0 Then
existing.Delete
End If
End If
Next existing
On Error GoTo ErrorHandler
leftPos = ws.Range("L1").Left
topPos = ws.Range("L1").Top
Set btn = ws.Buttons.Add(leftPos, topPos, 100, 28)
With btn
.Name = "btnClearClearedData"
.OnAction = "ClearClearedDataSheet"
.Characters.Text = "Clear Data"
End With
MsgBox "Clear Data button added to the Cleared Data sheet.", _
vbInformation, "Clear Data"
Exit Sub
ErrorHandler:
MsgBox "Could not create the Clear Data button." & vbCrLf & _
Err.Description, vbCritical, "Clear Data"
End Sub
Private Function GetClearedDataSheet() As Worksheet
On Error Resume Next
Set GetClearedDataSheet = ThisWorkbook.Worksheets(CLEARED_DATA_SHEET)
On Error GoTo 0
End Function
' Data area: A2 through last used cell (keeps row 1 headers only).
Private Function GetClearedDataClearRange(ByVal ws As Worksheet) As Range
Dim lastRow As Long
Dim lastCol As Long
Dim colIndex As Long
Dim colLastRow As Long
Dim used As Range
On Error Resume Next
Set used = ws.UsedRange
On Error GoTo 0
If used Is Nothing Then Exit Function
lastRow = FIRST_DATA_ROW - 1
lastCol = used.Column + used.Columns.Count - 1
If lastCol < FIRST_DATA_COL Then Exit Function
For colIndex = FIRST_DATA_COL To lastCol
colLastRow = ws.Cells(ws.Rows.Count, colIndex).End(xlUp).Row
If colLastRow > lastRow Then lastRow = colLastRow
Next colIndex
If lastRow < FIRST_DATA_ROW Then Exit Function
Set GetClearedDataClearRange = ws.Range( _
ws.Cells(FIRST_DATA_ROW, FIRST_DATA_COL), _
ws.Cells(lastRow, lastCol))
End Function
|