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

 

[jasonb75]

Unless Target is declared as a Public variable in a standard module then it will always be Nothing in the ListBox1_Click code so you will never get past Exit Sub.

 

[ipbr21054]

Can you advise what i need to add please

 

[nemmi69]

You are not picking up the response to the Msgbox.

Change

MsgBox"OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE"

to

answer = MsgBox("OPEN DATABASE ?", vbYesNo + vbCritical, "OPEN DATABASE MESSAGE")

 

[ipbr21054]

I did try that line but i get a RTE 424 object required.
I debugged it and this was in yellow

If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), target) Is Nothing Then

 

[nemmi69]

Unless Target is declared as a Public variable in a standard module then it will always be Nothing in the ListBox1_Click code so you will never get past Exit Sub.

Public
Have a public variable = Target.Row

'Declare public variable
Public TrgtRow As Long

'Then in your code
TrgtRow = Target.Row

Now refer to TrgtRow instead of Target.Row in your other code.

 

[ipbr21054]

Sorry but im not sure what or where the last part fits into the code.

Private Sub ListBox1_Click()
Dim answer As Integer
Public TrgtRow As Long

  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  answer = MsgBox("OPEN DATABASE USERFORM ?", 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

 

[jasonb75]

Using a second target variable to keep things neat.

In a standard vba module i.e. Module1

Public pTarget As Range

Then add a line to your double click code to set pTarget the same as Target

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
    Set pTarget = Target
    Database.LoadData Me, Target.Row
End Sub

Then use pTarget instead of Target in the listbox code.

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

Note that pTarget has been set back to Nothing once finished with, this shouldn't be necessary with what you're doing, but no harm in doing it just in case.

 

[ipbr21054]

Thanks.
Ive done as advised but i see a RTE 5 Invalid procedure or argument

This line of code is yellow

Database.LoadData Me, pTarget.Row

I tried it again but this time this is in yello.

 If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), pTarget) Is Nothing Then

 

[jasonb75]

What does DataBase.LoadData refer to in the first line? I'm not familair with the specific syntax used in that line but I'm assuming that these are object names?

In post 1 you said that you're trying to load another form, but looking at the code again, it appears that you're trying to reload the same form?

I've assumed that the whole procedure will be started by double clicking a cell, if you're trying to initailise the second form without the first one being opened then target (and subsequenty ptarget) will be empty so there will be nothing to work with.

I tried it again but this time this is in yello.

If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), pTarget) Is Nothing Then

For this part, you will need to qualify Range and Cells with a worksheet name, i.e. Sheets("Sheet1").Range(. I missed that part earlier, because that line of code is in the listbox code, it doesn't know where to look.