Run time error when using ComboBox to populate textboxes

[craigs23]

Hi All,

I having a problem with a run time error code that occurs when I attempt to copy and paste data from one worksheet to another sheet.

basically I have 3 sheets 1 sheet (sheet1) is used for data entry, the op enters int various cells and textboxes by pressing a command button which copies data and should paste into the 2nd sheet (Data) on the next empty row using the following code:

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long, cls
cls = Array("A2", "B3", "C2", "D3", "E2", "F3")
With Sheets("Data")
    LR = WorksheetFunction.Max(1, .Range("A" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
        Me.Range(cls(i)).Copy Destination:=.Cells(LR, i + 1)
    Next i
End With
CommandButton2_Click
End Sub
Private Sub CommandButton2_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Row 'first number = number of rows from top of spread sheet data will be inserted
 Sheets("Data").Visible = 1
'Dim x As Integer ' Will Transfer data from textboxes to sheet
Dim x As Integer ' Will Transfer data from textboxes to sheet
For x = 1 To 5  'Numbers of textboxes
   ws.Cells(iRow, x + 6).Value = ActiveSheet.OLEObjects("TextBox" & x).Object.Text
   'Me.Controls("Textbox" & x).Text 'the +1 means input will start from column 2(B)
Next x
End Sub

The code worked perfectley well, until I then wanted to add a third sheet which would b used as a summary sheet on this sheet I added a combobox and textboxes, the textboxes and specfic cells will fill with values from the Data depenedent upon the selection in the combobox. using the code below.

Sub WorkSheet_Activate()
Worksheets("Sheet3").OLEObjects("ComboBox1").ListFillRange = "MyRange"
ComboBox1.ListIndex = 0
End Sub
Private Sub ComboBox1_Change() 'Fills Boxes from Data Sheet
    Dim Rng As Range
    With Worksheets("Data").Range("MyRange")
        Set Rng = .Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not Rng Is Nothing Then
                TextBox1.Value = Rng.Offset(0, 1).Value
                TextBox2.Value = Rng.Offset(0, 2).Value
                TextBox3.Value = Rng.Offset(0, 3).Value
                TextBox4.Value = Rng.Offset(0, 4).Value
                TextBox5.Value = Rng.Offset(0, 5).Value
                Sheets("Sheet3").Range("D21").Value = Rng.Offset(0, 6).Value
            Else
                'TextBox1.Value = "Not Found"
                'TextBox2.Value = "Not Found"
                'TextBox3.Value = "Not Found"
            End If
    End With
End Sub

In isolation both of these codes work however I am now finding that when I press the command button in sheet on the macro runs up to following line:

Me.Range(cls(i)).Copy Destination:=.Cells(LR, i + 1)

Then swithces to the combobox change event and I get the following error Run Time Error '1004': Application or object-defined error at the following line:

Set Rng = .Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)

As i said this was working OK when I placed all the code on one worksheet can any one help?

Thanks in advance

 

[craigs23]

Can anyone Help?

 

[Firefly2012]

Hi Craig

When this bugs out, if you hover the mouse pointer over Combobox1.Value, what do you see returned as the value?

 

[craigs23]

Hi Firefly,

Thanks for the reply In the interim I managed to find a workaround by modifying the code to as below, It's not pretty and I'm sure a better person could make it hugely more efficent but it seems to work (fingers crossed)

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row 'first number = number of rows from top of spread sheet data will be inserted
 Sheets("Data").Visible = 1
'Dim x As Integer ' Will Transfer data from textboxes to sheet
Dim x As Integer ' Will Transfer data from textboxes to sheet
For x = 1 To 5  'Numbers of textboxes
ws.Cells(iRow, x + 6).Value = ActiveSheet.OLEObjects("TextBox" & x).Object.Text
ws.Cells(iRow, 2).Value = ActiveSheet.Range("A1")
ws.Cells(iRow, 3).Value = ActiveSheet.Range("B2")
ws.Cells(iRow, 4).Value = ActiveSheet.Range("C1")
ws.Cells(iRow, 5).Value = ActiveSheet.Range("D2")
ws.Cells(iRow, 6).Value = ActiveSheet.Range("E3")
Next x
End Sub