CODEWITHSUNDEEP
vbaexcel
whydoieven
whydoieven

Reputation: 599

Applying a macro to an entire column

I have a macro which when run, opens up file explorer and when the user selects the particular path, it pastes that path on the active cell. Here is the code. 

Sub CommandButton1_Click()
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = IIf(ActiveCell.Value = "", ActiveWorkbook.Path, ActiveCell.Value)
        .Title = "Please choose a folder"
        .AllowMultiSelect = False
        If .Show = -1 Then ActiveCell.Value = .SelectedItems(1)
    End With
End Sub

Now, I do not want to assign this Module to a button but to an entire column, say column A so whenever the user clicks on any row within column A the macro gets activated. Is there any way I can do this?

Upvotes: 0

Views: 1125

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Put this in the code module for the sheet where you want to trigger the folder selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.CountLarge = 1 And Target.Cells(1).Column = 1 Then
        With Application.FileDialog(msoFileDialogFolderPicker)
          .InitialFileName = IIf(target.Value = "", _
                          thisWorkbook.Path, target.Value)
          .Title = "Please choose a folder"
          .AllowMultiSelect = False
          If .Show = -1 Then Target.Value = .SelectedItems(1)
        End With
    End If

End Sub

For double-click:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Cells.CountLarge = 1 And Target.Cells(1).Column = 1 Then
        With Application.FileDialog(msoFileDialogFolderPicker)
          .InitialFileName = IIf(ActiveCell.Value = "", ActiveWorkbook.Path, ActiveCell.Value)
          .Title = "Please choose a folder"
          .AllowMultiSelect = False
          If .Show = -1 Then Target.Value = .SelectedItems(1)
        End With
        Cancel = True '<< prevents going into edit mode
    End If

End Sub

Upvotes: 1

Related Questions