CODEWITHSUNDEEP
vbaexcel
B.G.
B.G.

Reputation: 6016

VBA - Excel Showing form after double click on a Cell

I've encountered a strange Problem. I have an Input Form for the User in Excel. This Form provides basic CRUD functions to edit a List in a Table. Now this Userform also has a ListBox which shows all the entrys with Id and some basic infos (so you know which entry is which ). Now what I want is, that if you select a row in the normal excel table, and then open the InputForm, the selected row in the table should also be selected in the listBox of the InputForm.

I do this like this:

  If Selection.Row >= StartRow() Then
      ListBoxAll.listIndex = Selection.Row - StartRow()      
  Else
      ListBoxAll.listIndex = 0
  End If

This works great if I open the Ui per Button click. But if I try to open the form with the Before Double Click Event. I got an Offset.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    UserFormInput.Show
End Sub

Now for the strange part. If I check what value the Selection.Row has, by showing it with a MessageBox, it all works fine! But as soon as I remove the MessageBox its broken all over again.

    If Selection.Row >= StartRow() Then
        ListBoxAll.listIndex = Selection.Row - StartRow()
        MsgBox Selection.Row
    Else
        ListBoxAll.listIndex = 0
    End If

It does not matter where in the if I put the Messagebox.

So the Question has anybody ever got the same Problem ? And does anybody know a solution or a workaround to it ? (I don't want to show a Messagebox!)

So I added some screenshots to make it easier to understand.

Table with example data

Ui with example data

Update I think it has something to do with the Focus after a double click, which is (maybe) on the doubleclicked Cell. And gets changed if I put out a MsgBox

Upvotes: 0

Views: 2834

Answers (1)

Kathara
Kathara

Reputation: 1290

(I'll write this as an answer but this is only a part of the code... :)

Oh, german ^.^

I believe each time you call your userform you'll have to compare the selected data to the listbox data. As you have a multicolumn listbox you could do this by the following (I believe, but untested):

Dim ThisRow As Integer
ThisRow = ActiveCell.Row
Dim ThisValue As String
ThisValue = ActiveWorkbook.Sheet("Tabelle1").Cells(ThisRow, 1).Value

This "1" should be the column which contains the same kind of information as is displayed in the first column of your listbox.

Dim i As Integer
For i = 1 to UserFormInput.Listbox1.Listcount
    If UserFormInput.Listbox1.List(i, 0).Value = ThisValue Then
        UserFormInput.Listbox1.List(i).Select
    End If
Next i

Upvotes: 1

Related Questions