×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel VBA: Selecting Cells

Excel VBA: Selecting Cells

Excel VBA: Selecting Cells

(OP)
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!

RE: Excel VBA: Selecting Cells

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.

RE: Excel VBA: Selecting Cells

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources