Error in code

[Corey]

I am getting anerror with the below code:
Is there a way i can get it to use the ActiveCell that triggered the macro
here ??

Private Sub ComboBox1_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = workSheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) =
True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Value <> "" Then
If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then
'<=========== Error with ActiveCell
ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0)
ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i,
0).Address
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Corey....

 

[NickHK]

Corey,
Maybe you need to change the TakeFocusOnClick of the combo box from True to
false.

NickHK

 

[Corey]

Nick,
Will that stop the error in that line then ?

 

[NickHK]

Corey,
Err,.. no, because there is no TakeFocusOnClick property for a combo box. I
was thinking of the command button.

NickHK

 

[NickHK]

Corey,
Activecell is not a property of a specific sheet. There is only ever 1
ActiveCell, on the active sheet.

Depends what you are doing, but maybe
If ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then

NickHK

 

[Corey]

Thanks Nick,
I will give that a go tomorrow.

When i am refering to the Activecell i mean the Cell that Triggered the
Macro(Range Selecetion Change event) to run.

Corey....

 

[NickHK]

Corey,
But this code is in the ComboBox1_DropButtonClick() event, so you can use
the ActiveCell.
ActiveCell.Parent tells you the WS that is active, as does ActiveSheet.

If you do mean to use the Worksheet_SelectionChange event, then the argument
"(ByVal Target As Range)" is passed, which tells you the range that
triggered this event.

NickHK

 

[Corey]

Nick,
When the user clicks on a cell(in sheet5) in the range i have a Userform
popup to prompt for a value in the list.
The cell that run the macro is the ActiveCell that i was refering to.

I have a Value in Column A.(From sheet4.Column A)
then
Then a user selects a value in the SAME ROW in Column F(sheet5).

The value in Column A (sheet5)refers to a value in Column A in another
sheet., (sheet4)
I then need the values in Column C(sheet4) that are Offset from the Column
A(sheet4).
If the value in Column A(sheet4) was in Cell A102,
then,
The Range or list of values that i need to populate in the ColumnF
list(Userform Combobox) is:
a Range of ("C104:C124") in (sheet4)

Corey....

 

[NickHK]

Corey,
Not sure I follow all that, but maybe...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RowNumber as long
RowNumber=Target.Row
will make it more clear as to which address you are referring to.

NickHK