Selecting all cells in a worksheet using commandbutton

**ingolf_ingolfsen** · 06-28-2011, 04:39 AM

Something strange (at least for me).
When I record a macro that selects all the cells in a workbook and clear all content the code looks like this:

Sub Macro14()
 Sheets("Values to EIS").Select
 Cells.Select
    Selection.ClearContents
End Sub

This works fine. However when I have the same code inside a commandobutton it fails and gives me the error code "application-defined or object defined error". Anyone that knows why and how this can be done correct? I want this to be done within the commandbutton code.

The code that fails looks like:

Private Sub CommandButton2_Click()
    Sheets("Values to EIS").Select
   Cells.Select
    Selection.ClearContents
End Sub

**vikas.bhandari** · 06-28-2011, 04:50 AM

I would do this :


Private Sub Commandbutton1_Click()
    Dim sht As Worksheet
    
    Set sht = Worksheets("YourSheetName")
    sht.Cells.ClearContents
End Sub

This would work anytime, anywhere in your workbook.

Thanks,
Vikas

**romperstomper** · 06-28-2011, 05:09 AM

As you aren't reusing the variable, you can just use:

Sub Macro14()
 Sheets("Values to EIS").Cells.ClearContents
End Sub

or even:

Sub Macro14()
 Sheets("Values to EIS").Usedrange.ClearContents
End Sub

Thread: Selecting all cells in a worksheet using commandbutton

Thread Tools

Display

Selecting all cells in a worksheet using commandbutton

Re: Selecting all cells in a worksheet using commandbutton

Re: Selecting all cells in a worksheet using commandbutton

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions