Offset property only works for column 0

[morfex]

I have a workbook with several userforms. When a user fills out the form, the data in inserted into a series of columns in a single row. The next time they use the form, Excel adds the data to the next available row.

Now I want to add the ability for them to edit one of their entries via a form. This time, when the click on the first cell in the row, the userform loads. I've added the following code to the form on Activate:

Private Sub UserForm_Activate()
ActiveSheet.Unprotect
frmEliteEdit.txtGuestName.Value = tbl.Offset(0, 2).Value
frmEliteEdit.txtSaleDate.Value = tbl.Offset(0, 11).Value
End Sub

When the form opens, I want the textboxes and other fields on the form to be populated with the values from that row. So, if they click on the first cell in row 12, the GuestName field should contain the value of the 3rd cell in the row, and SaleDate should contain the value from the 12th cell in the row. However, it simply leaves the form fields blank.
If I change the Offset to (0, 0), the form does load the textbox with the data from the first cell, but it will not work for data in any other columns.

What am I doing wrong?

 

[Weaver]

what range does tbl represent?

 

[morfex]

Sorry about that. When they click on the cell to launch the edit form, the cell remains selected. The code should read:

Private Sub UserForm_Activate()
ActiveSheet.Unprotect
frmEliteEdit.txtGuestName.Value = ActiveCell.Offset(0, 2).Value
frmEliteEdit.txtSaleDate.Value = ActiveCell.Offset(0, 11).Value
End Sub

 

[Weaver]

Which event are you using to launch the form?

I tried it with:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 Then
        frmEliteEdit.Show
    End If
End Sub

And your code worked fine

 

[morfex]

Weaver,

Thanks so much for your help. Turns out I wasn't writing the code incorrectly, but on my worksheet each row contained many merged cells and this code doesn't appear to be able to jump across merged columns. So telling it to go from column 3 (merged 3-10), then column 11 (merged 11-19) did not work.

I rebuilt the spreadsheet without merging any cells in these rows and it works like a dream.

Thanks again!