How do I create a selection set of user picked cells from which I can iterate through in Excel vba?

Question

My question pertains to Excel vba, how can I create a selection set of user defined cells in one active worksheet from which I can then iterate through? I see many many reference to the use of a range but in my case I'm looking to act on individual cells that could be random and anywhere on the sheet that the user chooses.

A simple example of what I'm trying to do would be: The user starts the command, selects a series of random cells by the Left Mouse Button and the Ctrl Key anywhere in the active sheet, after selection the cells then turn a particular color.

This wouldn't be range right? Because there is no relationship between the cells selected, and they wouldn't be the same cells, or the same pattern of cells every time?

I'm stuck right at the beginning, how do I create a selection set of user select cell addresses and then iterate through them in order to act on them, in this case change the color?

Welcome to S.O, May we ask you to provide the code you did try ? — TourEiffel, Nov 08 '21 at 13:06
Sounds like you just want to loop through [the `Areas`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.areas) in [the `Selection`](https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Selection)? — Chronocidal, Nov 08 '21 at 13:10
You don't need to iterate i.e. you can run a sub containing just e.g. `Selection.Interior.Color = vbYellow` after you have selected the cells as described. — VBasic2008, Nov 08 '21 at 15:12

score 0 · Answer 1 · answered Nov 10 '21 at 16:23

For anyone interested, what I was trying to do in Excel was:

"select cells that had the same value as the name of the sheet tabs at the bottom of the spreadsheet. The lefthand most column in the following Excel spreadsheet shows values that relate to the sheets. By selecting my tool you're able to 'hide' and 'unhide' them."

And this is the code I ended up with, with TourEiffel's help:

Sub vba_hide_selected()

'uses an input-box to collect all of the user selected cells
Set customrange = Application.InputBox(Prompt:="Select Cells by using Left Mouse button + Ctrl", Type:=8)
For Each cell In customrange
    Dim cn As String
    Dim cl As String
    'this is just to try and reduce errors by only processing selection in the "A" column
    cl = Split(cell.Address, "$")(1)
    If cl = "A" Then
        cn = cell.Value
        'Debug.Print cn
        Worksheets(cn).Visible = False
    End If
Next

    'make sure that "Summary Sheet" is active after routine
    Worksheets("Summary Sheet").Activate

End Sub

TourEiffel · Accepted Answer · 2021-11-08T13:18:32.663

-1

I think you want to do something like that :

Sub test()
     Application.InputBox(Prompt:="Select cells", Type:=8).Select
     For Each cell In Selection
        Debug.Print cell.Address
        'What you want
     Next
End Sub

Also I recommand to avoid .Select so Your code should be :

Sub test()
Dim customrange As Range
     Set customrange = Application.InputBox(Prompt:="Select Cells", Type:=8)
     For Each cell In customrange
         Debug.Print cell.Address
         'What you want
     Next
End Sub

To get each cell background color you can add in the For Each :

Debug.Print Cell.Interior.Color

Example of Selection :

Output :

$U$22
$V$30
$Z$29
$W$27
$U$27
$Y$14
$V$13
$T$14

edited Nov 08 '21 at 13:18

answered Nov 08 '21 at 13:12

TourEiffel

4,034
2
16
45

Good Afternoon TourEiffel, Thank You for your help. And this does work for what I wanted to do. I was wondering if there was a better user interface mechanism to collect all the selected cells other than an "Input Box" to gather multiple selections? – Grot Nov 08 '21 at 18:54
@Grot I think you can build your own UserForm to do this. Glad I could help there. – TourEiffel Nov 09 '21 at 08:34

How do I create a selection set of user picked cells from which I can iterate through in Excel vba?

2 Answers2