Allow user to select a column and use the column as a reference point-CodePudding

I am attempting to allow the user to select a column through the application input, in order for me to be able to use the column as a reference point. However, have the following issues with my code;

Issues

My code doesn't force the user to select a column, rather it selects a single cell.
I can't seem to then reference a particular cell in the column. For example, if the user chose column A and the transaction column, if i wanted to sum all positive values in column A.

Dim UserInput1 As Range 'Transaction Column Identifier
Dim Transaction_Column As Range 'Transaction Column

'Error Handling
On Error Resume Next

'Allows the user to select location of Transaction Amount Column
Set UserInput1 = Application.InputBox _
        (prompt:="Choose the Transaction Amount Column", Type:=8)
   
'Error Handling to ensure user does not select more than 1 column
If UserInput1.Columns.Count <> 1 Then
    MsgBox ("Please Select One Column")
    Exit Sub
End If

CodePudding user response：

There are multiple ways to check if a user selected an entire column, including checking the .Cells.Count.

If UserInput1.Columns.Count <> 1 Or UserInput1.Cells.Count <> UserInput1.EntireColumn.Cells.Count Then