Open userform after listBox selection

[ipbr21054]

Morning,

I have this working code in use.

Private Sub ListBox1_Click()

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
 
  Unload DatabaseNameSearch
End Sub

Basically i enter a partial name in a textbox on my userform.
The listbox is then populated with names.
I make a selection in the listbox & then the userform is closed & the code above selects the row for the selected person on my worksheet.

What i have decided to do is when a selection is made in the listbox is then to show a MsgBox.
Selecting NO will close the userform & the customer is selected on my worksheet.
SO FAR ALL IS OK.

Selecting YES should close this userform & open another userform.
THIS DOESNT HAPPEN.

When YES is selected the userform closes & thats it ??
This is my code for the addition.

Private Sub ListBox1_Click()
Dim answer As Integer

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  MsgBox "OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE"
  If answer = vbYes Then
  If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
  Else
  Unload DatabaseNameSearch
  End If
End Sub

Currently if i double click a customers name using the code below the userform opens & that customers file is shown.
So above the code was taken from this working code but obviously ive missed something.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
End Sub

 

[ipbr21054]

This is how it works.
I press the command button DatabaseSheetCustomersName
Where a form opens.
I enter a letter etc in the text box.
This then returns names in the list box.
Selecting a name shows the msgbox
Selecting YES should close this user form 7 open the userform called Database where the selected name is now shown with the details.

With reference to double click.
I double click on the name TOM JONES in column A which then opens the userform Database.
Now i see the record for TOM JONES

The userform should do the same thing,ie select the name from the list box.

Because at present i select the name.
user form closes.
Tom Jones is selected in the worksheet.
I double click Tom Jones & the userform Database opens.

Very long winded hence why this post is started.

 

[ipbr21054]

Morning,

Here is a Drop Box link of my reduced in size file for you to look at an see it in a better way of how the code currently works etc.

DR.zip

Please do this to see,
Click "SEARCH FOR CUSTOMER"
Enter the letter "E" in the "TYPE NAME HERE" field
The results for letter "E" are now shown in the listbox
Select "ED 001"
Select "YES" on the Msgbox

Now you will see the Database userform open up & "ANDY PANDY" is currently shown.

So what i am looking for is when you selected "ED 001" the Database userform should open with "ED 001" being shown.
.................................................

Please do the same but this time select "NO" to the Msgbox
You will now be taken to the worksheet
You will see that "ED 001" is selected.
Please double click "ED 001"
You will see that the Database userform has opened with "ED 001" now shown

This is how i would like it to work when you select "YES" on the MsgBox

Many thanks for your time.

Im hopeless at explaining things but this i hope will clear things up.

 

[jasonb75]

The proverbial penny wasn't dropping when I looked at your code before, as soon as I checked the working version I realised what I was missing.

This should do it.

Private Sub ListBox1_Click()
      Dim answer As Integer
  
  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  answer = MsgBox("OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")
   If answer = vbYes Then
   Unload DatabaseNameSearch
   Database.LoadData Sheets("DATABASE"), Selection.Row

Else
    Unload DatabaseNameSearch
End If
End Sub

 

[ipbr21054]

Yes Yes Yes
Works a treat.

Many thanks for the continued support.