How to clear contents of an Excel Workbook through vba

0 votes

I'm using a master worksheet to automate data. I want a message box to display and clear the contents of particular columns in tables in one of the worksheets when I start the workbook. The "Application-defined or object-defined error" keeps occurring. The code for my "This workbook" section is as follows:

Option Explicit

Sub Workbook_Open()

Dim answer As Integer

answer = MsgBox("Do you want to clear the totals?", vbYesNo + vbQuestion, "Clear Totals")

If answer = vbYes Then

Call Sheet1.ClearContents_1

End If

End Sub

This is my Sheet1 code:

Sub ClearContents_1()

Call Loop_Clear_C

Call Loop_Clear_M

Call Clear_S

End Sub

Sub Loop_Clear_C()

For i = 1 To Range("UserTable79").Rows.Count
    Range("UserTable79[Total]")(i) = 0
Next i
End Sub

Sub Loop_Clear_M()

For i = 1 To Range("ServiceTable79").Rows.Count
    Range("ServiceTable79[Total]")(i) = 0
Next i
End Sub

Sub Clear_S()

Range("TotalTable79[Actual Total]").ClearContents

End Sub

They worked separately but not together. The msg box comes up but doesn't run the Sheet1 code. How do I call upon this sheet code?

Nov 19, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes
When the range you're referring to doesn't exist, the "Application-defined or object-defined error" notice frequently appears. When you activate Sheet1 by clicking on it, try using the Immediate panel to execute an operation on the ranges you're referring to (try entering Range in the Immediate panel, for example) ("UserTable79"). Get an error when you select)?

When calling a function from the "ThisWorkbook" section, the worksheet must be specified explicitly (for example, if the worksheet is named "SheetName," you must specify Call ThisWorkbook).


It may be easier to use ClearContents 1 rather than Call Sheet1.ClearContents 1).
answered Nov 19, 2022 by narikkadan
• 63,180 points

Related Questions In Others

0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21 in Others by Kithuzzz
• 38,010 points
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,180 points
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,180 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,220 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Python - how to read contents of an excel file

xlwings is an excellent library to interact with ...READ MORE

answered Dec 15, 2022 in Others by narikkadan
• 63,180 points
0 votes
1 answer

How do I change the format of an excel workbook from 'General' to 'Text'

Only cells have a format for numbers. ...READ MORE

answered Mar 23 in Others by narikkadan
• 63,180 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP