Display userform from customer shown in specific cell G13

[ipbr21054]

Just to give you an idea of what i have at present.

On my worksheet called DATABASE I have values in cells across the page.
Double clicking the customers name in column A loads a userform with these values & the code is shown below.

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

Now it is this DATABASE im interested in but from another worksheet.

The worksheet in question is called INV
In cell G13 is a drop down where the user selects a customer from the list & that customer is then shown in cell G13

Now i have a command button which when pressed should take note of customer in cell G13 & load the userform / details just like it did on the DATABASE sheet.

The point of this is so i dont have to keep flicking form sheet INV to DATABASE to look at a value.




Please advise.

 

[ipbr21054]

I attempted this but i keep seeing OBJECT REQUIRED

Private Sub ViewCustomerInfo_Click()
If Target.Address = "G13" Then
Database.LoadData Me, Target.Address
End If
End Sub

 

[ipbr21054]

I have the following which will look at Cell G13 on sheet INV.
It will then find that customer on sheet DATABASE & select the cell.

Can this be used BUT then have the userform shown on sheet INV

Thanks

Private Sub ViewCustomerInfo_Click()
  Dim findString As String
  Dim rng As Range
 
  findString = Range("G13").Value
 
  If findString = "" Or Trim(findString) = "" Then
    MsgBox "NO NAME SELECTED IN THE CUSTOMER DETAILS SECTION", vbCritical, "NO CUSTOMER SELECTED MESSAGE"
  Exit Sub
  End If
 
  With Sheets("DATABASE").Range("A:A")
    Application.EnableEvents = False
    
    Set rng = .Find(What:=findString, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
  If Not rng Is Nothing Then
      Application.Goto rng, True
      ActiveCell.Interior.Color = vbRed
  Else
      MsgBox "CUSTOMER NOT FOUND"
  End If
 
    Application.EnableEvents = True
 
  End With
End Sub

 

[ipbr21054]

When running the code below the customer is found in column A
So now i need to double click the cell to open the userform.

I added the extra code that i thought would run the double click but nothing happens & i also get NO errors

Private Sub ViewCustomerInfo_Click()
  Dim findString As String
  Dim rng As Range
  
  findString = Range("G13").Value
  
  If findString = "" Or Trim(findString) = "" Then
    MsgBox "NO NAME SELECTED IN THE CUSTOMER DETAILS SECTION", vbCritical, "NO CUSTOMER SELECTED MESSAGE"
  Exit Sub
  End If
  
  With Sheets("DATABASE").Range("A:A")
    Application.EnableEvents = False
    
  Set rng = .Find(What:=findString, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
  If Not rng Is Nothing Then
      Application.Goto rng, True
      ActiveCell.Interior.Color = vbRed
      
  With ActiveCell
      Application.DoubleClick
  End With
  
  Else
      MsgBox "CUSTOMER NOT FOUND"
  End If
  
    Application.EnableEvents = True
 
  End With
  
End Sub

 

[MikeVol]

Maybe, try.

Private Sub ViewCustomerInfo_Click()
    Dim findString  As String
    findString = Range("G13").Value

    If findString = "" Or Trim(findString) = "" Then
        MsgBox "NO NAME SELECTED IN THE CUSTOMER DETAILS SECTION", vbCritical, "NO CUSTOMER SELECTED MESSAGE"
        Exit Sub
    End If

    With ThisWorkbook.Worksheets("DATABASE").Range("A:A")
        Application.EnableEvents = False

        Dim rng     As Range
        Set rng = .Find(What:=findString, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

        If Not rng Is Nothing Then
            Application.Goto rng, True
            rng.Interior.Color = vbRed
            Database.LoadData Sheets("DATABASE"), rng.Row
        Else
            MsgBox "CUSTOMER NOT FOUND"
        End If

        Application.EnableEvents = True
    End With

End Sub

 

[ipbr21054]

Perfect, been at it ages.

Worked a treat Thanks