I have a user form to search previous records in a table on another sheet named "data base" and load them in to a worksheet. The form has a list box displaying the first 6 columns from the data base, a textbox to enter what to search for, and control buttons. The Find button has a find next code, and the load button copies info from the selected row of data in the data base to various cells in a work sheet.
The codes work great, but they work in the "data base" sheet, and totally ignor the list box on the user form. When I step through the code in split screen I see it go right to the sheet. How do I get the command buttons to work with the list box in the same user form?
Thanks, Mike
Option Explicit
__________________________________________________________
Private Sub CommandButton3_Click()
' laods record into work sheet
'
' Clearing user worksheet before loading
Sheets("user").Select
Range("D4").Select
Selection.ClearContents
Range("D6:E14").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("E6:E14").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("G6:G14").Select
Selection.ClearContents
Range("J4").Select
Selection.ClearContents
Range("I6:I14").Select
Selection.ClearContents
Range("L4").Select
Selection.ClearContents
Range("K6:K14").Select
Selection.ClearContents
Range("N4").Select
Selection.ClearContents
Range("M6:M14").Select
Selection.ClearContents
Range("P4").Select
Selection.ClearContents
Range("O6:O14").Select
Selection.ClearContents
Range("R4").Select
Selection.ClearContents
Range("Q6:Q14").Select
Selection.ClearContents
Range("T4").Select
Selection.ClearContents
Range("S6:S14").Select
Selection.ClearContents
Range("F18").Select
Selection.ClearContents
Range("E20:E28").Select
Selection.ClearContents
Range("H18").Select
Selection.ClearContents
Range("G20:G28").Select
Selection.ClearContents
Range("J18").Select
Selection.ClearContents
Range("I20:I28").Select
Selection.ClearContents
Range("L18").Select
Selection.ClearContents
Range("K20:K28").Select
Selection.ClearContents
Range("N18").Select
Selection.ClearContents
Range("M20:M28").Select
Selection.ClearContents
Range("P18").Select
Selection.ClearContents
Range("O20:O28").Select
Selection.ClearContents
Range("R18").Select
Selection.ClearContents
Range("Q20:Q28").Select
Selection.ClearContents
Range("T18:T29").Select
Selection.ClearContents
Range("C_Comments") = "Verylak base, Hentzen R, B, W, L, Verylak N, Y"
Range("C_Sales_Order_No.").Select
Selection.ClearContents
Range("C_Customer_Name").Select
Selection.ClearContents
Range("C_Color_Name") = "none"
Range("C_Manufacturer") = "none"
Range("C_Date") = Date
Range("C_Specie").Select
Selection.ClearContents
Range("C_New_Batch_Base").Select
Selection.ClearContents
' Loading
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 1).Select
Selection.Copy
Sheets("user").Select
Range("C_Sales_Order_No.").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 2).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Customer_Name").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 3).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Specie").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 4).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Color_Name").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 5).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Manufacturer").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 6).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Date").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 7).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Comments").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 8).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Base").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 9).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Red").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 10).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Brown").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 11).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Black").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 12).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Yellow").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 13).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_White").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 14).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Blue").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 15).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Misc1").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 16).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Misc2").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 17).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_TotalVolume").Select
ActiveSheet.Paste
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 18).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("user").Select
Range("C_Can").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("data base").Select
Cells(Application.ActiveCell.Row, 19).Select
Application.CutCopyMode = False
Selection.Copy
Range("A2").Select
Sheets("user").Select
Range("C_CanStain").Select
ActiveSheet.Paste
Range("T20:T28").Select
Application.CutCopyMode = False
Selection.Copy
Range("D6:D14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("C_New_Batch_Base").Select
frmSearch.Hide
End Sub
____________________________________________________________________
Private Sub CommandButton4_Click()
' search with FIND NEXT loop
Dim SearchRange As Range
Dim WantCell As Range
Dim Wanted As String
Dim FirstWantCell As String
Wanted = tbxSenter.Value
Set SearchRange = Range("A2", Range("F2").End(xlDown))
Set WantCell = SearchRange.Find(What:=Wanted, MatchCase:=False, LookAt:=xlPart)
If WantCell Is Nothing Then
MsgBox "No records found."
Else
FirstWantCell = WantCell.Address
Do
WantCell.Select
Set WantCell = SearchRange.FindNext(WantCell)
Loop While WantCell.Address <> FirstWantCell
End If
End Sub
_______________________________________________________________________
Private Sub lbxSearchResult_Click()
End Sub
____________________________________________________________________
Private Sub UserForm_Click()
End Sub
