Addition to working code advice

[ipbr21054]

Evening,
I am using the code shown below.

I have a userform where two columns are populated with data collected from my worksheet.

The code below will allow the user to select an entry in the listbox & be taken to the cell in question at column AB

What i have noticed it that the command button for which the code below is on can be pressed without the user making a selction from the listbox.
It is this i wish to address so that a Magbox pops up to advise say Must make listbox selection first

Private Sub SelectAndGo_Click()
Range("AB6:AC500").ClearContents
With ThisWorkbook.Worksheets("DATABASE")
Dim data As Variant

    data = .Range("J6:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
End With
  
   Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
 Unload HondaKeyCode
End Sub

 

[Micron]

before data = put

If Me.Listbox1.ItemsSelected.Count = 0 Then
   Msgbox "You must select an item from the list"
   Exit Sub
End If

However, if you don't want the range contents to be deleted in that case, then must move that line to after the End If that I wrote.
I might make other changes too - such as doing away with the With block. Excel vba isn't my strongest suit so I don't know if relying on ActiveCell is a good idea when I don't see that you're selecting a cell in code. I also don't see why you're assigning anything to "data" variable when you don't use the variable - at least not that I can see no matter how hard I look.

 

[ipbr21054]

Thanks for that & will try tomorrow.

I’m not sure I understand what you the. Wrote. Nothing is deleted.
In the listbox assume I select the value Z123 then the userform closes & the cell with Z123 in it is selected & that’s it.

Currently before asking if the user just run the code before selecting a random cell is selected.

 

[ipbr21054]

Hi,
The code had an issue but you put me on the right track thanks.
This worked for me.

Private Sub SelectAndGo_Click()
Range("AB6:AC500").ClearContents
With ThisWorkbook.Worksheets("DATABASE")
Dim data As Variant

    If ListBox1.ListIndex = -1 Then
        MsgBox "NO SELECTION WAS MADE FROM THE KEY CODE LIST", vbCritical, "NO KEY CODE WAS SELECTED MEAASGE"
    Exit Sub
End If

    data = .Range("J6:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
End With
  
   Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
 Unload HondaKeyCode
End Sub

 

[Micron]

Nothing is deleted

Does this not delete whatever is in the range: Range("AB6:AC500").ClearContents
Since I still can't see the reason for assigning anything to "data" variable, would you mind enlightening me? Thanks if you do, and thanks for the credit but maybe you're the one that deserves it.

I must have based my answer on how to do that in Access because I see that it doesn't code that way for Excel. It's another great example of how 2 departments at the same company can't be on the same page when it comes to how to design the same controls.

 

[ipbr21054]

Sorry we are talking about a step after what we needed to work on.
All sorted now thanks