Button To Clear multiple cells in multiple tabs

    I have a workbook with 11 tabs, plus one at the beginning marked as "Start Here". I want to create one button in that tab that will clear up to 9 individual cells in each of these tabs. The cells I want to clear do not necessarily reside in the same spot on each page. Is this possible and how is it done? I presume this can be done in VisualBasic, but would it be easier for a neophyte to create it using Macros?

    Macros and Visual Basic for Applications (VBA) are two ways of saying the same thing. Using the Macro Recorder writes a program in VBA. The VBA code generated by the Macro Recorder is inelegant but it should work for this. You can then use Design Mode to add a button to a sheet and assign your generated macro to that button.

    I understand that the end result should be the same when using VBA or Record a Macro. It seems easier to simply record keystrokes than writing code, but all of my attempts at recording a macro bring me a "Compile error: Invalid outside procedure". When searching on what that means, the explanation seems more to be correcting VisualBasic versus Recording Keystrokes. There was one video I saw that mentioned once it's better to use the keyboard to move keystrokes versus the mouse. Do you think that could be the problem? If so, would using Ctrl + PgDn fix that?


    Thanks,

    That error sounds as though you attempted to edit what the macro recorder created. The macro recorder does not "record keystrokes." It converts any relevant actions you do and creates VBA code to get to the same result. E.g., if you click on a cell, it does not record your moving the mouse and clicking; it instead writes the VBA code for that action: something like "Range("C5").Select."

    OK, perhaps it's what I am doing once after I click into the last cell on the last page. I stop the recording and I then go to "View". I select the Macros down arrow and select "View Macros". From there I select the one I created and saved. The button I created seems to go live, but I get that error as stated above. Am I missing a step, or a keystroke?


    Sorry for keep pressing, but I think I am close to it.


    Thanks,

    Hi JonathanVH, I am still having difficulty in creating this macro, but I found one that uses the cell color to ClearContents from multiple sheets, but the example I got was for a range of cells in a row. My application is not a range. Here is what i found. Do you have any suggestions? That color is a lime green that I looked up from this site - http://www.endprod.com/colors/216safe.htm


    Sub Macro()
    Dim ws As Worksheet, cell As Range
    For Each ws In Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11"))
    If cell.Interior.Color = 65280 Then
    cell.ClearContents
    End If
    Next cell
    Next ws
    End Sub

    You never mentioned anything about cell colors. I thought you had recorded a macro to delete the relevant cells. So you now want to clear the contents (but not the formatting?) of cells that are a particular color? That cannot be done with the macro recorder. Your code is missing the For statement to iterate through the cells on each sheet.

    JonathanVH, I am trying to ClearContent in any way possible, because all the ways I've tried thus far does not work including FormControls and ActiveX Controls

    Good day Jonathon and SHR,


    I've just been reading through this thread and I hope that you don't mind me putting in my two cents worth.


    I think that SHR's idea of using a cell colour to identify the cells in question is a good idea as they will vary in position on each sheet so perhaps the following code assigned to a button may help:-




    It will find any cell on any sheet (except the Start Here sheet) that has been coloured using the Color Palette and clear those cells of any content including the cell colour.


    I hope that this helps.


    Cheerio,
    vcoolio.

    SHR
    If you let us know which cells in each sheet you want to clear, someone can give you an easy to understand/adjust code to use.
    No use supplying a code that you don't understand and can't change for future similar projects.

    vcoolio, from my very, very basic understanding of Visual Basic, you macro looks like it would clear contents in any cell with color and unfortunately, I have some cells that are shaded that I do not want cleared.



    jolivanes, Below is the string of cells in each sheet that I want the ability to clear. I am uncertain if it matters, but each tab is named by me based upon its contents.


    Sheet2
    D9
    D25
    D36
    J25
    O25

    Sheet3
    D9
    D25
    D36
    J25
    N25

    Sheet4
    D9
    D24
    D35
    J24
    J35
    O24
    O35

    Sheet5
    D10
    D25
    D36
    J25
    J36
    O25
    O36

    Sheet6
    D10
    D26
    D35
    D51
    D62
    L51
    L62
    Q10
    Q26
    Q35
    Q51
    Q62

    Sheet7
    D11
    D25
    D35
    D51
    D62
    J51
    J62
    P12
    P25
    P35
    P51
    P62

    Sheet8
    D10
    D26
    D35
    D51
    D60
    L51
    L60
    Q11
    Q26
    Q36
    Q51
    Q60

    Sheet9
    D10
    D42
    I26
    I42
    O11
    O42

    Sheet10
    D10
    D42
    I26
    I42
    O11
    O42

    Sheet11
    D10
    D42
    I26
    I42
    N11
    N42

    Copy the sheet from the attached workbook into your workbook (hide it if you want) and run this code.
    Make sure to try it on a copy of your original workbook first so you can check to make sure it is OK


    Code
    Sub AAAAA()
Dim sh2 As Worksheet
Dim j As Long, i As Long
Set sh2 = Sheets("Cells To Be Cleared")
    For j = 1 To sh2.Cells(1, sh2.Columns.Count).End(xlToLeft).Column
        For i = 2 To sh2.Cells(Rows.Count, j).End(xlUp).Row
            Sheets(sh2.Cells(1, j).Text).Range(sh2.Cells(i, j)).ClearContents
        Next i
    Next j
End Sub


    [ATTACH]n1207473[/ATTACH]

    I had a suggestion for using a global named range for the cells, but, after playing with it, I realized that naming different ranges on multiple sheets is a difficult topic. One could, however, create a worksheet scoped name (using the same name) on each sheet and then iterate through those in the macro to clear each. E.g., if you select the cells and create a worksheet-scoped name "Clear" on each worksheet, then code like this should work:

    Code
    Sub ClearNamedRanges()
Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next
    ws.names("Clear").RefersToRange.ClearContents
    On Error Goto 0
  Next
End Sub


    Creating and maintaining the range names would be easy if you remember to scope them at the worksheet level (which means not using the Name Box to create), and the above macro is obviously simple and efficient.

    I tried pasting it into each sheet (1-11) and for the first time I got a reaction, which was a run time error. I chose debug and this popped up showing line four highlighted in yellow.


    Sub AAAAA()
    Dim sh2 As Worksheet
    Dim j As Long, i As Long
    Set sh2 = Sheets("Cells To Be Cleared")
    For j = 1 To sh2.Cells(1, sh2.Columns.Count).End(xlToLeft).Column
    For i = 2 To sh2.Cells(Rows.Count, j).End(xlUp).Row
    Sheets(sh2.Cells(1, j).Text).Range(sh2.Cells(i, j)).ClearContents
    Next i
    Next j
    End Sub

    Do I see that right that you want to clear all the cells below the word "Select"? Except for the first sheet ("START HERE!") of course.
    If so, you don't need to have all the cell addresses in your code.
    If that is indeed the case, copy/paste this code into Module1 and try it on a copy of your original first.



    Code
    Sub Maybe()
Dim sht As Worksheet, c As Range
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "START HERE!" Then
            For Each c In sht.UsedRange
                If Trim(c.Value) = "Select" Then c.Offset(1).ClearContents
            Next c
        End If
    Next sht
End Sub

    Yes, each cell below Select is the one I want the ability to clear. I pasted it into Module 1 and saved it. I went to the Developer site on the ribbon and clicked on Insert and chose a Forms Control button and it immediately prompted me to select the Macro, which is named Maybe. It still did not clear anything.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login