CODEWITHSUNDEEP
excelvba
Mr Mashabela
Mr Mashabela

Reputation: 112

How to count number of cell in a worksheet using vba

im new to Vba, I have 5 different sheet named sheet 1 to 5, the 1st sheet has a Button and Label so i want to select everything in sheet 3 and when i press the Button i want it to show me the number of cells that i selected in a Label

Sub Button2_Click()

Dim rngCell As Range, arrArray() As Variant, i As Integer

ReDim arrArray(1 To Selection.Cells.Count)

i = 1
For Each rngCell In Selection

    arrArray(i) = rngCell.Value
    i = i + 1

Next

ActiveSheet.Shapes("Label 1").Select
Selection.Characters.Text = i


End Sub

Upvotes: 0

Views: 3379

Answers (2)

CuberChase
CuberChase

Reputation: 4518

This will do it but it's not a very elegant way of doing things - I can't see any alternatives though. You need to utilise Events in order to capture the worksheet that was previously selected. Since the only way of determining the range of a selection on a worksheet is to Activate that worksheet, you have to turn off screen updating jump to the worksheet and then jump back to the original worksheet and turn screen updating back on.

Put the following code in a new module: Option Explicit

'Global variables (avoid using globals if you can)
Public wsLast As Worksheet
Public iSelectedCells As Integer

'Link this sub to your button
Public Sub CountCells()
    If Not wsLast Is Nothing Then
        Sheets("Sheet1").Shapes("Label 1").TextFrame.Characters.Text = "There " & IIf(iSelectedCells = 1, " is " & iSelectedCells & " cell selected ", " are " & iSelectedCells & " cells selected ") & "in " & wsLast.Name
    End If
End Sub

The following code needs to go in the 'ThisWorkbook' module of your spreadsheet:

Option Explicit

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Dim wsThisWorksheet As Worksheet

    'Turn off events to avoid triggering a loop
    Application.EnableEvents = False

    'Set this worksheet so we can come back to it
    Set wsThisWorksheet = ActiveSheet

    'Record the deactivated sheet as a global variable
    Set wsLast = Sh

    'Turn off screen updating, go back to the last sheet, count the selection
    Application.ScreenUpdating = False
    wsLast.Activate
    iSelectedCells = Selection.Cells.Count

    'Then come back to the original and turn screen updating back on
    wsThisWorksheet.Activate
    Application.ScreenUpdating = True

    'Restore events
    Application.EnableEvents = True

    'Set the local variable to nothing
    Set wsThisWorksheet = Nothing
End Sub

You could further enhance the code by checking if you're deactivating the worksheet with the button and ignore it if it is.

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27269

I think this is much simpler than you think ...

Option Explicit

Sub CaptureSelectionCount()

' Keyboard Shortcut: Ctrl+Shift+E '-> adjust to make sure this doesn't overwrite an existing function in your workbook
Dim lngCnt as Long

lngCnt = ActiveSheet.Selection.Cells.Count

Sheets("Sheet1").Shapes("Label 1").TextFrame.Characters.Text = lngCnt

End Sub

Upvotes: 1

Related Questions