CODEWITHSUNDEEP
vbaexcel
user3438974
user3438974

Reputation: 27

Clearing cells in excel and 6 checkboxes

I have a button in Excel with a VBA script attached to it to clear certain cells on a sheet.

Is there a cleaner code to achieve the same result? Also for my checkboxes for some reason 1 piece of code clears all 6 boxes, is this right?

The checkboxes are activeX boxes I added through the developer view.

   Sub ClearForm()
   Range("I9:I10").Select
   Selection.ClearContents

   Range("I13:I17").Select
   Selection.ClearContents

   Range("H20").Select
   Selection.ClearContents

   Range("C5").Select
   Selection.ClearContents

   Range("C9:C10").Select
   Selection.ClearContents

   Range("C13:C18").Select
   Selection.ClearContents

    Dim OleObj As OLEObject

    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.CheckBox.1" Then
            OleObj.Object = False
       End If
    Next OleObj
End Sub

Upvotes: 0

Views: 1241

Answers (2)

Cor_Blimey
Cor_Blimey

Reputation: 3310

To answer your first question:

Range.Select is unecessary and actually slows execution down a bit. You can directly call .ClearContents on the range object itself. I imagine you did the Range.Select bit because of macro recorder. Whilst a useful tool to learn about Excel object model, also bear in mind that it does churn out some ugly bits ;-)

As you are performing the same set of commands several times, it is also generally better practice to encapsulate the steps into their own sub to a) have a neater 'main' procedure and b) easily enable to you adapt the process, should you later wish it.

However, this second point is definitely optional if this is a minor and unlikely to change part of your code, as the actual 'step' is just a single call to Range.ClearContents. Indeed, if it is literally intended to only clear 5 cells, I would probably just keep it like you do in the sub. I show the alternative below just for reference for more complex bits in the future.

Finally, if the group of ranges you wish to clear or do things to could change, it is also considered good practice to define a set of the ranges and enumerate the set (aka "looping through") rather than specifying identical commands for each range itself. Most objects will be handled through a Collection or an array. However for ranges, a range can contain many ranges of contiguous and non-contiguous cells. Each group of contiguous cells is referenced by using the .Areas property of the range object, which returns a range of the area. I also demonstrate this below.

Note that as another answerer has shown, Range.ClearContents can operate on multiple Areas at once so looping through Areas is not necessary in this case (it is for many other operations).

For example:

Sub ClearForm()

   Dim rng As Range

   For Each rng in Range("I9:I10,I13:I17,...etc...").Areas
       clearRange rng
   Next rng

   ...Rest of code...

End Sub
Private Sub clearRange(rng As Range)
    rng.ClearContents
End Sub

With respect to your second question: the 6 checkboxes are cleared because you loop through all activex objects on the sheet in the last part of your code and set their Value property to False, which for a checkbox unchecks it.

Upvotes: 3

fnostro
fnostro

Reputation: 4591

Well for one thing you can have a string of ranges and do it all at once.

Range("I9:I10, I13:I17, H20, C5, C9:C10, C13:C18").Select
Selection.ClearContents

as for your check-boxen, if by "clear" you mean "Removes" or "Deletes" then yes it makes sense, if on the other hand all you want to do is clear a checkbox then I think this is what you need in the loop:

        OleObj.Object.Value = False

Upvotes: 3

Related Questions