UserForm on cell selection

**JoHansell** · 06-16-2011, 12:01 PM

I'm new to VBA so I might be missing something quite obvious.

I want to bring up a userform (which I have renamed paymentform) whenever a cell within a particular range is selected. The range is a column in a table which varies in length so I need to use the range name rather than cell references.

I found a similar question on the forum that gave a solution using a single cell reference rather than a range. I replaced the cell reference with my range name and nothing happens. I also tried adding Range before my range reference and did at least get an error message.

The code looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address(False, False) = ("sales[column14]") Then
paymentform.Show
End If

End Sub

Although I have created my userform and its layout I haven't yet done the code to work with the data it collects - does that make a difference?

Any ideas?

Cheers
JoH

**Dom83** · 06-16-2011, 03:28 PM

Hi,

The code below checks if the cell that has been selected (the "Target") intersects column 14 of your range "sales" (note that this might not be column 14 of the sheet as your range might not start in column A. If the selected cell is in column 14, it creates a new instance of your form and shows it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim frmPaymentForm As paymentform

    If Not Intersect(Target, Me.Range("sales").Columns(14)) Is Nothing Then
        Set frmPaymentForm = New paymentform
        frmPaymentForm.Show
    End If

End Sub

The code needs to go in the code module for the sheet that you're working on.

Cheers,
Dom