Excel VBA: Selecting Cells

[khouzam]

Hi,

I'm trying to write a function that completes a task whether the user selects any single cell, or any adjoining multiple cells (eg any two cells side by side, or any two cells top and bottom, or any block of 4 or more).

This I want to do with an IF statement
ie
If [a single cell is selected] Then
do task
ElseIf [more than one cell is selected in one row] Then
do task
ElseIf [more than one cell is selected in one column] Then
do task
ElseIf [a block of cells is selected] Then
do task
End If

Can anyone please help me find out what the code for each argument in the square brackets above is?

Thanks!

 

[dsi]

You can use the Selection_Change event to trap the cell selection. This code would go in the code window for the desired worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address
End Sub

This sample shows how the target address (selected cells) are returned. You can use simple string comparison and manipulation to determing which criteria is being met.

Here are some examples of the output:
Single selected cell: $D$16
More than 1 cell in a row: $D$16:$G$16
More then 1 cell in a col: $D$16:$D$20
Selection that spans rows and cols: $D$16:$G$19

Hope that helps...



DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[yakpol]

khouzam,
try next code:

If selection.cells.count = 1 Then ' single cell
     do task
  ElseIf selection.rows.count = 1 Then 'single row
     do task
  ElseIf selection.columns.count = 1 then 'single column
     do task
  Else  ' block
     do task
  End If