Reputation: 112
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
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
Reputation: 27259
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