Working code needs attention

[ipbr21054]

Morning all,
I have the working code supplied below.

How it works is i make a selection from the combobox & that person in question has its row highlighted & the cell in column A which is customers name is then selected.

What i would like to do now at this point is when i hit enter the userform opens & that customers entry is then shown.

The userform is called Database

If it helps when the userform opens the customers name is in the textbox called txtCustomer

Private Sub combobox1_Change()  Dim r As Range
  Set r = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox1.Value)
  If Not r Is Nothing Then r.Select
  ComboBox1.ListIndex = -1
End Sub

Many thanks & have a nice Sunday.

 

[JLGWhiz]

this should open your user form, but there is insufficient information to do the Textbox selection.

Private Sub combobox1_Change()
Dim r As Range
  Set r = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox1.Value)
      If Not r Is Nothing Then
         r.Select
         Database.Show
      End If
  ComboBox1.ListIndex = -1
End Sub

 

[ipbr21054]

OK
Please advise what you would like to supply you with.

 

[JLGWhiz]

I think what you would need to do is put this statement in your UserForm_Initialize event.

txtCustomer.Value = r

You can do that by opening the vbEditor, doubleclick your userform name, right click on the form, 'View Code', then select the Initialize Event and when the pre-composed code"

Private Sub Database_Intialize()

End Sub

appears, put the statement before the End Sub statement.

 

[ipbr21054]

This is the full code,i put it after the line Navigate Direction:=xlFirst & before End sub but it then opened userform where a 6 was shown in the customers name box.

Many Thanks.

 Dim ws As Worksheet Dim r As Long
 Dim EventsEnable As Boolean
 Const startRow As Long = 6


Private Sub ImageClose_Click()
    'close the form (itself)
    Unload Me
End Sub


Private Sub CloseUserForm_Click()
    'close the form (itself)
    Unload Me
End Sub


Private Sub ComboBoxCustomersNames_Change()
    If Not EventsEnable Then Exit Sub
'get record
    r = Me.ComboBoxCustomersNames.ListIndex + startRow - 1
    Navigate Direction:=0
End Sub


Private Sub ComboBoxCustomersNames_Update()
    With ComboBoxCustomersNames ' change as required
        .RowSource = ""
        .Clear
        .List = ws.Range("A6:A" & ws.Range("A" & Rows.Count).End(xlUp).Row).Value
    End With
End Sub


Private Sub DeleteRecord_Click()


Dim C As Range


With Sheets("DATABASE")
    Set C = .Range("A:A").Find(What:=txtCustomer.Value, _
                        After:=.Range("A5"), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
End With


If Not C Is Nothing Then
    If MsgBox("Are you sure you want to delete the record for " & txtCustomer.Text & "?", vbYesNo + vbCritical) = vbYes Then
        Rows(C.Row).EntireRow.Delete
        MsgBox "The record for " & txtCustomer.Text & " has been deleted!"
    Else
        MsgBox "The record containing customer " & txtCustomer.Text & " was not deleted!"
    End If
Else
    MsgBox "There were no records containing customer " & txtCustomer.Text & " to be deleted"
End If


Set C = Nothing


Unload Me
End Sub


Private Sub NewRecord_Click()
    Dim i As Integer
    Dim IsNewCustomer As Boolean
    
    IsNewCustomer = CBool(Me.NewRecord.Tag)
    
    Navigate Direction:=IIf(IsNewCustomer, xlNone, xlPrevious)


    'if new customer, add Date
    If IsNewCustomer Then
        Me.txtJobDate.Text = Format(Date, "dd/mm/yyyy")
        Me.txtCustomer.SetFocus
    End If
    
    ResetButtons IsNewCustomer


End Sub


Private Sub NextRecord_Click()
    Navigate Direction:=xlNext
End Sub


Private Sub PrevRecord_Click()
    Navigate Direction:=xlPrevious
End Sub


Private Sub txtBlankUsed_Change()


End Sub


Private Sub txtCustomer_Change()


End Sub


Private Sub txtVehicle_Change()


End Sub


Private Sub UpdateRecord_Click()


Dim C As Range
Dim i As Integer
Dim Msg As String
Dim IsNewCustomer As Boolean
'New Part
 Dim ctrl As MSForms.Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then ctrl.BackColor = RGB(255, 255, 255)
    Next ctrl
'End New part


    If Me.NewRecord.Caption = "CANCEL" Then
        With Sheets("DATABASE")
            Set C = .Range("A:A").Find(What:=txtCustomer.Value, _
                                After:=.Range("A5"), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
        End With
        If Not C Is Nothing Then
              MsgBox "Customer already Exists, file did not update"
              Exit Sub
        End If
    End If
    
    IsNewCustomer = CBool(Me.UpdateRecord.Tag)
       
    Msg = "CHANGES SAVED SUCCESSFULLY"
        
    If IsNewCustomer Then
    'New record - check all fields entered
    If Not IsComplete(Form:=Me) Then Exit Sub
        r = startRow
        Msg = "NEW CUSTOMER SAVED TO DATABASE"
        ws.Range("A6").EntireRow.Insert
        ResetButtons Not IsNewCustomer
        Me.NextRecord.Enabled = True
    End If
    
    On Error GoTo myerror
    Application.EnableEvents = False
    'Add / Update Record
    For i = 1 To UBound(ControlNames)
        With Me.Controls(ControlNames(i))
            'check if date value
            If IsDate(.Text) Then
                ws.Cells(r, i).Value = DateValue(.Text)
            ElseIf i = 15 Then
                ws.Cells(r, i).Value = CDbl(.Text)
            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With
    Next i
    
    If IsNewCustomer Then
        Call ComboBoxCustomersNames_Update
        Range("A6:P6").Interior.ColorIndex = 6
        
        With Sheets("DATABASE")
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
                .Range("A5:P" & x).Sort Key1:=.Range("A6"), Order1:=xlAscending, Header:=xlGuess
                Range("A6:P6").Borders.LineStyle = xlContinuous
                Range("A6:P6").Borders.Weight = xlThin
            
       End With
              
    End If
    
    ThisWorkbook.Save
    
    'tell user what happened
    MsgBox Msg, 48, Msg
    
    Set C = Nothing
    
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
Unload Me


End Sub


Sub ResetButtons(ByVal Status As Boolean)
    
    With Me.NewRecord
        .Caption = IIf(Status, "CANCEL", "ADD NEW CUSTOMER TO DATABASE")
        .BackColor = IIf(Status, &HFF&, &H8000000F)
        .ForeColor = IIf(Status, &HFFFFFF, &H0&)
        .Tag = Not Status
    Me.ComboBoxCustomersNames.Enabled = CBool(.Tag)
    End With
    
    With Me.UpdateRecord
        .Caption = IIf(Status, "SAVE NEW CUSTOMER TO DATABASE", "SAVE CHANGES FOR THIS CUSTOMER")
        .Tag = Status
    End With
End Sub


Private Sub Userform_Initialize()
    Set ws = ThisWorkbook.Worksheets("Database")
    
    ComboBoxCustomersNames_Update




    ResetButtons False


    'start at first record
    Navigate Direction:=xlFirst
    
End Sub


Sub Navigate(ByVal Direction As XlSearchDirection)
    Dim i As Integer
    Dim LastRow As Long
    
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    r = IIf(Direction = xlPrevious, r - 1, r + xlNext)
    
    'ensure value of r stays within data range
    If r < startRow Then r = startRow
    If r > LastRow Then r = LastRow
    
    'get record
    For i = 1 To UBound(ControlNames)
         Me.Controls(ControlNames(i)).Text = IIf(Direction = xlNone, "", ws.Cells(r, i).Text)
    Next i
    
    Me.Caption = "Database"
    
    'set enabled status of next previous buttons
    Me.NextRecord.Enabled = IIf(Direction = xlNone, False, r < LastRow)
    Me.PrevRecord.Enabled = IIf(Direction = xlNone, False, r > startRow)
    
    EventsEnable = False
    Me.ComboBoxCustomersNames.ListIndex = IIf(Direction = xlNone, -1, r - startRow)
    EventsEnable = True




End Sub

 

[ipbr21054]

Morning,
Below is my current code for the UserForm_Initialize event.

If i put the code as advised "txtCustomer.Value = r" before the End Sub, the userform opens and i see the first entry in the database, i also see in the textbox customers name the digit 6.

If i put the code "txtCustomer.Value = r" anywhere else in the code the userform opens but i see the first entry & the digit 6 isnt shown but the customers name is.

So the userform opens but is not opening on the customers name that was selected from the combobox.

Private Sub Userform_Initialize()    Set ws = ThisWorkbook.Worksheets("Database")
    
    ComboBoxCustomersNames_Update




    ResetButtons False


    'start at first record
    Navigate Direction:=xlFirst
    

End Sub

 

[JLGWhiz]

My previous post was inaccurate. In this snippet you would identify the customer.

Private Sub combobox1_Change()
Dim r As Range
  Set r = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox1.Value)
      If Not r Is Nothing Then
         r.Select
         Database.Show
      End If
  ComboBox1.ListIndex = -1
End Sub

at this point you have selected range 'r' with the customer name. So you can use 'Selection' in the Initialize Event to show the customer name.

Private Sub UserForm_Initialize()
    'Other code
    txtCustomer.Value = Selection.Value
End Sub

The object is to get the customer name from a known location and have it load into the text box with the initialize event.

 

[mikerickson]

I would avoid putting the "fill text box with value" in the initialize event. (What if the UF were invoked (via other means) while a Shape was selected.)

The OP could use code like

Private Sub combobox1_Change()
Dim r As Range
  Set r = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox1.Value)
      If Not r Is Nothing Then
         With Database
             .TxtCustomer.Text = r.Text
             .Show
         End With
      End If
  ComboBox1.ListIndex = -1
End Sub

 

[ipbr21054]

I would avoid putting the "fill text box with value" in the initialize event. (What if the UF were invoked (via other means) while a Shape was selected.)

The OP could use code like

Private Sub combobox1_Change()
Dim r As Range
  Set r = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox1.Value)
      If Not r Is Nothing Then
         With Database
             .TxtCustomer.Text = r.Text
             .Show
         End With
      End If
  ComboBox1.ListIndex = -1
End Sub

I think the above is correct so i have removed the txtCustomer.Value = Selection.Value as previously advise on the click event and now going to use the above.

There is one issue that i can see though.

I make my selection from the worksheet combobox which then opens the userform.
The selection say TOM JONES has now been pulled up & the info is shown for that customer,which is great.

BUT

Also on the userform is a combobox which allows me to navigate to another customers files should i need to.
So what i see on this userform is in the text box TOM JONES is shown BUT the name in the combobox is the first entry from the combobox on the worksheet say ANDY SMITH.
If this makes sense to you i make an edit to the info shown on this userform BUT because there is a conflict it saves to the name in the combobox list.

I know it sounds odd without the form to look at BUT when the TOM JONES name is pulled up can we also match the name TOM JONES in the combobox.

This way when i save it i know i am saving to TOM JONES & not ANDY SMITH.

 

[mikerickson]

I'm not sure what the r in the ComboboxCustomerName_Change event is for, but if you have txtCustomerName automatically filled when the user selects from the user form's ComboBox, the calling routine could put the name in the user form's combo box. Also I don't see EventsEnabled being set to True in the Intialize event. You probably want to do that as well.