PDA

View Full Version : Solved: Copy button to second sheet causes error



benong
09-17-2010, 02:02 AM
hi,
i have created a button on sheet1 to perform some work, it works perfectly.
But when i cut & paste the button to sheet2, it does not work normally!



Private Sub ComboBox1_Change()
Dim partCode As String
Dim matchPartCode As Integer

'enable the buttons once selection is made
CommandButton1.Enabled = True

'get user selected value on combo box
partCode = UserForm1.ComboBox1.Value

'match the partCode
On Error Resume Next
matchPartCode = Application.Match(partCode, Worksheets("Sheet1").Range("H:H"), False)
On Error GoTo 0

If Not IsError(matchPartCode) Then
UserForm1.TextBox1.Value = Application.Index(Range("A:T"), matchPartCode, 11)
End If

End Sub


i do a check and it seems the error come from here:
matchPartCode = Application.Match(partCode, Worksheets("Sheet1").Range("H:H"), False)
i do not know the reason why. Pls advise.

Simon Lloyd
09-17-2010, 02:38 AM
Why not upload a workbook so we can see your structure.

someboddy
09-17-2010, 04:50 AM
Try changing
matchPartCode = Application.Match(partCode, Worksheets("Sheet1").Range("H:H"), False)
to
matchPartCode = Application.Match(partCode, ActiveSheet.Range("H:H"), False)

benong
09-19-2010, 06:56 PM
I've just spotted the error.
many thanks for your advice.

From:
UserForm1.TextBox1.Value = Application.Index(Range("A:T"), matchPartCode, 11)
change to:
UserForm1.TextBox1.Value = Application.Index(Worksheets("Sheet1").Range("A:T"), matchPartCode, 11)